Solved: Rounding in units of 0.5

[Gusty]

Hi

Im having trouble rounding up/down in units of 0.5

The personnel manager would like the results of the rounding calculation to be those in column F of the attached spreadsheet. Cell e6 is problematical as it is neither one way or the other, Ive tried ceiling etc but cant get it to work as he would like. Any help would be greatly appreciated

[Kenneth Hobs]

Maybe you should show what he expected.

If the rule is to upper 0.5 then numbers like 16.5 and 17.0 are not changed.

I would have used MRound() to get the nearest 0.5 but you may not want a round down.

[shrivallabha]

You can use if case as below:

=IF(RIGHT(E3,1)="5",CEILING(E3,1),CEILING(E3,0.5))

[macropod]

Another approach would be:
=ROUND(E2*2,0)/2

[shrivallabha]

Hello Paul,
The OP is specifically looking for a case where *.5 should be converted to next round up value. Please see his last cell:
16.5 should be (probably) 17.0.

[Bob Phillips]

According to his request he said up and down, and his example left 16.5 at 16.5

[macropod]

Hello Paul,
The OP is specifically looking for a case where *.5 should be converted to next round up value. Please see his last cell:
16.5 should be (probably) 17.0.

There's nothing I've seen in either the OP's post or workbook to indicate that. The formula I posted correctly renders to specified rounding - both up and down - to the nearest 0.5.

[Gusty]

Thanks guys, Shrivallabhas solution was the one I was looking for, he wanted to round up to the nearest 0.5 if the value was less than .49 but also to round up to the nearest 0.5 if it was .5 or above.
Many many thaanks to you all!