Bonus Formula

Hi Guys,

I need some help with a formula for calculating bonus.

We have 3 values - Threshold, Target and Stretch. These could be for example 36%, 40% and 51% (not linear).

If someone achieves Threshold, the get a score of 50, if the achieve Target, they score 100, if they score Stretch, they score 200.

But, how do I calculate the score if they achieve 41% (for example)? I know it needs to be between 100 and 200, but what exactly?

Because the targets are not linear, I can't see how I'd do this with a single formula. Is it possible?

Thanks.

How about something along the lines of the attached?

Not sure what score you would want to calculate if stretch target was exceeded/threshold wasn't met however?

Originally Posted by brokenbiscuits

How about something along the lines of the attached

That's perfect!! Thanks. 200 is the max score, so I guess I'll do something like you did for 'below threshold'

Thank you

Good stuff.

A simple "IF(B6>=B4,200," at the start of the formula would enforce a maximum score of 200.

Great Help, Thanks!

Thanks for this - it was a great help.