Round to nearest half cent then display the half cent as fraction

[wilkisa]

I've been struggling with this one and I don't think it should be so difficult. My user is rather old school and wants to show his stock prices round to the nearest half cent and then display that half as a fraction. Example: 10.165 should be displayed as 10.16 1/2 or 4.555 should be displayed as 4.55 1/2.

I've tried the DOLLARFR function but it wants to display the entire decimal as a fraction. I tried custom formatting the cells as using various combinations of # ?/? trying to get it to display only the half cent in fractional format but haven't been successful. I then hit on the following formula:

=IF(RIGHT(B18,1)="5",SUBSTITUTE(B18,RIGHT(B18,1)," 1/2"),B18)

and it works great...as long as there is only a single 5 in the value to convert. The value above, 4.555, displays as 4. 1/2 1/2 1/2. So, I clearly need help with this. Does someone have a good solution?

Thanks,
Shirlene

 

[mole999]

if you format to one decimal place, and use round, use point5 to make sure you limit to only the fraction

 

[wilkisa]

Thanks, Mole, for responding but I don't think your suggestion will get what we need. His price quotes are rounded to 3 decimals places and that what he wants it to be; 1 decimal place is not adequate and that's my dilemma. With the 3 decimal places, he wants to see the first 2 numbers as decimal and the 3rd number as a fraction of the nearest half cent. So, 4.555 should become 4.55 1/2.

 

[SpillerBD]

deleted

 

[SpillerBD]

=text(trunc(mround(b18,0.005),2),"#.00") & " " &text(mod(mround(b18,0.005)*100,1),"#/2")

 

[SpillerBD]

I've refind to

=TEXT(TRUNC(MROUND(B28,0.005),2),"#.00") & " " & IF(MOD(MROUND(B28,0.005)*100,1)=0,"",TEXT(MOD(MROUND(B28,0.005)*100,1),"#/2"))

Which is meant to display the "1/2" when valid. But testing shows that some of that funky math error can yield a "0/2".

 

[wilkisa]

Hello, SpillerBD. Thank you so much for this formula as it works perfectly! I really appreciate it.

Shirlene