Rounding up hours

[daveray]

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

 

[daveray]

Thanks Ian but i really must clarify something here. To look in the Help files for the CEILING definition you need to knows it exists. I must admit that I had never even heard of it never mind what function it provided!

Thanks for your explanation though it was very helpful

Dave

 

[Juan Pablo González]

I have to agree with that point. And I must add that to know each function well, that can take a while !

I HAVE to believe that the Help file is getting better, at least that's what I "feel" (I don't use Excel's help a lot, only VBA's) it is, because in XP, Excel now asks "What do you want to do ?" and returns a list of possible functions.... as I said, I NEVER use this feature, but, it looks helpful !

 

[Ian Mac]

On 2002-03-28 10:27, daveray wrote:
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

I must come back on this one, as to make the comment

"...Unfortunately I know nothing about the CEILING function..."

you must first have know that there was such a function.

I agree with Juan's comments about the help files, XP also use the VBA style help which puts help 'Arguements' under the formula your typing.

I do also agree that you need a lot of practice before you know even a small ammount of functions........but you did know that CEILING exsisted before asking.

 

[daveray]

I do also agree that you need a lot of practice before you know even a small ammount of functions........but you did know that CEILING exsisted before asking.

Ian

I think you have missed something. Go back and read my first post where I volunteered the TRUNC function that someone had passed onto me. As the posts progress, you will notice that a few people then kindly volunteered the CEILING function. That function was never mentioned by me prior to those posts and up until those posts I DID NOT KNOW ABOUT THE EXISTENCE OF THE CEILING function let alone what it did.

Next in the list came the post from Leslie who applied some very helpful thinking strategy. In her post she ALSO mentioned CEILING along with Floor, average etc. I kindly thanked her and explained that I knew NOTHING about the CEILING function.

You then very kindly posted a helpful explanation of the CEILING function and I also thanked you. In the meantime Thomas had fixed my problem and emailed the sheet back to me. I then didn’t need to read the help file as

• My problem was solved
• You had very kindly posted the help file.

To reiterate my initial reply to Leslie - I knew nothing about CEILING prior to the very kind people on this board mentioning it to me.

Once again I thank everyone for their kind help and that definitely includes you Ian for taking the time to point out my supposed error.

Dave