RATIO number format

[Guest]

I want my result to be expressed as a ratio:

4:1

thank you, jane

 

[Niek Otten]

Hi Jane,

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

 

[Guest]

Thank you Niek!

now how would I fit that in if in my cell I have a formula? in cell K6, I
have a simple formula ... =M9/M8. it's the result that I need as stated
below.

I do appreciate your help!

 

[Guest]

I got it myself!
THANK YOU AGAIN!

Hi Jane,

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

 

[Guest]

Hi Neik,
another question if I may as I have not had to work with ratios in this
manner and do not know the standard in which they are expressed------

here is my actual formula and results:
cell K4 contains "# of Tops"
cell k5 contains "# of Pants"
=SUBSTITUTE(TEXT(K4/K5,"?/?"),"/",":") results = 15:7
=SUBSTITUTE(TEXT(K5/K4,"?/?"),"/",":") results = 1:2

Since I want to find the ratio of Tops to Bottoms, I expected to see 2:1,
not 15/7. The people that will be using this spreadsheet will also expect
2:1 and will not be happy with 1:2.
Suggestion?

 

[Roger Govier]

Hi Jane

If the value is just for display, and is not to be used for further
calculation then you could use
=ROUND(K4/K5,0)&":1"

If you want to use the values in further calculation, then just use =K4/K5
but format the cell
Format>Cells>Number>Custom>General":1"
which would result in 2.1429:1

or format as #":1" which would result in 2:1

Regards

Roger Govier

 

[Guest]

this also worked very well Roger!
thank you!

Hi Jane

If the value is just for display, and is not to be used for further
calculation then you could use
=ROUND(K4/K5,0)&":1"

If you want to use the values in further calculation, then just use =K4/K5
but format the cell
Format>Cells>Number>Custom>General":1"
which would result in 2.1429:1

or format as #":1" which would result in 2:1

Regards

Roger Govier