OT; Excel help please

=3DROUNDUP((A1/B1), 0) is what you want

Luke

Put =ROUNDUP(C1,0) in D1, this will show your original calculation but D1 will then show the number of lengths you will need to buy.

Peter

Thanks very much chaps - sorted!

A generic solution to that sort of problem in the absence of a round up function, is to simply add 0.5 to the answer and then truncate all the decimal places.

But not here...he'd have to add 0.99999 and then truncate.

Yup, true, my bad. Adding 0.5 rounds off, 0.99 rounds up.

I'd have thought that it's a bit of a no-brainer that if your spreadsheet shows that you need 6¼ lengths, you go and buy 7.

Unless of course you have, say, a roughly half-length left over from a previous job, when you can get away with buying 6, scrap the useless little bit, and charge the customer for 7...

:-)

I can cope with that on smaller jobs, but for costing purposes on bigger ones its more important.

Nah! The jobs don't work out like that.

That's too easy to work out, and I wonder if he allows the necessary percentage for wastage - or would that be too difficult a calculation to make? Usually 10%

He wouldn't do that, he be too afraid the local Trading Standards Department would start chasing him!

(Usual replies patiently awaited)

Oh hello its the f****it again. If you didn't have s*it for brains, you would realise a spreadsheet ( you are clearly too thick o know what that is) gives a much more accurate figure than 'usually 10%'.

What? The ususal reply that you are a sad no life muppet? Did you ever post anything constructive around here?

How is the council house in Caerphilly?

Wanker.

It's nothing to do with Trading Standards - all he's doing is quoting a price for a job. A tradesman can quote exactly what he wants for a requested piece of work. It's entirely up to the customer to accept or not that quote.

For what, exactly?

MBQ

The function needed is ROUNDUP

What he really needs is a spreadsheet where you enter the lengths required for the job and it calculates what you need to buy for the minimum wastage. Somewhat more complex though, especially when there's a big choice of available lenths

AAMOI this one was to calculate the materials & cost for adding ballustrades to decking. Given a certain run of ballustrade its handy to be able to calculate the number of posts & spindles. The handrail is only available in

That's not too difficult - its basically a version of the old coin analysis problem that most payroll packages used to do (i.e. work out the minimum number of each coin denomination required to make up the pay packets).

You could probably get clever and offer options like least waste, lowest cost, fewest cuts etc! ;-)

I do believe that one lacks a little sense of humour - particularly as TMH has oft quoted that he is the bees-knees with his local trading standard officer - ah well! ROTFL

I agree - but surely when quoting or estimating for a job, an amount for wastage must be included in the calculations, especially as it's not always (almost never) possible to do the job with the *exact* amount of wood that is purchased (and that btw, is the material that we are talking about to be a little pedantic in [expectation of the usual replies])

But I suppose when not allowing for waste in a quotation or estimate, one can always charge extra to go and pick up some more material - or god forbid, lose cash on the job by by bearing the lost hours and cost of travelling ones self at the very least!

Oh-hum - it's the TMH protectionist brigade at work again!

Back to the sack cloth and ashes now, good night!

Unbeliever

I and probably you wouldn't think twice about doing that. However, I have a feeling that, if he needs help to find the ROUNDUP function, it could be stretching Dave's knowledge of Excel a bit.

Colin Bignell

Nightjar