Sum to a max value?

[Fish1]

I have a template I'm using to see what wage increases would cost. One idea is to give a wage increase to everyone under a specific hourly wage and give a different raise to anyone already over that same specific wage. For a simple example, right now I have:

Please Login or Register  to view this content.

I have a list of many employees. I'd like to be able to look at the cost of giving all employees under $20.00 a $1.00 hourly increase, and all employees at $21.00 -$25.00 a $0.50 increase.

What formula can I enter in cell E8 (New Wage) so it will add C8 (starting wage) and D8 (hourly increase), but only apply the $1.00 increase if the starting wage is $20 or less. And also add $.50 if the starting wage is between $21-$25?

Thanks!!!

[ben_hensel]

In E8:

Please Login or Register  to view this content.

Full of references would be:

Please Login or Register  to view this content.

Like that?
Note that this applies +0.50 to everyone at x from 20 <= x < 25, you were actually excluding everyone from 20 <= x < 21 from your example.

This won't increase any wage that's already 25 or greater.

Note that in edge cases (eg, 24.75 for example), this could bump people past others, who didn't get an increase because their starting wage was higher? I dunno if that matters, but it's the sort of kindof-unexpected consequence that can cause heartache in these situations.

[ben_hensel]

double-posted by accident n/m

[Fish1]

Thank you Ben! I will take a look at the formulas.

Regarding the employees passing another. We would need to make it so each employee getting a raise would max out at the next threshold. So the guy at $24.75 would max out at $25. he wouldn't get the full $.50 and pass the next person currently at $25 that wouldn't get the raise. He would catch him, but not pass him.


Could the formula include this?

[ben_hensel]

Yeah, just wrap a MIN function around it, and pull the threshold into one term, and the check in the other.

How is your data structured? I'm kinda thinking you might be better served to be using a couple lookups on a table, rather than trying to nest it all in Boolean assessors.

Can you post an example spreadsheet?

[Fish1]

I got it worked out with the help of a friend. This is what we cam up with in case anyone searches and comes across this thread for a similar question:

In my spreadsheet I have
Starting Wage - E3
Wage Increase Amount for staff $21 or less - E4
New Starting Wage for newly hired staff- E5 (E3+E4)
Wage Increase for staff between $21-$25 - E6
Max Wage after increases - E7


List of drivers begin at H3 and down column H
Starting wage for that driver starts at I3 and goes down that column
Wage increase amount starts in J3 and goes down that column
New wage for that driver after increase starts in K3 and goes down that column

In the column I have the amount of the increase (column J) I have this formula - =CHOOSE(IF($I3>=$E$7,1,IF($I3<$E$5,2,3)),0,$E$4,$E$6)
In the column I have the new wage after increase (column K), I have this formula - =CHOOSE(IF($I3>=$E$7,1,IF($I3<$E$5,2,3)),$I3,MIN($E$5,$I3+$J3),MIN($E$7,I3+J3))


Thanks for the help Ben!!!