Formula Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Would anyone know an if statement for calculating pay rates? The rules are:
For the first 20 Hours you get hourly wage * hours
For the next 10 Hours you get hourly wage * (hours*1.5)
For anything abouve 30 hours you get hourly wage * (hours*2)

Thank you,

Denise
 
Try this.

IF(hours<=20,hours*wages,IF(AND(hours>20,hours<=30),(hours*wages+(hours-20)*wages*1.5),(hours*wages+(hours-20)*wages*1.5+(hours-30)*2)))
 
Hi Denise
This is a bit ugly but works.
Hours worked in A1, Pay rate in A2
In B3 put:
=IF(B1>30,(B1-30)*B2*2+10*1.5*B2+B2*20,IF(B1>20,(B1-20)*1.5*B2+B2*20,B1*B2))

HTH
Michael
 
Hi,

=hourly wage * MAX(hours, 1.5*hours-10,2*hours-25)

For example, if hours is in A2, and hourly rate is in B2, the formula is

=B2*MAX(A2,1.5*A2-10,2*A2-25)

Regards,
B. R. Ramachandran
 
Hi Denise
Oops !! Ithink I told you A1 and A2 for the input data, when it should have
been B1 and B2
Sorry for the confusion.
Michael
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

wages - multiply hours and minutes by number of hours worked 6
Overtime Calculations 4
linking different worksheets in a workbook for a formula 1
Excel Catagorise AM or PM 7
Formula assistance 10
Formula for Calculating Pay 1
time sheets 1
why is answer rounded up? 1

Back
Top