Conditional formula

[olliedstuff]

Hi,

I'm trying to produce a formula for bonus calculation that is driven by monthly and quarterly cumulative performance. I need the formula to calculate the bonus in month one, two and three if target sales are met in the individual months; but if the cumulative performance over the entire quarter deoes not meet the entire quarter target then 0 in each of the three months. I have tried using, IF(OR( formulas but no luck thus far.
Can anyone help please?

Thanks

 

[GlennUK]

Don't you just mean total performance over the 3 months, rather than cumulative. If the total 3 months performance does not meet target, then the calc is zero for any individual month. Just test the total for the quarter in your IF function.

 

[olliedstuff]

Hi Glenn,

Thanks for your quick post! Its not quite that simple unfortunately as we need to show if they have achieved on the individual months as we go through the quarter so I need something that will supersede these monthly calculations once the quarters position is known. This will be at the end of the quarter when all 'actual' sales are in and all 'pipeline/forecast' sales have been removed. Any thoughts?

 

[GlennUK]

Is there some test you can do in the formula to see whether the quarters position is known?

 

[olliedstuff]

Yes, I have a quarterly summary (made up of actual plus forecast sales) so I can compare this to budget within the formula.

 

[GlennUK]

Surely that will show the quarterly summary whether it's complete or not. You said:

I need something that will supersede these monthly calculations once the quarters position is known

Is there some indicator that you can test to see if the quarters actual position is known?

 

[olliedstuff]

Unfortunately not, if the month target is met but the quarter target is not it needs to return 0 but with my formula it still returns the month figures. I need it to say if X is achieved do Y but if Z is not achieved then replace Y with 0.

 

[GlennUK]

Can you test for a date then, after which the calculation will change?

 

[olliedstuff]

Yes, this sounds promising!

 

[GlennUK]

So,
IF(date < quarter_end,Do_single_Month_calc,IF(quarter_target_met,Do_single_Month_calc,0))
should be the generic shape of the formula.

What do you think?