A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

how do I format an Excel sheet to create a ratio as in 1:25 ??



 
 
Thread Tools Display Modes
  #1  
Old August 30th 07, 01:08 PM posted to microsoft.public.excel.misc
Fizz Richardson
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??
Ads
  #2  
Old August 30th 07, 01:46 PM posted to microsoft.public.excel.misc
Phil
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

  #3  
Old August 30th 07, 03:04 PM posted to microsoft.public.excel.misc
David Biddulph[_2_]
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??



  #4  
Old August 31st 07, 12:26 AM posted to microsoft.public.excel.misc
Fizz Richardson
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??

>
>
>

  #5  
Old August 31st 07, 12:28 AM posted to microsoft.public.excel.misc
Fizz Richardson
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
>
>

  #6  
Old August 31st 07, 01:42 AM posted to microsoft.public.excel.misc
David Biddulph[_2_]
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??

>>
>>
>>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 12: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 02: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® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.