Max and Min and showing how much of a cost bracket has completed

[tony7262]

Hi,

I am trying to measure how much of a target a series of people have managed to achieve and as a result how much of a payout there should be for beating that target?

My problem is that the payouts are split into brackets depending on how much they beat the target by and these brackets are differing sizes and relate to differing amounts of payouts and everyone receives extrapolated amounts of payout between brackets (i.e. if the bracket is 100% to 130% of target and they achieve 120% of target they would receive the payout for achieving 100% plus (120-100)/(130-100) = 2/3 of the payout from the uncompleted bracket.

I have attached an example to try and talk through what I want the formula to do but I am struggling with the looking up the minimum completed bracket section of it - which I hope becomes clear after seeing the example.

Thanks in advance for any help anyone can give.

Cheers,
Tony

[crushdrinker06]

If in D10 you put the percent completed the in E10 put

Please Login or Register  to view this content.

With this formula you can fill up or down.

[tony7262]

Hi crushdrinker06,

Thanks for the quick reply, this seems really helpful. However in trying to be clever I created an example and then was planning to just copy the formula over into the true file I am working with (to preserve some confidentiality) and adjust it to my needs. However I have tried that and for some columns it seems to be working but for others it doesn't.

Can you have a look at the attachment and see if you can see what is wrong in the red cells please?

Thanks again,
Tony

[crushdrinker06]

I have corrected the formula in the the file. In BA3 it should be

Please Login or Register  to view this content.

You did a pretty good job of adapting the formula, but where there is a match()+SomeNumber that SomeNumber is how many rows from the top to the row that is the first reference which in this case is BI3 and in the previous case was A2. I hope I have explained well enough.

[tony7262]

Thank you - that solved the issue perfectly