Custom Formatting in Excel

[marapnyy]

Hi!

I am looking to format a range of numbers to only show a decimal when the decimal is .5. Ex- .5, 1.5, 2.5...etc. So if the number is 2.6, I'd like it to just round to 3 or if the number is 1.2, I'd like it to round down to 1, no decimal. I've never done custom formatting, so I am trying to figure out a way to do this.

Any help would be greatly appreciated!

Thanks!!!

 

[oldbrewer]

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]2.7[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]3.8[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9.1[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8.5[/TD]
[TD="align: right"]8.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula in B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]=IF(A1-INT(A1)=0.5,A1,INT(A1+0.5))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mrmmickle1]

Not sure if you can do that using custom formatting here is a workaround:
Tools>Add-ins and check Analysis Toolpak.
Then use MROUND() Function
Example:

=MROUND(YOUR CELL OR NUMBER HERE,0.5)