Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default how do I format an Excel sheet to create a ratio as in 1:25 ??

when doing calculations I want the answer toi come out as a ratio 1: 25
rather than just 25 - I have found a way of doing that by adding the 1: as
text within inverted commas - but it won't allow a rounding.

Does anyone know another way of doing this pls??
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: how do I format an Excel sheet to create a ratio as in 1:25 ??

Yes, there is another way to format an Excel sheet to display a ratio as 1:25. Here are the steps:
  1. Select the cell where you want to display the ratio.
  2. Right-click on the cell and select "Format Cells" from the drop-down menu.
  3. In the "Format Cells" dialog box, select the "Custom" category.
  4. In the "Type" field, enter the following format code: 0":"00
  5. Click "OK" to apply the format.

Now, when you enter a value in the cell, it will display as a ratio with a colon separator. For example, if you enter 25, it will display as 1:25. If you enter 50, it will display as 2:50.

Note that this format will not allow rounding, as you mentioned. If you need to round the ratio to a specific number of decimal places, you can use a formula to calculate the ratio and then round the result. For example, if you have two values in cells A1 and A2, you can use the following formula to calculate the ratio:

Formula:
=ROUND(A1/A2,2)&":"&ROUND(A2/A1,2
This formula will calculate the ratio of A1 to A2 and display it as a rounded ratio with a colon separator. You can adjust the number of decimal places by changing the second argument of the ROUND function.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default how do I format an Excel sheet to create a ratio as in 1:25 ??

On 30 Aug, 13:08, Fizz Richardson
wrote:
when doing calculations I want the answer toi come out as a ratio 1: 25
rather than just 25 - I have found a way of doing that by adding the 1: as
text within inverted commas - but it won't allow a rounding.

Does anyone know another way of doing this pls??


Hi Fizz,

This isn't the whole answer, but it might help.....

If you type the ratio into the cell as a fraction (eg for 1:25 type it
as 0 1/25) it will appear as a fraction format, 1/25. Similarly your
ratio can be typed into a fraction-formatted cell as a decimal (eg 0.5
will appear a 1/2).

I say it's only half an answer since I can't yet see a way to use the
":" separator instead of the "/" - but I'm sure one of the experts
here might be able to find a way to do that, if it exists.

HTH

Phil

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default how do I format an Excel sheet to create a ratio as in 1:25 ??

If you have a number of 24.8, and format the call as "1:"0 , then it will
display as 1:25.
If you wanted it in a different cell, you can use the formula
="1:"&TEXT(A1,0) to produce a text string.
--
David Biddulph

"Fizz Richardson" wrote in
message ...
when doing calculations I want the answer toi come out as a ratio 1: 25
rather than just 25 - I have found a way of doing that by adding the 1: as
text within inverted commas - but it won't allow a rounding.

Does anyone know another way of doing this pls??



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default how do I format an Excel sheet to create a ratio as in 1:25 ??

Hi David
ths for that - as I said I have found that I can use the inverted commas to
force the colon - but it can't then round the answer so somewhere I was
hoping that there might be another way of getting te same effect.

"David Biddulph" wrote:

If you have a number of 24.8, and format the call as "1:"0 , then it will
display as 1:25.
If you wanted it in a different cell, you can use the formula
="1:"&TEXT(A1,0) to produce a text string.
--
David Biddulph

"Fizz Richardson" wrote in
message ...
when doing calculations I want the answer toi come out as a ratio 1: 25
rather than just 25 - I have found a way of doing that by adding the 1: as
text within inverted commas - but it won't allow a rounding.

Does anyone know another way of doing this pls??






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default how do I format an Excel sheet to create a ratio as in 1:25 ??

ths - you can force the colon to show by making Excel think that it's text
(put inverted comas either side) but this doesn't quite do what I want. But
thnks for the suggestions.

"Phil" wrote:

On 30 Aug, 13:08, Fizz Richardson
wrote:
when doing calculations I want the answer toi come out as a ratio 1: 25
rather than just 25 - I have found a way of doing that by adding the 1: as
text within inverted commas - but it won't allow a rounding.

Does anyone know another way of doing this pls??


Hi Fizz,

This isn't the whole answer, but it might help.....

If you type the ratio into the cell as a fraction (eg for 1:25 type it
as 0 1/25) it will appear as a fraction format, 1/25. Similarly your
ratio can be typed into a fraction-formatted cell as a decimal (eg 0.5
will appear a 1/2).

I say it's only half an answer since I can't yet see a way to use the
":" separator instead of the "/" - but I'm sure one of the experts
here might be able to find a way to do that, if it exists.

HTH

Phil


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default how do I format an Excel sheet to create a ratio as in 1:25 ??

If you want to round the number, rather than merely display it rounded, you
can use the ROUND function and then format as discussed below. Another
option is to use Tools/ Options/ Calculation: Precision as displayed, and
then the rounding in the display will be carried into the stored number.
--
David Biddulph

"Fizz Richardson" wrote in
message ...
Hi David
ths for that - as I said I have found that I can use the inverted commas
to
force the colon - but it can't then round the answer so somewhere I was
hoping that there might be another way of getting te same effect.

"David Biddulph" wrote:

If you have a number of 24.8, and format the call as "1:"0 , then it will
display as 1:25.
If you wanted it in a different cell, you can use the formula
="1:"&TEXT(A1,0) to produce a text string.
--
David Biddulph

"Fizz Richardson" wrote in
message ...
when doing calculations I want the answer toi come out as a ratio 1:
25
rather than just 25 - I have found a way of doing that by adding the 1:
as
text within inverted commas - but it won't allow a rounding.

Does anyone know another way of doing this pls??






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
Ratio - How To Create Your Own Function/Formula Dawg House Inc Excel Worksheet Functions 2 February 14th 07 01:40 AM
Divide x/y should give Ratio x:y,How to express ratio in excel arvind3738 Excel Worksheet Functions 2 July 9th 06 09:40 AM
RATIO number format Jane Excel Worksheet Functions 6 December 6th 05 03:58 PM
How do I calculate and display ratio ( format 3.5:2) BKW Excel Worksheet Functions 4 September 28th 05 07:38 PM
How do I create a ratio function in Excel? Michele Excel Worksheet Functions 2 May 24th 05 05:47 PM


All times are GMT +1. The time now is 10:33 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"