Rounding up hours

daveray · Mar 28th, 2002, 10:32 AM

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 ]

Sbirro Cornuto · Mar 28th, 2002, 11:00 AM

If I understood you correctly, you should use:

=G6+F9+F10 in G4

and

=CEILING(G4;4) in F11

Did I?

daveray · Mar 28th, 2002, 11:17 AM

Almost but it was I that made the mistake.

I wrote

Quote:

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?

But I forgot to include the following.

The total "extra hours" are summed in G12 using =sum(F9:F11) and to get my total hours I simply put the total in G14 using = G6+G12

Would it be easier of I sent you a copy of the sheet?

Thanks for your reply

Dave

Sbirro Cornuto · Mar 28th, 2002, 11:26 AM

Yes, please send me a copy of your Sheet, but I hope you are not in hurry cause I got to run now and I'm able to return to this issue no earlier than tomorrow, sorry man!
I'll be glad to take a look at it then, though!

Tom Schreiner · Mar 28th, 2002, 11:47 AM

Hi
I'll try and help you if you want to send me a copy?
Tom
tstom@hotmail.com

Tom Schreiner · Mar 28th, 2002, 12:36 PM

Hi
Put this formula where you need it to go.
Will round total upto the next four.
Will leave the total alone if it is a multiple of four.
=IF(MOD((F9+F10+G6),4)<>0,CEILING(F9+F10+G6,4),(F9+F10+G6))
Have a Nice Day!
Tom

[ This Message was edited by: TsTom on 2002-03-28 06:36 ]

IML · Mar 28th, 2002, 02:09 PM

one more offering
=ROUNDUP(SUM(G6,F9:F10)/4,0)*4

jleslie48 · Mar 28th, 2002, 02:19 PM

I believe you have a basic misunderstanding of number systems. If you change your way of thinking the solutions to this, and future problems of this sort, will be much easier. Lets examine your number system a little more carefully.

You are trying to work in 4-hour increments, but your calulations are all based on a number system based on 1 hour increments. Your 1 hour increments are using the base 10 arithmetic that we have all grown up with. what you need is a base 4 number system and your problems will go away.

For example, lets make up a new base type called fhi (four hour increments). to convert from 1 hour increments to fhi, you simply divide by 4, ie, 12.8 hours = 12.8/4 = 3.8 fhi. as you can now see, the fhi number will work with the functions ceiling,
floor, average, +,-,*,/, etc. Working only with cells that are of fhi format, do all your calculations. when you are finished, you
can then convert back to 1 hour increments, ie, =ceiling(3.8fhi) = 4fhi*4=16 hours.

- leslie

daveray · Mar 28th, 2002, 04:27 PM

Leslie

Your comments about thinking were most welcome. I have a fair understanding of Maths and number bases but I am dreadful at translating that into formulae that Excel understands. Unfortunately I know nothing about the CEILING function and my knowledge of Excel is limited to the basic functions.

I am learning though through this board and your idea was excellent - thanks

I always like the self help approach!

Dave

[ This Message was edited by: daveray on 2002-03-28 10:28 ]

Ian Mac · Mar 28th, 2002, 04:31 PM

Sorry to but in,

the help file for CEILING is:

Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.

Syntax

CEILING(number,significance)

Number is the value you want to round.

Significance is the multiple to which you want to round.

Remarks

· If either argument is nonnumeric, CEILING returns the #VALUE! error value.
· Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.
· If number and significance have different signs, CEILING returns the #NUM! error value.

Examples

CEILING(2.5, 1) equals 3

CEILING(-2.5, -2) equals -4

CEILING(-2.5, 2) equals #NUM!

CEILING(1.5, 0.1) equals 1.5

CEILING(0.234, 0.01) equals 0.24

If you don't understand a Function ALWAYS try the help files first, they're can be more informative than any advice on the board, the reason I generally find is that people are nearly always saying the same as the help file but in a longer way (I'm VERY guilty of this myself).

If a help file doesn't do then it's time for the Experts on here.

Mar 28th, 2002, 04:31 PM	#10
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	Sorry to but in, the help file for CEILING is: Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel. Syntax CEILING(number,significance) Number is the value you want to round. Significance is the multiple to which you want to round. Remarks · If either argument is nonnumeric, CEILING returns the #VALUE! error value. · Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs. · If number and significance have different signs, CEILING returns the #NUM! error value. Examples CEILING(2.5, 1) equals 3 CEILING(-2.5, -2) equals -4 CEILING(-2.5, 2) equals #NUM! CEILING(1.5, 0.1) equals 1.5 CEILING(0.234, 0.01) equals 0.24 If you don't understand a Function ALWAYS try the help files first, they're can be more informative than any advice on the board, the reason I generally find is that people are nearly always saying the same as the help file but in a longer way (I'm VERY guilty of this myself). If a help file doesn't do then it's time for the Experts on here. __________________ "Have a good time......all the time" Ian Mac

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 10:32 AM	#1
daveray Join Date: Mar 2002 Posts: 28	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 ]

Mar 28th, 2002, 11:00 AM	#2
Sbirro Cornuto Join Date: Mar 2002 Location: Little Italy Posts: 93	If I understood you correctly, you should use: =G6+F9+F10 in G4 and =CEILING(G4;4) in F11 Did I?

Mar 28th, 2002, 11:26 AM	#4
Sbirro Cornuto Join Date: Mar 2002 Location: Little Italy Posts: 93	Yes, please send me a copy of your Sheet, but I hope you are not in hurry cause I got to run now and I'm able to return to this issue no earlier than tomorrow, sorry man! I'll be glad to take a look at it then, though!

Mar 28th, 2002, 11:47 AM	#5
Tom Schreiner Join Date: Mar 2002 Posts: 6,205	Hi I'll try and help you if you want to send me a copy? Tom tstom@hotmail.com

Mar 28th, 2002, 12:36 PM	#6
Tom Schreiner Join Date: Mar 2002 Posts: 6,205	Hi Put this formula where you need it to go. Will round total upto the next four. Will leave the total alone if it is a multiple of four. =IF(MOD((F9+F10+G6),4)<>0,CEILING(F9+F10+G6,4),(F9+F10+G6)) Have a Nice Day! Tom [ This Message was edited by: TsTom on 2002-03-28 06:36 ]

Mar 28th, 2002, 02:09 PM	#7
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	one more offering =ROUNDUP(SUM(G6,F9:F10)/4,0)*4

Mar 28th, 2002, 02:19 PM	#8
jleslie48 Join Date: Mar 2002 Posts: 7	I believe you have a basic misunderstanding of number systems. If you change your way of thinking the solutions to this, and future problems of this sort, will be much easier. Lets examine your number system a little more carefully. You are trying to work in 4-hour increments, but your calulations are all based on a number system based on 1 hour increments. Your 1 hour increments are using the base 10 arithmetic that we have all grown up with. what you need is a base 4 number system and your problems will go away. For example, lets make up a new base type called fhi (four hour increments). to convert from 1 hour increments to fhi, you simply divide by 4, ie, 12.8 hours = 12.8/4 = 3.8 fhi. as you can now see, the fhi number will work with the functions ceiling, floor, average, +,-,,/, etc. Working only with cells that are of fhi format, do all your calculations. when you are finished, you can then convert back to 1 hour increments, ie, =ceiling(3.8fhi) = 4fhi4=16 hours. - leslie

Mar 28th, 2002, 04:27 PM	#9
daveray Join Date: Mar 2002 Posts: 28	Leslie Your comments about thinking were most welcome. I have a fair understanding of Maths and number bases but I am dreadful at translating that into formulae that Excel understands. Unfortunately I know nothing about the CEILING function and my knowledge of Excel is limited to the basic functions. I am learning though through this board and your idea was excellent - thanks I always like the self help approach! Dave [ This Message was edited by: daveray on 2002-03-28 10:28 ]