Strange behavior with rounding

[Venomous Duck]

I have a situation where I am converting decimal feet back to separate feet, inch, and fraction numerators. I have a couple of instances were using either ROUNDDOWN or TRUNC is giving me a result of 1 less than what it should be. At first I thought I was missing something with the rounding function default behavior, but then I tried TRUNC and i get the same thing.

Basically I took the resulting 12.41666667 of decimal feet and preformed a round down to drop the decimal places to get 12. I then isolated the inches by subtracting the cell with 12 from the cell with 12.4166667 and multiplied it by 12 to get an even 5 in decimal inch, but when preform a ROUNDDOWN(S7,0) i end up with a result of 4. TRUNCE does the same thing. It also happens with 18.333333 decimal feet but works as I intend/need it to with 24.25 decimal feet.

So what is happening is with the 12.41666667 example I am getting a final result of 12-4-16 (that is 16 6ths of an inch) instead of 12-5-0. The 24.25 example is correctly giving me 24-3-0.

I am sure I can come up with some crazy work around, but i would rather figure out why it is doing that in the first place.

In the file attached I have the bad ones highlighted in red and the good one highlighted in green.

[JohnTopley]

Use ROUND not ROUNDOWN

in S7, inches are 4.99999999 so ROUNDOWN(S7,0) will result in 4

[TMS]

If you increase the decimal places, you will discover that there is a small difference versus the displayed values.

5.499999999999990 1/2
11.250000000000000 1/4
4.999999999999970 1
10.750000000000000 3/4
4.499999999999960 1/2
10.250000000000000 1/4
3.999999999999990 1
9.750000000000000 3/4
3.500000000000010 1/2
9.250000000000030 1/4
3.000000000000040 0
8.750000000000060 3/4

Hence, if you round down or truncate the number, and subtract that from the number, you are left with a very large fraction. See the examples above.

[Venomous Duck]

ROUND wont work as it gives me undesired results elsewhere, like taking 8.75 and turning it into 9 inches and -4 (/16ths).

Weird that when I checked the calculations with my calculator I get 5.000004 but when I expand the decimal places in Excel I see where you are saying it is 4.99999999.

Is there a way to make excel TRUNC the decimal places from what is shown instead of actually calculated? I feel like by default TRUNC should drop what is seeing, not what is "behind the scenes"

[TMS]

Maybe just use a formula:

Please Login or Register  to view this content.

[TMS]

Or, you could just format column S as Fraction. That gives the "look" you want but retains the numeric value (in inches and fractional inches).

5 1/2
11 1/4
5
10 3/4
4 1/2
10 1/4
4
9 3/4
3 1/2
9 1/4
3
8 3/4

[Rick Rothstein]

Here is another formula that will work...

Please Login or Register  to view this content.

[TMS]

Or with the quote marks - Custom Format:

# #/#\" 

5 1/2"
11 1/4"
5"
10 3/4"
4 1/2"
10 1/4"
4"
9 3/4"
3 1/2"
9 1/4"
3"
8 3/4"

[TMS]

Updated sample file

[Venomous Duck]

Thanks a lot TMS. My problem with what you have in Rick's solution is it looks to be rounding up anything with a decimal value higher than .5 thus making 9-3/4" 10. I assume it will also do it to 9/16 and up.

I wont have time to play around with until it later but was thinking, if I simply change S to fractional format will it treat the .4999999 as 5 when I ROUNDDOWN or TRUNC row S in row M? I should then still be able to subtract M from S and multiply by 16 for row O, thus eliminating the problem of 16 instead of 0 also. S can be in any format that works, but final result in row O must be in the numerator of the non-simplified fraction to the 16th.

Or can I specify to round up if the decimal is over .9375?

I also am not following the logic path of either formula to understand what is happening, but I can dig into that later.

[TMS]

I just put Rick's solution in because it was there. Nothing to do with me and I'm not sure it does what you want.

Formatting does nothing at all to the underlying value. So, if you use TRUNC, INT, or ROUNDDOWN, you will always get 4 out of 4.9999999

[Venomous Duck]

Thanks for the help guys. I figured out a work around, I think. I just moved the formula from M to W and O to X. Then in M used the formula =IF(X3>15,W3+1,W3) and in O used the formula =IF(X3>15,X3*0,X3) and copied them down. So far seems to work as expected. I will play with input numbers to see if I get anything erratic out of it, but I think that should work.