Formula to conscise

Well-known Member
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.

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

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

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

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

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

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

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!

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

Replies
34
Views
2K
Replies
4
Views
221
Replies
3
Views
312
Replies
5
Views
279
Replies
4
Views
238

1,217,498
Messages
6,136,997
Members
450,037
Latest member
Tao86

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.

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

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