CODEWITHSUNDEEP
excelmaxcelllimit
Max
Max

Reputation: 13

Limiting cell values in formulas

I would like to use some kind of limit or over/under rule in a formula. The premise is if a number exceeds a certain value, then I only want to take that certain value. Here's an example:

If an employee's hours exceed 80 hours per month, only use 80 hours for accounting purposes. Let's say my employee Bob's timesheet looks like this:

Jan = 20 hours

Feb = 40 hours

Mar = 100 hours

Apr = 60 hours

etc.

If Bob gets paid $100/hours, then his final payment should be $100/hour * total hours works. Or in excel format, something like =100*sum(b1:b12) if hours are stored in column B.

BUT, I only want to take a Maximum of 80 hours each month... So the hours in the above example, as far as accounting is concerned, would look like this:

Jan = 20 hours

Feb = 40 hours

Mar = 80 hours

Apr = 60 hours

etc.

I'd rather not build in another column for =if(b1>80,80,b1). And to be honest, it's bugging me now that I can't figure it out.

Please help!

Upvotes: 1

Views: 343

Answers (2)

tigeravatar
tigeravatar

Reputation: 26650

For a regular (non array) formula solution:

=SUMPRODUCT((B1:B4<80)*B1:B4+(B1:B4>=80)*80)

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152505

Try this array formula:

=100 * SUM(IF(B1:B12>80,80,B1:B12))

This is an array formula and must be confirmed with Ctrl-Shift-Enter when exiting edit mode instead of Enter or Tab.

enter image description here

Upvotes: 0

Related Questions