Formula to conscise

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
hi there
I have a formula that works that calculates overtime rates depending on what variables you enter for time/half or double time to start.

eg would be an 8 hour working day with time?half from 8-10 hour then double time after that.

=SUM($AZ10*BB10)+(IF(INT(BA10)+(BA10-INT(BA10))/0.6<$E$72,0,IF($E$72<$E$74,MAX(0,(INT(BA10)+(BA10-INT(BA10))/0.6-$E$72-($E$74-$E$72)))*2+(IF((BA10-$E$72)<($E$74-$E$72),(INT(BA10)+(BA10-INT(BA10))/0.6-$E$72),($E$74-$E$72)))*1.5,(INT(BA10)+(BA10-INT(BA10))/0.6-$E$72)*2))*$FY10)*BB10

AZ10 = daily rate - user entered
BB10 = no of days - user entered
BA10 = No of hours in a day - user entered
E72 = base daily rate (after time/half starts)
E74 = double time starts
FY10 = hourly rate (daily rate/ base hourly day ie 8hr)

does anyone know of a way of condensing/simplifying this formula?

Johnny
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Please give the logic that you are aiming for in English. Trying to decipher someone's formula and infer the intention is not so easy, especially when there may be errors involved.
 

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
Thanks glen,

yes the current formula works fine, it is essentially a wage counter where the user enters the daily wage amount, number of worker hours each day and the total number of days.

from there the formula calculates the total value based on the overtime structures, ie if some one was hired for a base day of 8 hours and they worked 12, then hours 8-10 would be calculated on x1.5 and any hour after that would be on x2 time. (please refer to my cell reference as to which cell are what).

hope that clears it up and thanks for your help.

johnny
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I'm not sure I understand what's in all the cells

Can you give an example of the entry in each of the cells you've specified?

When dealing with time it's useful to know the format in which you are entering data, e.g. is the number of hours in a day 8.5 or 8:30? It makes a big difference to how the formula works
 

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528

ADVERTISEMENT

Its base 6 so you would write hallf past 8 as 8.30 or 8 and a half hours

AZ10 = daily rate - user entered (eg$500 a day)
BB10 = no of days - user entered (eg 3 days)
BA10 = No of hours in a day worked - user entered (12 hour day)
E72 = base daily rate (after time/half starts) minimum hours before overtime is calculated eg 8 hour day)
E74 = double time starts (eg 10 hours so that the difference between E74 & E72 is calculated at x1.5 rate
FY10 = hourly rate (daily rate/ base hourly day ie 8hr)

So that an 12 hour day would calculate 2 hours at time and a hald and 2 hours at doble time = the daily rate and then times all of t by the number of days.

hope that clarifies it a little more.

thanks barry if you can make sense
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Do you have to input times in that format, that's one of the things that makes your formula so complex?

If you input times in time format, i.e. with a colon like 8:30 then you should be able to use

=(E72+MEDIAN(E74-E72,0,BA10-E72)*1.5+MAX(0,BA10-E74)*2)*BB10*FY10*24
 

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528

ADVERTISEMENT

Barry thanks for the reply,

I think I have over confused the issue.

=(daily rate(eg500) + Overtime(eg12hrs) (hours>greater than base of 8 hours) where by hours >8<10@ x1.5 + hours>10 @ x2) * No days

but I need the equation to be that no matter what user value is substituted for
base hours, x1.5 & x2 ie 8hr for base & >x1.5, & >10hr for x2 the equation can produce a value.

hope that makes sense, if it in time of 8.30 or 8.5 i can go either way.

I've made the formula so that you can work on my example
Code:
=SUM(G2*F2)+(IF(INT(E2)+(E2-INT(E2))/0.6<B2,0,IF(B2<C2,MAX(0,(INT(E2)+(E2-INT(E2))/0.6-B2-(C2-B2)))*2+(IF((E2-B2)<(C2-B2),(INT(E2)+(E2-INT(E2))/0.6-B2),(C2-B2)))*1.5,(INT(E2)+(E2-INT(E2))/0.6-B2)*2))*A2)*F2

A2 - hourly rate
B2 - base hours
C2 - double time starts after

E2 - Hours worked - user entered
F2 - no.of days - user entered
G2 - Daily rate - user entered

H2 - total

PS if its easy to factor in a triple time factor that would be a plus?

cheers barry
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Assuming you input all hours in decimal format, e.g. 8.5 or 8.75 then you could use

=(G2+(MAX(0,E2-C2)*2+MEDIAN(0,E2-B2,C2-B2)*1.5)*A2)*F2

If you have a triple time threshold in D2 then you can revise that as follows:

=(G2+(MAX(0,E2-D2)*3+MEDIAN(0,E2-C2,D2-C2)*2+MEDIAN(0,E2-B2,C2-B2)*1.5)*A2)*F2

.....or for an entirely different appoach - this will give you the same result as formula 2

=(SUMPRODUCT(--(E2>B2:D2),E2-B2:D2,{1.5,0.5,1})*A2+G2)*F2
 

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
You are a genius!

Thanks for that, sorry I took you on the long way to get it.

I can now put this into my other argument without it being so complicated.

johnny

Just on a side note is there a way so that if triple time is blank, it does not affect the equation.

It seems on my version that if I leave triple blank it almost doubles the out come? - any suggestion, but understand if your over it!
 

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
Slight amendment from my last log:

This is the formula that I am working with.

Code:
 =(G2+(MAX(0,E2-D2)*3+MEDIAN(0,E2-C2,D2-C2)*2+MEDIAN(0,E2-B2,C2-B2)*1.5)*A2)*F3

I was just wondering how I change it so that the Hours E2 works on a base 6 ie so that quater past six is 6.15 and that the formula can adjust accordingly?

Any suggestions
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top