fraction formatting

[Galgenstrick]

This is a slightly weird questions, but I need a decimal converted to a fraction a certain way, similar to the fractional number formatting. I'm guessing this will have to be done in VBA

Here's an example of what I'm looking to do:

I have a cell that is always rounded to the nearest 0.5, I want to convert that to a fraction of either 1/16" if the number is whole, or 1/32" if the number ends in 0.5.

Example:
0.5 converted to 1/32"
1 converted to 1/16"
3 converted to 3/16"
3.5 converted to 7/32"
-5 converted to -5/16"
-5.5 converted to -11/32"
etc...

 

[Norie]

You could do it with a formula.

=IF(INT(A1)=A1,A1&"/16""", A1/0.5&"/32""")

 

[petertenthije]

This formula assumes the lookup value is in A1. The output will be a text value, not a numerical value!

=If(roundup(A1,0)=A1,A1&”/16”,A1*2&”/32”)

 

[shg]

How about just ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]

0.5​

[/td][td="bgcolor:#CCFFCC"]

1/32​

[/td][td]B1: =A1/16[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1​

[/td][td="bgcolor:#CCFFCC"]

1/16​

[/td][td]Format of B1: # ??/??[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

3​

[/td][td="bgcolor:#CCFFCC"]

3/16​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

3.5​

[/td][td="bgcolor:#CCFFCC"]

7/32​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

-5​

[/td][td="bgcolor:#CCFFCC"]

- 5/16​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

-5.5​

[/td][td="bgcolor:#CCFFCC"]

- 11/32​

[/td][td][/td][/tr]
[/table]

 

[Galgenstrick]

You could do it with a formula.

=IF(INT(A1)=A1,A1&"/16""", A1/0.5&"/32""")

This one worked great. Just what I needed Thank you!