Formula to conscise

[cadence]

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

 

[GlennUK]

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]

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]

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]

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]

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]

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

=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]

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]

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]

Slight amendment from my last log:

This is the formula that I am working with.

 =(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