FormatCells --> Custom --> ??:?? instead of ??/??

[James Perry]

CurrentAssets CurrentLiabs FractionFormat RatioFormat
1100 2400 11/24 11:24
1234 2345 10/19 10:19
Using FormatCells --> Custom --> ??/?? gives the results as shown above in FractionFormat.
Instead of ??/?? i would like to use ??:?? (in the FormatCells --> Custom) but MS gvs out an error msg.
My question is…..
Is there a way of doing Ratios directly thru' FormatCells by incorporating a 'Ratio' option in FormatCells.
Thank u.

 

[Hui]

James

I don't believe you can do what you want

the / is a special format character that tells Excel to work out what fractions the Decimal number can be displayed as

 

[James Perry]

Thank you, Sir.
In which case is there an Excel formula that can do the same ?
Thank you.

 

[Hui]

=SUBSTITUTE(TEXT(A1,"00/00"),"/",":")

 

[NARAYANK991]

Hi Hui ,

Please note that the values 1234 and 2345 will not result in 10:19.

Excel converts the displayed value alone to fractional format ; given the above values , if you select one digit fraction , you will see 1/2 ; selecting 2 digits gives 10/19 ; selecting 3 digits gives 311/591.

Narayan

 

[Hui]

Narayan

1234 and 2345 does result in 10:19

B1: =SUBSTITUTE(TEXT(A1,"00/00"),"/",":") = 10:19
as requested by the op.

But yes, If you use
=SUBSTITUTE(TEXT(A1,"0/0"),"/",":") it will result in 1:2
=SUBSTITUTE(TEXT(A1,"000/000"),"/",":") it will result in 311:591 etc

 

[NARAYANK991]

Hi Hui ,

Sorry , my mistake ; I didn't try out your solution.

Narayan

 

[James Perry]

Thank you, Mr. Hui.
I got the ratios 'directly' by using your formula:
=SUBSTITUTE(TEXT(A6/B6,"00/00"),"/",":")
So, instead of the fraction 11/24 i directly got the desired ratio 11:24.
Much appreciated & thanks again.

 

[Hui]

James,

Please make sure you note my and Narrayn's comments about the effects of rounding
ie: change the formula to:
=SUBSTITUTE(TEXT(A6/B6,"0/0"),"/",":")
or
=SUBSTITUTE(TEXT(A6/B6,"000/000"),"/",":")
etc

and you will get similar but rounded variances of the same fraction