Using IF or alternative function

**jb2235** · 02-12-2013, 12:31 PM

Hi there, I need help using an IF statement. Or, if there is a better formula to use, I'm all ears. Here is what I'm trying to do. We've hired a freelancer who has offered us a bulk rate discount. For any given month, if we give him 25 or less hours of work, his rate is $50 per hour. Anything 26 hours or more would be calculated at a rate of $40 per hour. I'd like to track his hours throughout the month and calculate when it hits 26 hours or more. Using the IF statement seems the best here, but the second half of my formula seems messy. Here's where I'm at:

A1: $50
A2: $40

A4: Hours
B4: Rate
C4: Amount

A5: 5
B5: =IF(SUM($A$5:A5)>25,A2,A1)
C5: =A5*B5

A6: 22
B6: =(SUM($A$5:A6)>25,A2,A1)

This is where I'm stuck. Both formulas are incomplete and don't calculate the two rates if indeed it is over 25 hours. The second formula doesn't factor in multiplying the hours at one rate plus the hours at a different rate. I know the calculations I need to do, but can't think of an easier way to do it. I need to be able to calculate the number of hours that are 25 hours and under at $50 and anything over that at $40 where it resets at the end of each month. Thoughts?

**Fotis1991** · 02-12-2013, 12:50 PM

Does this works for you?

=(SUM(A5:A6)*A1)-(SUM(A5:A6)-25)*(A1-A2)

**Cookstein2** · 02-12-2013, 12:57 PM

how about this?

**jb2235** · 02-12-2013, 01:15 PM

Thanks. It's close. But, technically, 20 of those hours in line 2 should be multiplied by 50 and the remaining 2 hours multiplied by 40. Then, any subsequent hours thereafter will be multiplied at $40.