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??

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:

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.

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

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??

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??

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

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??