OT; Excel help please

[The Medway Handyman]

I'm trying to knock up a spreadsheet to calculate how many standard lengths of timber I need for a certain run.

For example; if I want to run 15.0 metres of timber, and its in 2.4m lengths I can enter 15 in cell A1, 2.4 in cell B1 and the formula =A1/B1 in cell C1 I get the answer 6.25.

However I would need 7 lengths to complete the job.

I can format cell C1 to 'show' 7, but anything multiplied by C1 is still multiplied by 6.25 & not 7.

So, if the timber was £3 per 2.4m piece '=C1 x 3' would give the answer £18.75, but 7 lengths would cost me £21.

What function can I use to sort this? In very basic terms please :-)

[Luke]

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

Luke

[Peter Andrews]

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

[The Medway Handyman]

Thanks very much chaps - sorted!

[John Rumm]

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.

[Bob Eager]

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

[John Rumm]

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

[Frank Erskine]

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

:-)

[The Medway Handyman]

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.

[Unbeliever]

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)

[The Medway Handyman]

Oh hello its the f****it again. If you didn't have shit 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.

[Frank Erskine]

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.

[Man at B&Q]

For what, exactly?

MBQ

[Invisible Man]

The function needed is ROUNDUP

[stuart noble]

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

[The Medway Handyman]

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

2.4m lengths at £14 each. A rule of thumb guess could cost you £50 ish on some jobs.

[John Rumm]

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! ;-)

[Unbeliever]

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

[Nightjar]

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

[The Medway Handyman]

Nightjar