Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.
share|improve this question

1 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:

enter image description here

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:

enter image description here

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.