PDA

View Full Version : Solved: Rounding in units of 0.5



Gusty
01-28-2011, 06:03 AM
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:doh:

Kenneth Hobs
01-28-2011, 06:48 AM
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
01-28-2011, 08:45 AM
You can use if case as below:

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

macropod
01-29-2011, 03:13 PM
Another approach would be:
=ROUND(E2*2,0)/2

shrivallabha
01-29-2011, 09:22 PM
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
01-30-2011, 12:48 AM
According to his request he said up and down, and his example left 16.5 at 16.5

macropod
01-30-2011, 02:25 AM
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
01-31-2011, 02:52 AM
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!