Ration

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Can excel support ratio's. ie: 20/1 but I want to show
it as 20:1

where

one cell has 20 and another has the 1. when divided I get
5% but I want 20:1

Any Ideas??

Aaron
 
Hi Aaron,

=SUBSTITUTE(TEXT(A1,"?/1"),"/",":")

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


But you can't calculate with this anymore. If you need to calculate use the
original cell.
 
I should have been more clear. The ratio will not always
be my example nor will it end with :1. There is another
ratio with comes to 22% however I want to show 9:2.
 
I should have been more clear. The ratio will not always
be my example nor will it end with :1. There is another
ratio with comes to 22% however I want to show 9:2.
Click to expand...
...

If you have values like 5% or 22(.222...??)% that you want to format as ratios
like 20:1 and 9:2, then you need to specify what you want more precisely. 22%
as-is would need to be inverted and the result formatted as ?/?, then the /
changed to : as a text operation. Meaning you need to restrict your fractions to
one decimal place in the denominator.

For example, TEXT(1/22%,"?/????") gives 50/11 rather than 9/2.

If you always want one decimal place in the right hand side of ':', try

=RIGHT(TEXT(x,"?/?"),1)&":"&LEFT(TEXT(x,"?/?"),LEN(TEXT(x,"?/?"))-2)

The reason you can't just use =SUBSTITUTE(TEXT(1/x,"?/?"),"/",":") is that
you're subject to floating point rounding error. If you have 22% exactly, or
0.22, entered in a cell named x, then TEXT(1/x,"?/?") gives 41/9 even though
TEXT(x,"?/?") gives 2/9.
 
This should give you your "9:2" display.

=SUBSTITUTE(TEXT(1/A1,"?/2"),"/",":")


However, you are forcing one level of rounding in one example, and no
rounding in another example (ie 20 for 20:1). You may want to use an "IF"
statement and specify your two rules. I.e. IF(A1>=1,A1,1/A1)...etc with
your two different display rules.

HTH
Dana DeLouis
 
Back
Top