Thanks:  0
Likes:  0

1. 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 ]

2. If I understood you correctly, you should use:

=G6+F9+F10 in G4

and

=CEILING(G4;4) in F11

Did I?

3. 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?

Dave

4. 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!

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

6. 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 ]

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

8. 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

9. 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 ]

10. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•