M
Mark D
Good afternoon everyone
I'll try and put this as simple as possible but have tried doing this myself
and cannot work out how to do this.
I have a new employee - summary as follows
He starts 01/01/2010 (c65)
He is a level 2 (b65)
He has a monthly sales target of say €10,000 (d65)
However his target does not start in month 1, instaead for the first 4
months (Jan - April his target will be €0.00, From months 5 - 8 (May - Aug)
his taget will be 75% of the €10,000 (€7,500). From month 9 (Sept) it'll be
100% of the target = €10,000
Along the top of my spreadsheet I have months (all set to the last day in
the month)
AH3 AH4 AH5 AH6
Jan 09 Feb 09 March 09 Apr 09 etc.
I have had a crack at a couple of formulas (for example below) but nothing
is working. (B65=level, C65=Start date. AH3= Calander Month, D65=Target
=(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*0%))+(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*75%))))+(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*100%))
I just cant get it to work out what month in the cycle my guy is at and
therefore what rule to apply
Thanks for taking the time to look and for any help you can give me. I hope
I provided enough information
I'll try and put this as simple as possible but have tried doing this myself
and cannot work out how to do this.
I have a new employee - summary as follows
He starts 01/01/2010 (c65)
He is a level 2 (b65)
He has a monthly sales target of say €10,000 (d65)
However his target does not start in month 1, instaead for the first 4
months (Jan - April his target will be €0.00, From months 5 - 8 (May - Aug)
his taget will be 75% of the €10,000 (€7,500). From month 9 (Sept) it'll be
100% of the target = €10,000
Along the top of my spreadsheet I have months (all set to the last day in
the month)
AH3 AH4 AH5 AH6
Jan 09 Feb 09 March 09 Apr 09 etc.
I have had a crack at a couple of formulas (for example below) but nothing
is working. (B65=level, C65=Start date. AH3= Calander Month, D65=Target
=(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*0%))+(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*75%))))+(SUMPRODUCT(($B$65=2)*($C$65<AH3),$D$65*100%))
I just cant get it to work out what month in the cycle my guy is at and
therefore what rule to apply
Thanks for taking the time to look and for any help you can give me. I hope
I provided enough information