To change the rounding of 0.5 to up not down

**stevewilko** · 01-09-2009, 02:53 PM

Dear All,

I have been trying for ages to get a formula where any number prefixing .5 automatically rounds up and not down as the excel standard does.

EG,

16.4999999 does show with no decimal places as 16.
16.5 does show with no decimal place as 16. But I want it to round up to 17.
and obviously 16.50000001 does indeed round to 17 anyway.

Its purley numbers that have .5 I need to go up and not down.

To those people out there with significantly more brains than me then please help.

Kind regards

Steve Wilko.

**NBVC** · 01-09-2009, 02:55 PM

If I format the cell as Number and 0 decimals, then 16.5 shows up as 17..

**daddylonglegs** · 01-09-2009, 03:00 PM

Hello Steve,

I presume you are using ROUND function.

16.5 exactly will round up but anything below will round down, so 16.49999999 will round down to 16. If you want that number to round up then the question is, how low do you go before you round down?

What happens to 16.4 or 16.3?

Assuming you want to round up anything that would display as 16.5 when formatted to one decimal place, i.e. anything 16.45 or above, you could introduce a small adjustment into the formula, e.g.

=ROUND(A1+0.05,0)

where your number is in A1

**NBVC** · 01-09-2009, 03:03 PM

To those people out there with significantly more brains than me then please help.

\

I guess that's not me today... I misread your question and thought you were trying to format the cell, not create a formula in a separate cell....

**stevewilko** · 01-09-2009, 03:07 PM

Hi Thanks for the replies.

My Excel rounds .5 figures down to the nearest whole number when I set the decimal places to 0.

However thank you Daddylonglegs that formula wroked a treat.

Great stuff

Kid regards

Steve Wilko