How many sleepers

[Shaun]

Hi All

I am trying to work out how many sleepers are required to build several retaining walls of varying shapes and sizes. I have a long holiday period coming up. The trouble is if I order too few sleepers and need to order more the colours won't match as each set of concrete sleeper is coloured slightly differently, and of course I don't want to order too many as cast concrete sleepers are not cheap.

I have several options for a retaining wall each of varying size. I would like to be able to input a few dimensions and have Excel calculate how many sleepers are required.

I just can't seem to work out how to do the maths in Excel. Any ideas? I have attached my progress to date.

Any assistance is greatly appreciated.

Cheers

 

[NARAYANK991]

Hi Shaun ,

Can you confirm the following ?

1. The width of the sleepers is irrelevant. It is only the height and length which matter.

2. What are the variables in this situation ? You have given the different types of sleepers which can be used ; I assume that this will be specified , and the formula does not have to recommend any specific size.

Other than this , I can only assume that the variables are just 2 - the height of the wall , or rather the difference in height between the start and the end , and the length of the wall.

Narayan

 

[Hui]

B14: =ROUNDUP(B8/B10,0)*ROUNDUP(B7/B9,0)-(B6>0)*ROUNDUP(B8/B10,0)*ROUNDUP((B7-B6)/B9,0)/2

What this does is two things
1. Calculate the Rectangular area of the Length and Max Height
2. Calculate the Triangular area of the Length and (Max-Min) Height (This won't be built)
3. Subtract 2 from 1

 

[bobhc]

Hui answer as always is the answer to the problem.

But as I have just had some garden alterations done I thought I would pass on my penny worth's.

Don't do a straight wall as it will look like an empty fish pond and you will begin to regret it.

To move the earth from a land gradient will be more work than you think.
As soon as you remove a spade full of undisturbed earth it will triple in volume, this will increase your cost to.

a)remove the earth via expensive skips and

b) increase your production of bucket loads of sweat.

c) a fridge topped up with beer (not fosters) to replace the lost sweat will incur costs.

d)will take more sleepers to build an ugly wall.


.

 

[David Evans]

Hui answer as always is the answer to the problem.

But as I have just had some garden alterations done I thought I would pass on my penny worth's.

Don't do a straight wall as it will look like an empty fish pond and you will begin to regret it.

To move the earth from a land gradient will be more work than you think.
As soon as you remove a spade full of undisturbed earth it will triple in volume, this will increase your cost to.

a)remove the earth via expensive skips and

b) increase your production of bucket loads of sweat.

c) a fridge topped up with beer (not fosters) to replace the lost sweat will incur costs.

d)will take more sleepers to build an ugly wall.


.

Not only do you get a Chandoo Solution form Hui, Bob provides more insight and dare I say it, with Can Do attitude - what a forum!

 

[bobhc]

@David Evans

with Chan Doo attitude

jeeeeeeze, you are spending to much time with the coffee gods.......


.

 

[bobhc]

Shaun I am sure you will be happy with the help you have received..............but
In your drop down data validation you have merged three cells together.
This is BAD, I think the only reason Microsoft keep the merge in the ribbon is so that those who do not know any different use it and by doing so end up calling a very expensive help line. Merge should never be used.........................

Avoid merging cells at all costs.


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.And there is a better way of doing it.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick, once you emerge a cell.Bad thing


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.

 

[Shaun]

Hi everyone

Thank you for your replies.

Narayan:
You are correct, the width of a sleeper can be ignored for this purpose. Height and length of the sleepers are the only considerations as I am trying to calculate the number of whole sleepers in a given wall. In terms of calculating the number of sleepers, in the example list of sleepers I really on have 4 options:

1. 1750 x 195
2. 1750 x 200
3. 1800 x 200
4. 2000 x 200

Hui:
Thank you for your formula, but I am not sure it is evaluating correctly.

ROUNDUP(B8/B10,0)*ROUNDUP(B7/B9,0) - This part is correctly evaluating to 50 sleepers

(B6>0)*ROUNDUP(B8/B10,0)*ROUNDUP((B7-B6)/B9,0)/2 - This part evaluates to 0 as (B6>0) if B6 = 0, if I change B6 = 1 this part evaluates to 50/2 = 25 Sleepers

50 - 25 = 25 sleepers in the wall, which to me is the mean number of sleepers in the wall, not the whole number of sleepers in the wall, to include those sleepers partially buried.

bobhc:
Unfortunately I have no alternative but to construct straight walls, but to avoid additional bureaucracy, these walls will be tiered and planted out to hopefully avoid what you describe.

This undertaking has many long walls over several gradients. There will be no manual shovelling of soil, an excavator, bobcat and dump truck will be used.

There beer budget was established first and is generous. Happy workers are beer drinking workers (after heavy machinery has been switched off!)

As for the merged cells, it is a terrible habit which I have been trying to kick for some time. I relapse in times of weakness and haste. With strength of the shortcut keys I hope this will be the last.

David:
What a forum indeed! Chandoo.org would have to be one of the best forums I have been a member of.

Cheers

 

[bobhc]

@Shaun

Get the camera out and take one or two before and after snaps, and then nip over to the lounge forum and post so we can do one of two things

a) admire your skills in design, planning, construction, garden design, and take wonderment at the finished wall in all its glory

b) have a bloody good laugh.....