Hi I was hoping someone could help me with my formula I have, I am basically tring to make a worksheet that calculates pay based on the inputed number of hours. I can make the formula work for either straight time hours or overtime hours depending on what i do to try to make the formula work the right way. I have rivised and revised this formula to try and solve this and not getting no luck. for example if i work 48 hours that would be 8 hours overtime i can make the formula calculate that right then if i work 32 hours it either dont calculate it right or it dont calculate at all. or with changes I made I can get it to calculate all straight time hours and not the overtime. Any help is appreciated

This is the current Formula



=IF(AND(D5>40,D5<=(D5-40)*D3*1.5+D3*40),D5*D3)

D5=Hours worked
D3=hourly wage
D8=where the function goes

Let me break that formula down and see what it's doing. Let's say h is the number of hours worked and r the hourly rate; in that case your formula saysExcel doesn't know what you want to do, only what you tell, of course; and it it won't accept this formula because you need also tell it what value to use if the condition is false. But forget about fixing that part of it; it seems to me that you need to start over. You want D8 to be something like this:Or even simpler:"The part of h > 40" could be written as just h-40, but if h < 40 that yields a negative number. You want MAX(h-40,0); that picks the larger of the two numbers, h-40 and 0, so that if h > 40 then you get h-40 but otherwise you get 0. So in D8 you put

Thank you for your reply and I appreciate the effort into looking into this. I dont understand how that last code is working as I dont see where it is calculating overtime hours. This spreadsheet that I am making needs to tell me how much my paycheck will be, after taxes. The part of the formula that we are working on is just for the Gross Pay then it goes on and calculates the taxes and deductions. So I would need to have it work and calculate automatically overtime hours as well as regular hours. For instance if I work 35 in D5 and get paid 8.00 an hour in D then cell D8 returns Gross Pay of 280.00. If I work 43 hours in D5 at the same hourly rate then D8 returns 356.00 for Gross Pay.

This is the other code I have written and cant get to work just right. the way it is now it works fine if I work overtime but if I do not it just writes false in cell D8 instead of calculating those hours.

Thanks for any help you can and have given.

clo312

=IF(D5>=40,(D5-40)*D3*1.5+D3*40)

That is tthe other part of the formula sorry forgot to paste it in the original reply

Well, I thought I got that right. Let's see: If D3=8 and D5=35, then that should be 35 hrs * 8 $/hr = $280. My formula is ...or $280. Right so far. And if D5 is 43 then it should be 40*8 + 3*8*1.5 = 320+24*1.5 = 320+36 = $356; my formula is...or $356. So it looks to me like the formula I gave you is right. I thought you meant you tried it and it didn't work; but I guess you mean you tried it, and you see that it gives the correct results, you just don't understand how it works. Right? You would like an explanation of how this calculates overtime?

Hello I appreciate your reply and I do need to deeply apologize for not explaining better and making you doubt yourself. I did not try it cause I was not understanding how it was working if you could explain it would be great as I have come up with a formuula that works and would like to have you analize it and tell me the difference if any that comes to the same results.

=IF(D5>=80,(D5-80)*D3*1.5+D3*80,D5*D3)

All I had to do was add a comma and the formula for straight time hours. In other words just told excel what to do if the logical test comes back FALSE.

Then I do have a more indepth question maybe you and I could make to work. All though I will admit I dont see how to make it work. Thanks clo312

Also how do i get my spreadsheet to open properly without having to click the little down arrow on the formula bar. When I open the file it shows a blank lite blue screen and then I have to click the down arrow to the far right of the formula bar to bring up the spread sheet.

Thanks again clo312

Oh yeah In my formula I had to change 40 to 80 as I get paid every two weeks, but that is where the more intriguing question I was talking about. Which is that everything over 40 hours is overtime still for 1 week so for example I could work 43 hours 1 week and only 32 hours the next which totals 75 hours, In my formula right now it will calculate that as all straight time since the 2 week total is not more then 80 but in reality I would be paid for 3 hours overtime. I dont know how to add that equation in the formula.

clo312

OK, the holidays are over so I'm back. Sorry for the delay.

First, about the explanation. This really has more to do with algebra than with Excel, so watch closely:

You need the sum of two parts: a) regular hours (ie up to 40) times the rate r, plus b) overtime hours (any hours over 40) times (r times 1.5). Algebraically that might look like this:But watch this:So I can substitute t + o/2 back into the formula:
In Excel, that looks like this:...which is the formula I gave you.

Now let's take a look at the formula you used:
Yeah, that should work, although you could make it "D5>80" rather than "D5>=80" in the IF clause, if you want. It looks more complex, but it really isn't; it just uses IF instead of straight calculation, and it accomplishes the same result.

About using 80 hours: If overtime is calculated weekly (that is, based on whether you worked more than 40 hours in a week rather than more than 80 hours in two weeks), then you cannot accurately calculate overtime based on the two-week total. You may be paid bi-weekly, but your formula has to know the weekly figure in order to figure OT correctly. There's no way around that.

About "opening the workbook correctly": You know, until you pointed it out I'd never noticed the down arrow to the right of the formula bar. I didn't know it existed. I see now what it does, but I don't know what it has to do with opening the workbook. Maybe post that question in the forum separately, so someone else will notice it and answer.

Thank you for your help in getting the formula right I do appreciate it, as to the other question calculating the overtime weekly while bieng paid bi weekly, that make sense and that is why i had responded i dont see how it could be done but I just thought of something....... I do not know how to do it but is there a way to create a formula across worksheets???? what i could do is create 2 work sheets one for each week then a third worksheet that totals the two worksheets together???? thus giving me the overtime weekly and still creating a two week calculator.
Thanks again
clo312

So you are saying if they worked more than 40 hours and they work less than 40 hours plus other stuff do something

Couple of problems;
In =if(AND(d5>40,D5<=(D5-40)*D3*1.5+D3*40),D5*D3)) you haven't terminated the AND properly.

An OR would be better but your logic is a little overcomplicated.

Assuming you rewrote it to say =IF(OR(D5>40,D5<=(D5-40),(D5-40)*D3*1.5+D3*40),D5*D3) then you'd calculate overtime if they'd worked more than 40 hours but it's overly complicated.

How about the following which calculates overtime over 40 hours else normal pay ? Always keep ifs as simple as possible.
=if(d5>40,(D5-40)*D3*1.5+D3*40),D5*D3)

Optionally this works as well
=d3*40 +(if(d5>40,(d5-40)*1.5*d3,0)

Yes, you can!

Try this:
1) Create a workbook with three worksheets.
2) Type some data into the first one or two worksheets.
3) On the third worksheet, pick a cell and type "=". Do not hit <Enter> yet.
4) Leaving that formula unfinished, navigate to another worksheet, either by clicking on the tab or just hitting <Ctrl-PgDn> and <Ctrl-PgUp>.

Note that in the formula bar, your formula is sitting there still waiting to be completed.

5) Once on the selected worksheet, select a cell.

Note that in the formula bar, Excel has filled in the address of the cell you selected.

6) Hit <Enter>. The cell on your third worksheet now points to the cell on the other worksheet. You can use this syntax to combine values on other cells in various ways, just plugging them into formulae. You can even do it from other worksheets, if you want to, though that is not always convenient.

Thank you so very much for the reply back sorry it has taken me so long to reply I have been working alot and free time is been spent loading a new operating system which proved to take days and be a pain in the butt, all set with it for now till the wife finds out and either pulls my hair or says it will be good enough. Must say Windows 8 is a pain in the butt.

Any way i will look that over and reply back again cause I lost my exact idea and dont know how to make that idea work so I have to think on it again.

Thanks again and hope to stay in touch

clo312