Round worksheet cells to nearest 1/4?

[Bruce Kieffer]

I asked something similar to this about 6 months ago. @Pat Stanford explained to me that the thicknesses in my worksheet that I think to be 3/4" are actually slightly different than 3/4", and that is the case here. Instead of being exactly 3/4" some are .750000000000001, so when I summarize a column in my worksheet, those not exact and those that are exact appear as ---. How do I round them so they all appear as 3/4" in the worksheet? Without any luck, I tried all the worksheet cell number formatting options, and I tried changing the Units settings and using Dimension as the numbering format for those cells.

 

Column E is the thickness. When column A is not summarized, then they all appear as 3/4".

 

When column A (or column G) are summarized, then the thicknesses (column E) are all ---.

[Pat Stanford]

You can't do it with display formatting. You actually need to round or truncate the values to make them the same.

 

Try

 

=Truncate(Min(Height, Length, Width), 2)

 

The last digit in the formula is the number of decimal places.  2 is fine for 1/2 and 1/4 increments.  You will need to go to 3 for 1/8 and 4 for 1/16.

 

[Bruce Kieffer]

@Pat Stanford It roks on two of the three cells:

 

[Pat Stanford]

Change the display on Column E from fractional to decimal with at least 4 decimal places and see what the real data is.

 

[Bruce Kieffer]

Ah, it looks like line 13.13 has a problem that I need to go to the object and fix. I hope that is the problem.

 

[Pat Stanford]

@Bruce Kieffer  For anyone but you I would recommend just faking it. 😉

 

=Truncate(Min(Height+.005, Length+.005, Width+.005), 2)

 

Adding the extra 5 thousandths of an inch before truncating will make the Truncate act more like a Round.  Anything with a digit in the thousandths place of 0, 1, 2, 3, 4 will end up with a value of 9 or less and will be ignored during the truncation. A digit tin the thousandths place of 5, 6, 7, 8, 9 will exceed 10 and will carry into the hundredths place and will truncate to the higher value.

 

It would be interesting to see what all those values are without the Truncate and see just how far off they are.

 

[Bruce Kieffer]

3 hours ago, Pat Stanford said:

@Bruce Kieffer  For anyone but you I would recommend just faking it. 😉

 

=Truncate(Min(Height+.005, Length+.005, Width+.005), 2)

 

Adding the extra 5 thousandths of an inch before truncating will make the Truncate act more like a Round.  Anything with a digit in the thousandths place of 0, 1, 2, 3, 4 will end up with a value of 9 or less and will be ignored during the truncation. A digit tin the thousandths place of 5, 6, 7, 8, 9 will exceed 10 and will carry into the hundredths place and will truncate to the higher value.

 

It would be interesting to see what all those values are without the Truncate and see just how far off they are.

 

LOL! @Pat Stanford you know me too well. Sure I know they are 3/4" thick, but I want to understand what Vectorworks is doing, and you know that too!. I fixed all the pieces so there's no need for modification to the formula anymore. I do appreciate learning about the truncate formula, which I may use if the future.

Edited January 8 by Bruce Kieffer