SOLVED

Change how Excel Rounds Displayed Values

Copper Contributor

From all of my searching, it appears that I want the opposite of most of the questions out there. I have a spreadsheet dealing with hazardous materials. On it, there are lists of weights, and dimensions.

 

Normal functionality is when a cell is too small to show the full value, Excel displays a rounded value. This is just a displayed value, which does not affect the actual value. This fine with me; except that I need to change how that rounding is done. I need the displayed rounded value to always round up (safety concerns).

* Say I have 1.2 lbs of a hazardous material, but the cell is only big enough to show one character. Normally Excel rounds the displayed down to 1, while not affecting the actual value of 1.2.

* I need this to round up to 2; but again, only the display and not the actual number.\

 

I've chosen small numbers to make the example easy, while in reality the numbers are longer ~10 digits or so. Changing the precision doesn't work, because I don't have infinite width for the cells. At some point, the cell is going to be skinnier than the value in the cell, and Excel will round the display.

6 Replies

@brakthol 

 

Seems like you could take advantage of the several different functions Excel offers vis-a-vis rounding. Here's a link to a reference on ROUNDUP, but it also includes references to some others, as well as discussion of when and how one might use them.

mathetes_0-1692900698599.png

 

I am familiar with those functions. The issue is that they are changing the actual value of the cell. I need the actual value of the cell to stay untouched.
I'm only looking to change the rounding of the Displayed number.
best response confirmed by brakthol (Copper Contributor)
Solution

@brakthol 

I don't think that is possible in the current Excel versions.

But to be sure I asked my secret contact at Microsoft:

 

I understand your requirement. You want Excel to display the rounded-up value in the cell, but you don’t want the actual value stored in the cell to change. Unfortunately, Excel’s cell formatting doesn’t provide an option to round up for display purposes only.

However, there’s a workaround you can use by creating an additional column to display the rounded-up values. Here’s how you can do it:

  1. Suppose your weights are in column A. In column B (or any other column), use the ROUNDUP function to round up the weights. The formula would look like this: =ROUNDUP(A1, 0). This will round up the value in cell A1 to the nearest whole number.

  2. Drag this formula down to apply it to all cells in column B.

  3. Now, you can adjust the width of column A such that only the rounded-up values in column B are visible.

Remember, this is just a workaround and might not be ideal for all situations. It’s always recommended to keep the original data visible and clear for accuracy and reference purpo...1234. If you need more help with Excel functions, there are many tutorials available online567

 

@Detlef_Lewin 

 

A new use (or variation) of "helper columns": not to do calculations, per se, but to handle the display. I like it.

@mathetes 

I will tell my secret contact. :lol:

I have spent many hours trolling through search results, and digging into the Options available in Excel, all to no avail, so I was pretty certain that this wouldn't be possible. But I was hoping :)

Unfortunately, due to the specialized circumstances that I'm in, a second set of helper columns doesn't suit my needs, but I can see how they would work, and I agree that in general, that's a valid solution.

My problem is that I'm using a Gov Mandated form, and the cells are set to exact standards that I can't change. So when I output the data to the form, I am forced into rounded Display values. I was really hoping to be able to change how those Display values rounded, for safety considerations.

I appreciate all of your help with this,
Thank you,
Aaron
1 best response

Accepted Solutions
best response confirmed by brakthol (Copper Contributor)
Solution

@brakthol 

I don't think that is possible in the current Excel versions.

But to be sure I asked my secret contact at Microsoft:

 

I understand your requirement. You want Excel to display the rounded-up value in the cell, but you don’t want the actual value stored in the cell to change. Unfortunately, Excel’s cell formatting doesn’t provide an option to round up for display purposes only.

However, there’s a workaround you can use by creating an additional column to display the rounded-up values. Here’s how you can do it:

  1. Suppose your weights are in column A. In column B (or any other column), use the ROUNDUP function to round up the weights. The formula would look like this: =ROUNDUP(A1, 0). This will round up the value in cell A1 to the nearest whole number.

  2. Drag this formula down to apply it to all cells in column B.

  3. Now, you can adjust the width of column A such that only the rounded-up values in column B are visible.

Remember, this is just a workaround and might not be ideal for all situations. It’s always recommended to keep the original data visible and clear for accuracy and reference purpo...1234. If you need more help with Excel functions, there are many tutorials available online567

 

View solution in original post