Formula Help

BobBridges · #8 01-03-2014, 09:56 AM

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:

Code:

t := total hours worked
h := t up to 40
o := overtime hours
r := regular hourly rate 
MIN := the lesser of two values
MAX := the greater of two values
---
h = MIN(t,40)       ;that is, t or 40, whichever is less
o = MAX(40-t,0)     ;if t<40 then just use 0
pay = hr + 1.5or
pay = r(h + 1.5o)   ;simplifying

But watch this:

Code:

h + o = t
h + o + o/2 = t + o/2
h + o(1+1/2) = t + o/2
h + 1.5o = t + o/2

So I can substitute t + o/2 back into the formula:

Code:

pay = r(h + 1.5o)
h + 1.5o = t + o/2
pay = r(t + o/2)

In Excel, that looks like this:

Code:

D3 := regular hourly rate
D5 := total hours worked
o = MAX(D5-40,0)
---
=D3*(D5+MAX(D5-40,0)/2)

...which is the formula I gave you.

Now let's take a look at the formula you used:

Code:

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

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need help with a formula	e_a_g_l_e_p_i	Excel	14	02-07-2013 12:37 AM
Formula help	furface00	Excel	10	02-26-2012 05:34 AM
Formula Help - Look and if and that	Corca	Excel	2	02-05-2012 09:18 AM
Help with formula please.	AndrewSE	Excel	3	04-05-2011 08:50 PM
If formula	sixhobbits	Excel	1	10-02-2009 08:02 AM