Can I make a cell round the value to the nearest whole number?

**Garren1013** · 12-10-2012, 03:09 PM

I have a cell that is going to be used for the quantity of a certain item. I would like to dummy proof this sheet as much as possible so that if someone puts in 21.5, that it will do something like change the value to either 21 or 22, since there can't really be a .5 of this item.

**JSallen** · 12-10-2012, 03:32 PM

For rounding, just select Number for cell format and set it to 0 decimel places.

Please Login or Register  to view this content.

will give you integers as well.

As to your other question, just use General as the cell format (assuming you don't have leading/trailing zeros that you want to keep sometimes).

**Garren1013** · 12-10-2012, 03:40 PM

The only problem with setting the cell format decimal is that it doesn't actually change the value. It may show as 22, but the actual value is still 21.5. I need it to round the value, not just the visual number.

**ChemistB** · 12-10-2012, 04:08 PM

If you set the format to General, it will show the entire number as you enter it, i.e. 1.2345 or 7.65

As you pointed out, if you format the cell to specific decimal places, Excel still holds (and uses for calculations) the entire number.
You can set your spreadsheet to "Format as displayed" but then you permanently lose those extra digits.

Your best bet might be entering them in 1 column (with general format) and then using the ROUND function as mentioned by JSallen in another column to round it to the proper number of decimal places.

**Garren1013** · 12-10-2012, 04:32 PM

I'm sorry for any confusion I may have made asking 2 entirely different questions in the same thread. They weren't meant for the same cell. Just ignore the everything except for the first paragraph where I want to round the value to the nearest whole number. I will try using a general cell format and see if I can use his =ROUND(#,0) suggestion.