I have created a bonus plan for my team. It has 3 factors that could reduce the value. If all 3 factors = 100%, they get 100% of bonus. If any factors are less than 100%, then their bonus is reduced by the % of the lowest factor to give them their payout.

Example1: If any factors are 0%, bonus is 0%,

Example 2:If the factors are 100%, 75%, and 50%, then payout would be at 50% (the lowest factor). Below are the parameters of the 3 factors.Factor A = 100%, 75%, 50%,0%

Factor B = 100%, 75%, 0%

Factor C = 100%, 50%I hope this is clear enough. Thanks in advance for your help inautomating this recurring process for me if possible!

Your text doesn't match your math.

"If any factors are less than 100%, then their bonus is reducedbythe % of the lowest factor to give them their payout. ""reduced

bythe % of the lowest factor" means that:If any factors are 0%, bonus is "reduced

by"0%I assume you mean that the bonus is "reduced

to"the percentage of the lowest factor. That is the only way a Factor of 0% would equal a bonus of 0%.Assuming that you meant "reduced

to", let's go from there.You didn't supply any information related to the layout of your data, so it's hard to be specific with a solution.

Assuming that the percentage for each Factor is stored in a cell, you should be able to use the MIN function to determine the Bonus amount.

For example, let's say you start with this layout:

A B C D E 1 Max Bonus Factor A Factor B Factor C Actual Bonus 2 $1,000 100% 75% 100% =A2*MIN(B2:D2)The formula in E2 should return $750, which is 75% of the Max Bonus.

Is that what you were looking for?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thanks for your help and for drawing the "reduced to" conclusion which is what I meant. This works!

Ask Your Question

Weekly Poll