Solved Need Excel to return value based on multiple cells & values

November 5, 2013 at 15:58:30
Specs: Windows 7
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!


See More: Need Excel to return value based on multiple cells & values

Report •


#1
November 6, 2013 at 12:09:49
✔ Best Answer
Your text doesn't match your math.

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

"reduced by the % 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


Report •

#2
November 6, 2013 at 14:45:47
Thanks for your help and for drawing the "reduced to" conclusion which is what I meant. This works!


Report •

Related Solutions


Ask Question