About to Lose my mind

[foxandphids]

I have tried this too many times without success. Here is the problem. Joe Smith is a VP of Sales he has 3 annual goals that his sales team can hit. he gets paid $1.00 for every $1.00 sold up to Goal 1, anything over Goal 1 up to Goal 2 he will be paid $2.00 for every dollar sold, and anything over Goal 2 he is paid $3.00 per dollar sold. Here is the issue I am having. John Smith has an annual goal paid monthly. So I need to some how figure out where his team is YTD against annual goals, but only pay on the monthly amount. Every IF and formula I have tried I keep getting errors. Here is an example of what I am trying to do Row 18-20 column D-O.

D5 is Goal 1
D6 is Goal 2
D7 is Goal 3

Row 10 is $ of sales per months, Row 11 Cumulative and is what I need to compare against all 3 goals.

Row 18 - 20 is what it would look like if I could figure out the formulas comparing against all 3 goals. Please Help!!!

 

[iggydarsa]

Try the following formulas

On D18:

=IF(SUM($D$10:D10)<=D5, D10, IF(D10+(D5-SUM($D$10:D10))<0, 0, D10+(D5-SUM($D$10:D10))))

On D19:

=IF(SUM($D$10:D10)<=D6, D10, IF(D10+(D6-SUM($D$10:D10))<0, 0, D10+(D6-SUM($D$10:D10))))-D18

On D20:

=D10-SUM(D18:D19)

And drag the formulas to right...

It is not worth losing your mind.... ohhhmmmmm....

 

[foxandphids]

Try the following formulas

On D18:

=IF(SUM($D$10:D10)<=D5, D10, IF(D10+(D5-SUM($D$10:D10))<0, 0, D10+(D5-SUM($D$10:D10))))

On D19:

=IF(SUM($D$10:D10)<=D6, D10, IF(D10+(D6-SUM($D$10:D10))<0, 0, D10+(D6-SUM($D$10:D10))))-D18

On D20:

=D10-SUM(D18:D19)

And drag the formulas to right...

It is not worth losing your mind.... ohhhmmmmm....

I could hug you right now. Thank you so much!!!

 

[iggydarsa]

You are very welcome