Dividing to find an integer

[ddocg]

Hi,

Is there a formula that would return an integer from a division calculation?

For example:

A1 will contain a varying non integer

In A2 I would like to show the integer from dividing the value in A1 however, this division is limited by a set number.

If A1 = 2.962 this number should then be divided by, at most, 0.220 this would return 13.463 which is no use, I need it to return a whole number so, the formula would need to iterate down from 0.220 until it returns an integer in this case 14 for 0.211 or ideally return 16 for 0.185

I don't know if what I'm asking is even possible in excel, hopefully it is, I appreciate it may need more cells than A1 and A2 which fine.

Appreciate your advice

 

[Eric W]

2.962 divided by .211 is 14.03791. 2.962 divided by .185 is 16.01081. Neither one is an integer. Are you looking for some specific tolerance? Say, the decimal must be less than .02? Or do you want to try all values from .220 to .001 decreasing by .001 at a time, and return the value that results in the smallest decimal?

 

[Joe4]

Why is 16 ideal over 14?

You said that the divisor cannot be more than 0.220, but that leaves a lot of options open.
So, we need to know what is considered to be "ideal", what we are striving for.

 

[ddocg]

Apologies Eric, I should have been more specific, I also note I have changed the order of the calculation below, again, I should have been more specific.

The value in A1 is a distance in metres, in this case 2 metres 962 millimetres, working to 3 decimal places, if I divide 2.962 by 14 I get 0.212 - not 0.211 as I incorrectly stated above - which is great because it is less than 0.220

Dividing by 13 it would be 0.228 which is no use as it is greater than 0.220

I am trying to work out how many treads are required in a flight of stairs, the 2.962 is the distance from finished floor to finished floor and each tread height has to be <= to 220mm - it may well be that I would want 16 treads which would be more comfortable at 0.185

I'm now thinking a list with a Vlookup might be a better way as it would allow selection of the preferred rise.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Delta H​

[/td][td="bgcolor:#F3F3F3"]

Steps​

[/td][td="bgcolor:#F3F3F3"]

Rise/Step​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

2.962​

[/td][td="bgcolor:#CCFFCC"]

14​

[/td][td="bgcolor:#CCFFFF"]

0.212​

[/td][td][/td][td="bgcolor:#CCFFCC"]B2: =CEILING(A2 / 0.22, 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

2.745​

[/td][td="bgcolor:#CCFFCC"]

13​

[/td][td="bgcolor:#CCFFFF"]

0.211​

[/td][td][/td][td="bgcolor:#CCFFFF"]C2: =A2/B2[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2.781​

[/td][td="bgcolor:#CCFFCC"]

13​

[/td][td="bgcolor:#CCFFFF"]

0.214​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

3.335​

[/td][td="bgcolor:#CCFFCC"]

16​

[/td][td="bgcolor:#CCFFFF"]

0.208​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

2.846​

[/td][td="bgcolor:#CCFFCC"]

13​

[/td][td="bgcolor:#CCFFFF"]

0.219​

[/td][td][/td][td][/td][/tr]
[/table]

Note that there is actually one less, because the last step is the the finished floor.

 

[Eric W]

It seems that for stairs, you would also have a minimum rise. A rise of .002 would give you an exact integer, but you'd end up with 1481 stairs! So you'd only need to divide 2.962 from the minimum height to the maximum height to get a range of acceptable stair counts. But it also seems you need to worry about the width of the stair. For a given width of the entire stairwell, the more stairs you have, the narrower each stair would be. I'm not a builder, so I really can't say. It might be best to figure out the numbers once, then put the results in a VLOOKUP table. I've also seen cases where the bottom or top stair is slightly shorter than the rest. I'm sure there are rules governing that, but I don't know them.

 

[ddocg]

It seems that for stairs, you would also have a minimum rise. A rise of .002 would give you an exact integer, but you'd end up with 1481 stairs! So you'd only need to divide 2.962 from the minimum height to the maximum height to get a range of acceptable stair counts. But it also seems you need to worry about the width of the stair. For a given width of the entire stairwell, the more stairs you have, the narrower each stair would be. I'm not a builder, so I really can't say. It might be best to figure out the numbers once, then put the results in a VLOOKUP table. I've also seen cases where the bottom or top stair is slightly shorter than the rest. I'm sure there are rules governing that, but I don't know them.

Thank you Eric,

I have it working now using Vlookup - the width is not an issue as the calculator is only for a single flight, so far! though, I think, when you reference width you actually mean the length of the stair which here is referenced as the total going, my calculator does take this into account as it has to satisfy a maximum angle of 42 degrees

There are regulations that govern a stair construction and your example of a stair that has a smaller step at either the top or bottom is likely a result of incorrect dimensions being conveyed at the time of order hence, the stair was adapted to fit........ happens a lot!

 

[ddocg]

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

Delta H​

[/TD]
[TD="bgcolor: #F3F3F3"]

Steps​

[/TD]
[TD="bgcolor: #F3F3F3"]

Rise/Step​

[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

2.962​

[/TD]
[TD="bgcolor: #CCFFCC"]

14​

[/TD]
[TD="bgcolor: #CCFFFF"]

0.212​

[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFCC"]B2: =CEILING(A2 / 0.22, 1)[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

2.745​

[/TD]
[TD="bgcolor: #CCFFCC"]

13​

[/TD]
[TD="bgcolor: #CCFFFF"]

0.211​

[/TD]
[TD][/TD]
[TD="bgcolor: #CCFFFF"]C2: =A2/B2[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

2.781​

[/TD]
[TD="bgcolor: #CCFFCC"]

13​

[/TD]
[TD="bgcolor: #CCFFFF"]

0.214​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD]

3.335​

[/TD]
[TD="bgcolor: #CCFFCC"]

16​

[/TD]
[TD="bgcolor: #CCFFFF"]

0.208​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

6​

[/TD]
[TD]

2.846​

[/TD]
[TD="bgcolor: #CCFFCC"]

13​

[/TD]
[TD="bgcolor: #CCFFFF"]

0.219​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Note that there is actually one less, because the last step is the the finished floor.

Thank you SHG,

I'll look into the Ceiling command and see if I can find a use for it, I do appreciate you taking the time to reply, very helpful.

 

[Giordano Bruno]

Hi ddocg,

Why is it so complicated? Surely all you need do is decide your minimum riser height, say 170mm and divide the floor height difference by that and round the number down. That will give you the number of risers which will be the same whether you have a landing or not. If you need the number of treads, it's number of risers minus number of landings - 1 If you want a count of treads, perhaps to count the number of nosing tiles or something it's equal to the number of risers. There is never a need to have each riser as a millimetre integer.