I use a spreadsheet to calculate labour time for engineers etc. and we calculate all our work to 4 hour multiples.
For example a 3 hour job indreases to 4 hours, an 11 hour job rounds up to 12 and a 12.8 hour job rounds up to 16.
I am in need a formula that will always round up to the nearest four hours using the following data.
In G6 I have a Subtotal of the labour hours
In F9 I have an allowance (hours for awkward access etc)
In F10 I have an allowance for excessive travelling (ie 4 hours driving to complete a 1 hour job)
I want to put the roundup formula in F11 and the overall total hours are displayed in G4.
Someone gave me the formula
=IF(G6+F9+F10>0, ((TRUNC((G6+F9+F10)/4)+1)*4)-G6-F9-F10, 0)
It sort of works but if I add sufficient hours in any of my allowances to make the total divisible by 4 the formula still adds another 4 hours?
This is totally beyond me and I hope I have explained it correctly?
All help would be welcomed. VBA code or formula.
Many thanks in anticipation
Dave
This message was edited by daveray on 2002-03-28 04:33
This message was edited by daveray on 2002-03-28 04:34
For example a 3 hour job indreases to 4 hours, an 11 hour job rounds up to 12 and a 12.8 hour job rounds up to 16.
I am in need a formula that will always round up to the nearest four hours using the following data.
In G6 I have a Subtotal of the labour hours
In F9 I have an allowance (hours for awkward access etc)
In F10 I have an allowance for excessive travelling (ie 4 hours driving to complete a 1 hour job)
I want to put the roundup formula in F11 and the overall total hours are displayed in G4.
Someone gave me the formula
=IF(G6+F9+F10>0, ((TRUNC((G6+F9+F10)/4)+1)*4)-G6-F9-F10, 0)
It sort of works but if I add sufficient hours in any of my allowances to make the total divisible by 4 the formula still adds another 4 hours?
This is totally beyond me and I hope I have explained it correctly?
All help would be welcomed. VBA code or formula.
Many thanks in anticipation
Dave
This message was edited by daveray on 2002-03-28 04:33
This message was edited by daveray on 2002-03-28 04:34