Help: SUM/ max limit / return 0 if reached

[happydays886]

Hi All,

I'm trying to calculate the benefit off of income for several people.

The idea is once each individual's 'PAY sums to $51,400' or 'BENEFIT = 2,544,36' that the benefit calculation should return a value of 0 AND the sum of the benefit for each individual should = $2,544.36.3

I'm wondering if this is possible in a single calculation within the "benefit" column, or if this kind of formula requires a 'benefit' column to work from?

All help is immensely appreciated.

Thank you,

HD

[ron2k_1]

If you enter this in E6 and drag down, does it do what you want?

Please Login or Register  to view this content.

I'm not sure what you mean by:

The idea is once each individual's 'PAY sums to $51,400' or 'BENEFIT = 2,544,36' that the benefit calculation should return a value of 0 AND the sum of the benefit for each individual should = $2,544.36.3

So if the formula doesn't do what you want, then kindly expand your explanation.

Ron

[ron2k_1]

Sorry, it seems that you want the benefit to be calculated by Employee, so how about this on E6 and dragged down?

Please Login or Register  to view this content.

And on F6:

Please Login or Register  to view this content.

Again, if the above does not do what you want show us on column E (and F if there is anything there that you want) the figures that you wish excel to return. Highlight them for us.

Ron

[happydays886]

Hi,

I attached a copy of the file with yellow highlighting for what I'd like to see a formula do.

What this formula should do is populate for a benefit amount (pay*%) until the total benefit reaches the benefit max ($2,544.36). In each case, when the benefit max is exceeded, the formula then takes the benefit amount, using 'joy' as example, and reduces the benefit amount to an amount that would ensure the sum of all of her benefits equals exactly 2,544.36.
- Cell G21 = 69 and not 495 as a result
- All benefit amounts for Joy following June are populated 0

[ron2k_1]

Did you use my formula on E6? It is doing exactly what you want

[happydays886]

omg! This works! Your formula works!

I am at a loss of words - thank you so much!

[ron2k_1]

No prob. You're very welcome.

You can mark the thread "SOLVED" if you got a chance.

Ron