Looking to make an excel formula for hourly persons where I can enter the forecasted number of hours for a given week and the formula will generate the straight wage, 1.5 wage, and 2.0 wage. Trying to get figure out the following (where X = number of forecasted hours and A1 is cell with hourly wage):

IF 40 or less, then X * A1
IF greater than 40 and less than 55, then (40 + ((X - 40)* 1.5))) * A1
IF greater than 55 then (66.5 + ((X - 55) * 2))) * A1

Any one here know how to enter this into a cell?

quote:

Originally posted by LS1 GTO:
Looking to make an excel formula for hourly persons where I can enter the forecasted number of hours for a given week and the formula will generate the straight wage, 1.5 wage, and 2.0 wage. Trying to get figure out the following (where X = number of forecasted hours and A1 is cell with hourly wage):

IF 40 or less, then X * A1
IF greater than 40 and less than 55, then (40 + ((X - 40)* 1.5))) * A1
IF greater than 55 then (66.5 + ((X - 55) * 2))) * A1

Any one here know how to enter this into a cell?

I don't have time to fool with it right now but you're on the right track. You'll need to nest the formulas with the IF function probably carrying the load. If TRUE is returned, then do the math. If FALSE is returned, then nest the next iteration of IF.
Please note that in the examples in your post, you've not dealt with the absolute value of 55.

quote:

Originally posted by FRANKT:

quote:

Originally posted by LS1 GTO:
Looking to make an excel formula for hourly persons where I can enter the forecasted number of hours for a given week and the formula will generate the straight wage, 1.5 wage, and 2.0 wage. Trying to get figure out the following (where X = number of forecasted hours and A1 is cell with hourly wage):

IF 40 or less, then X * A1
IF greater than 40 and less than 55, then (40 + ((X - 40)* 1.5))) * A1
IF greater than 55 then (66.5 + ((X - 55) * 2))) * A1

Any one here know how to enter this into a cell?

I don't have time to fool with it right now but you're on the right track. You'll need to nest the formulas with the IF function probably carrying the load. If TRUE is returned, then do the math. If FALSE is returned, then nest the next iteration of IF.
Please note that in the examples in your post, you've not dealt with the absolute value of 55.

Thank.

Caught that about 55 when entering the formula.

How do I nest? (Monkey's comment(s) not withstanding. )

This is where I am so far:

=IF((<40 THEN Hours*Wage), IF(>40 AND 55 OR <55 THEN ((40 + (Hours - 40) * 1.5))*Wage), if(>55 THEN (66.5 + (Hours - 55) * 2)) * Wage)

I'm the treasurer for our local community water system. We use a similar formula for our budget as we have tiered rates...If you use 6,000 gallons you pay X per gallon, 6,000 - 9,000 gallons you pay a different rate, etc... Took me a while to get it to work properly. If you want, shoot me an email and I will send you the Excel file. The formula has a bunch of cell references so it wont make sense if I paste the formula here. Email is in my bio.

We also have a minimum that everyone pays. This is the formula:
=IF(AND(R5>=0,R5<='Rate Final'!$D$14),'Rate Final'!$H$14,IF(AND(R5>='Rate Final'!$C$15,R5<='Rate Final'!$D$15),(ROUND((R5-'Rate Final'!$D$14)/'Rate Final'!$E$15,2)*'Rate Final'!$G$15)+'Rate Final'!$H$14,IF(AND(R5>='Rate Final'!$C$16,R5<='Rate Final'!$D$16),(ROUND((R5-'Rate Final'!$D$15)/'Rate Final'!$E$15,2)*'Rate Final'!$G$16)+'Rate Final'!$H$15,(ROUND((R5-'Rate Final'!$D$16)/'Rate Final'!$E$15,2)*'Rate Final'!$G$17)+'Rate Final'!$H$16)))

Here my take on it:

Think your constant of 66.5 s/b 62.5 but that is accounted for in the formula (15*1.5).

=IF(A5<=40, A5*B5, IF(AND(A5>40,A5<=55),((A5-40)*1.5+40)*B5, IF(AND(A5>55), ((40*B5)+(15*1.5*B5)+((A5-55)*2)*B5), "NA")))

Cell A5 is the gross Hours, Cell B5 is the Straight rate for the wage, The formulas above do not round so if rounding is needed, you'll have to put the computations in a "ROUND(Formula, #places)" - and have more fun matching up parenthesis!

quote:

Originally posted by Sailor1911:
Here my take on it:

Think your constant of 66.5 s/b 62.5 but that is accounted for in the formula (15*1.5).

=IF(A5<=40, A5*B5, IF(AND(A5>40,A5<=55),((A5-40)*1.5+40)*B5, IF(AND(A5>55), ((40*B5)+(15*1.5*B5)+((A5-55)*2)*B5), "NA")))

Cell A5 is the gross Hours, Cell B5 is the Straight rate for the wage, The formulas above do not round so if rounding is needed, you'll have to put the computations in a "ROUND(Formula, #places)" - and have more fun matching up parenthesis!

The parens were/are correct - thank you!!

Many ways to skin this cat. Here's my take:

B1 is the cell that contains how many hours were worked. A1 is your hourly rate.

= IF(B1 <= 40, B1 * A1, IF(B1 <= 55, 40 * A1 +(B1 - 40) * A1 * 1.5, 40 * A1 + 15 * A1 * 1.5+( B1 - 55) * A1 * 2))

The formula says if hours are less than or equal to 40, multiply by the hourly rate, if less than or equal to 55, then multiply 40 by the hourly rate plus the hours over 40 multiplied by the hourly rate times 1.5 and lastly, if the hours are greater than 55, then multiply 40 by the hourly rate plus 15 hours by the rate times 1.5 plus the hours over 55 times the rate times 2.

My formula doesn't need to check whether it's between 40 and 55 or greater than 55. Excel does the If statement in sequence and the sequence covers all the possibilities: less than or equal to 40, less than or equal to 55, and greater than 55 hours.

If you want something "fancier", this formula will also work:

=(MIN(B1,40) + MIN(MAX(0,B1-40),15) * 1.5 + MAX(0,B1-55)*2) * A1

It just adds up all the wage equivalent hours first then multiplies it by the hourly rate. For exampe, the middle term MIN (MAX (0, B1 - 40), 55) * 1.5 says first take the hours work above 40 up to 15 hours above but if it's less than 40, then return zero then multiply that by 1.5.

The third term just says take the hours above 55 and multiply by 2.

WRT the formula, several excellent answers. But the logic may not cover all real world possibilities. For example, if there is a paid holiday or other PTO during the week, there may be more than 40 compensated hours without overtime (OT rates apply only after 40 hours of actual on-the-clock work).

My technique is less compact, but easier for me to follow. I'd use IF statements to set 3 new variables, C1, C2, and C3 to 1 or 0 depending on which set of parameters were true (only one set to 1, the others 0). Then I'd take the 3 wage formulae each enclosed in parentheses and multiplied by the appropriate C variable and add them together. Only the wage formula that has a C = 1 will contribute to the sum.

I'm a simple man and complex multi-IF logic just asks for mismatched parentheses.

flashguy