Rounding up hours

daveray

New Member
Joined
Mar 3, 2002
Messages
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Almost but it was I that made the mistake.

I wrote
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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
Back
Top