i want to see month/date on which target exceeded

[anilrini]

hello
my raw data is in two tables
table1: name, sales_amount, Sales_date
table2: name, sales_target

i have imported table1 data in pivot table as -
name in row, date in column and sales_amt in sum_total

my problem is that i want to find out that on which date/month each sales_man exceeded his/her target.

Is it possible in excel.

TIA

[anilrini]

I think 'FORECAST' function can work but with some array formula that will put the cumulative values of sales instead of actual sales.

hello
my raw data is in two tables
table1: name, sales_amount, Sales_date
table2: name, sales_target

i have imported table1 data in pivot table as -
name in row, date in column and sales_amt in sum_total

my problem is that i want to find out that on which date/month each sales_man exceeded his/her target.

Is it possible in excel.

TIA

[Portuga]

One way to do it would be to consolidate the information from table 2 to table 1 and add a few helper columns to table1

Add a helper column in table 1 called "Month" with the month formula feeding from the Sales date Column. =Month(

Add a helper column in table 1 called "Sales target" where you would put monthly target per name. (Considering that the sales target is fixed, if its variable by month/person, you would probably need a table with that info and then use a vlookup formula for 1 variable of a sumifs formula for 2 variables. Which formula would be dependent on the info contained in table 2 and the way it is displayed.

Add a helper column in Table 1 called "Target achieved" where you would calculate the sales amount for the person/per month using a "If"/ "Sumifs" combination formula where the display would be either “Achieved” ”Not achieved”

Then you would add the pivot with these new fields and select the ones you want to see. The pivot would work with data for 1 year only. For several years you would have to add the =year( dimension to it

[anilrini]

That is fine but i want to learn the array formula method

One way to do it would be to consolidate the information from table 2 to table 1 and add a few helper columns to table1

Add a helper column in table 1 called "Month" with the month formula feeding from the Sales date Column. =Month(

Add a helper column in table 1 called "Sales target" where you would put monthly target per name. (Considering that the sales target is fixed, if its variable by month/person, you would probably need a table with that info and then use a vlookup formula for 1 variable of a sumifs formula for 2 variables. Which formula would be dependent on the info contained in table 2 and the way it is displayed.

Add a helper column in Table 1 called "Target achieved" where you would calculate the sales amount for the person/per month using a "If"/ "Sumifs" combination formula where the display would be either “Achieved” ”Not achieved”

Then you would add the pivot with these new fields and select the ones you want to see. The pivot would work with data for 1 year only. For several years you would have to add the =year( dimension to it

[Portuga]

Personally I preffer to have the data consolidated in just 1 sheet and let the pivot do the work once the helper columns are in place but a formula approach is also possible without an array formula:


For January,after you create your named ranges "Agents", "SalesDate" and "Amount" and considering the name of the agent in A2 and target in B2:

=IF(SUMPRODUCT(--(Agents=A2);--(MONTH(SalesDate)=1);Amount)>=B2;"Target Achieved";"Target not achieved")

[anilrini]

Actually i wanted to see the month/date on which target exceeded.

[Portuga]

Change ">=" to ">"