help writing formula to determine salary/overtime

sockmodel7

New Member
Joined
Dec 5, 2005
Messages
5
Alright, here's the deal, the wonderful people at my college bookstore didn't manage to get my textbook in until 1 1/2 weeks left in the semester and I need some help putting together a formula in Excel 2003 for the final that will determine the salary of multiple employees, at different hours, pay per hour and overtime.

i.e.
employee 1: 40 hours at $9
emp. 2: 48 at 10
emp. 3: 42 at $8
emp. 4: 44 at $9.50

All hours after 40 hours are time and a half. If someone could help construct a formula for this it would be hugely appreciated because my efforts to do so have only resulted in profanity.

We'll also have to determine pay after 8% taxes, so I'm assuming that can be done with a forumla sum=gross pay*8% followed by the auto sum feature for all columns in that field?
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
I am assuming you are looking for one formula that does all the calculations and sums?

If so, try

=SUM(IF(A1:A4>40,A1:A4*B1:B4+(A1:A4-40)*B1:B4*1.5,A1:A4*B1:B4))

Confirmed with Ctrl+Shift+Enter.

You can then multiply that by 1.8 for your pay after tax calculation.

If you looking for individual calculations: =IF(A1>40,A1*B1+(A1-40)*B1*1.5,A1*B1) copied down and then use sum(c1:c4) at bottom of range.

Note: Adjust all ranges to suit your table.
 

sockmodel7

New Member
Joined
Dec 5, 2005
Messages
5
I will send $25 with Pay Pal or Stormpay to the first person who can put together a formula for me. I'm getting desperate haha
 

sockmodel7

New Member
Joined
Dec 5, 2005
Messages
5
I think that might be what I'm looking for, so even though I posted it after if you have either pay pal or stormpay leave an address and I'll send along the cash for helping out
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

First, make sure it works by doing the calculations manually,

Second, your welcome. This is a free help forum. We don't expect payment. (except for maybe a Thank You! :)
 

sockmodel7

New Member
Joined
Dec 5, 2005
Messages
5
Well I appreciate, thank you kindly. Now just to clarify what would A1 and A4 represent? regular hours and overtime hours?
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

The A1:A4 is the range where your hours are located for each employee and B1:B4 is the corresponding wages.

Note: Please change the formula to this one below. I noted that I a fault with the previous one I posted.

=SUM(IF(A1:A4>40,(40*B1:B4)+(A1:A4-40)*B1:B4*1.5,A1:A4*B1:B4))

Remember to evaluate it with CTRL+SHIFT+ENTER, not just Enter
 

sockmodel7

New Member
Joined
Dec 5, 2005
Messages
5
Can't thank you enough for the help. You deserve a nomination for man of the year, or at the very least a sticker
 

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,454
Members
412,595
Latest member
slim313
Top