Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jane
 
Posts: n/a
Default RATIO number format

I want my result to be expressed as a ratio:

4:1

thank you, jane
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default RATIO number format

Hi Jane,

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

--
Kind regards,

Niek Otten

"Jane" wrote in message
...
I want my result to be expressed as a ratio:

4:1

thank you, jane



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jane
 
Posts: n/a
Default RATIO number format

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!

"Niek Otten" wrote:

Hi Jane,

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

--
Kind regards,

Niek Otten

"Jane" wrote in message
...
I want my result to be expressed as a ratio:

4:1

thank you, jane




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jane
 
Posts: n/a
Default RATIO number format

I got it myself!
THANK YOU AGAIN!

"Niek Otten" wrote:

Hi Jane,

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

--
Kind regards,

Niek Otten

"Jane" wrote in message
...
I want my result to be expressed as a ratio:

4:1

thank you, jane




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jane
 
Posts: n/a
Default RATIO number format

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?
"Niek Otten" wrote:

Hi Jane,

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

--
Kind regards,

Niek Otten

"Jane" wrote in message
...
I want my result to be expressed as a ratio:

4:1

thank you, jane






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default RATIO number format

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
FormatCellsNumberCustomGeneral":1"
which would result in 2.1429:1

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

Regards

Roger Govier


Jane wrote:
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?
"Niek Otten" wrote:


Hi Jane,

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

--
Kind regards,

Niek Otten

"Jane" wrote in message
...

I want my result to be expressed as a ratio:

4:1

thank you, jane




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jane
 
Posts: n/a
Default RATIO number format

this also worked very well Roger!
thank you!

"Roger Govier" wrote:

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
FormatCellsNumberCustomGeneral":1"
which would result in 2.1429:1

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

Regards

Roger Govier


Jane wrote:
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?
"Niek Otten" wrote:


Hi Jane,

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

--
Kind regards,

Niek Otten

"Jane" wrote in message
...

I want my result to be expressed as a ratio:

4:1

thank you, jane




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
number format Ivan Excel Discussion (Misc queries) 2 October 14th 05 02:08 PM
Number format exactly the same, displays differently in some cells eider Excel Discussion (Misc queries) 1 July 29th 05 12:26 AM
Number format for Combobox MBlake Excel Discussion (Misc queries) 1 June 29th 05 03:25 AM
16 digit number wont keep alteration unless format cell to text Croc001 Excel Discussion (Misc queries) 3 March 30th 05 09:12 AM
How do i change numbers in text format to number format? Greg New Users to Excel 1 December 14th 04 06:22 PM


All times are GMT +1. The time now is 03:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"