Round off calculated values to upper limit or lower limit in Excel

Question

I have some columns in Excel. The result will be based on calculations.

What I want is, if the result after calculation is >=.50 then the value should be rounded off to the upper limit that is ceiling() and if the value is <.50 then it should be rounded down to the lower limit...

e.g.

• If the calculated value is 11.59, the result should be 12.
• If the calculated value is 11.49, the result should be 11.

Answer 1

For the cells containing results that you want rounded, simply configure the cell format by right clicking the cell, choosing Format Cells, selecting the Number category and setting the Decimal places to 0:

This will round up anything >= .50 and round down anything < .50.

In the screenshot below column A doesn't have the formatting applied and column B does:

If you don't want to use formatting, you can also use the ROUND() function. Assuming for example you wanted to round whichever value was in cell E4, you could use:

=ROUND(E4, 0)

This will round up or down the number the same way as described above.

Just to clarify there is a major difference between using these two methods:

• The first method only changes the formatting of what is displayed in the cell (meaning the value in the cell will remain for example 11.59 but you will see 12. However if you perform any further calculations with this cell the calculation will use the 11.59 value)

• The latter method permanently changes the value from say 11.59 to 12 or from 11.49 to 11, and would be used in any further calculations.