excel - Limiting cell values in formulas -

i use kind of limit or over/under rule in formula. premise if number exceeds value, want take value. here's example:

if employee's hours exceed 80 hours per month, use 80 hours accounting purposes. let's employee bob's timesheet looks this:

jan = 20 hours

feb = 40 hours

mar = 100 hours

apr = 60 hours

etc.

if bob gets paid $100/hours, final payment should $100/hour * total hours works. or in excel format, =100*sum(b1:b12) if hours stored in column b.

but, want take maximum of 80 hours each month... hours in above example, far accounting concerned, this:

jan = 20 hours

feb = 40 hours

mar = 80 hours

apr = 60 hours

etc.

i'd rather not build in column =if(b1>80,80,b1). , honest, it's bugging me can't figure out.

please help!

try array formula:

=100 * sum(if(b1:b12>80,80,b1:b12))

this array formula , must confirmed ctrl-shift-enter when exiting edit mode instead of enter or tab.

Ben