Conversion Ratio

[cyberlink.natalie]

I used the ffollowing to get the conversion ratio format I wanted
CONCATENATE(1,":",A1/B1)

Now I am trying to figure out if there is a way to show the results in
a whole number ie 1:32 instead of 1:32.6666666.

Is there a way to do that?

 

[Pete_UK]

Try this:

=CONCATENATE(1,":",ROUND(A1/B1,0))

or slightly shorter:

="1:"&ROUND(A1/B1,0)

Hope this helps.

Pete

 

[Guest]

=CONCATENATE(1,":",ROUNDDOWN(A1/B1,0))

 

[James Silverton]

Hello, Pete_UK!
You wrote on 27 Sep 2006 16:42:59 -0700:

PU> =CONCATENATE(1,":",ROUND(A1/B1,0))

PU> or slightly shorter:

PU> ="1:"&ROUND(A1/B1,0)

PU> Hope this helps.

PU> Pete

PU> (e-mail address removed) wrote:
??>> I used the ffollowing to get the conversion ratio format I
??>> wanted CONCATENATE(1,":",A1/B1)
??>>
??>> Now I am trying to figure out if there is a way to show
??>> the results in a whole number ie 1:32 instead of
??>> 1:32.6666666.
??>>
??>> Is there a way to do that?

To tell the truth, I'm not quite sure what you want but if you
have a decimal number, you could investigate formatting to a
fraction. Format cells > Fraction

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

 

[Pete_UK]

My reply hasn't appeared yet (10 mins ago), but here's a shorter
alternative:

="1:"&INT(A1/B1)

You could use ROUNDDOWN instead of INT and instead of ROUND suggested
in my earlier reply.

Hope this helps.

Pete

 

[James Silverton]

Hello, James!
You wrote to Pete_UK on Wed, 27 Sep 2006 19:55:56 -0400:

PU>> =CONCATENATE(1,":",ROUND(A1/B1,0))

PU>> or slightly shorter:

PU>> ="1:"&ROUND(A1/B1,0)

PU>> Hope this helps.

PU>> Pete

PU>> (e-mail address removed) wrote:
??>>> I used the ffollowing to get the conversion ratio format
??>>> I wanted CONCATENATE(1,":",A1/B1)
??>>>
??>>> Now I am trying to figure out if there is a way to show
??>>> the results in a whole number ie 1:32 instead of
??>>> 1:32.6666666.
??>>>
??>>> Is there a way to do that?

JS> To tell the truth, I'm not quite sure what you want but if
JS> you have a decimal number, you could investigate formatting
JS> to a fraction. Format cells > Fraction

An interesting result occurs when a cell put =PI() is formatted
to a three digit fraction. The given answer is
3 16/113. Writing this as fraction, you get 355/113, which is
the best 3 digit approxoimation to Pi!

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

 

[bplumhoff]

Hi,

If the OP only intends to show this then your fractional format seems
to be easiest solution.

Another approach if he wants to use nominator and denominator later on:
Insert my UDF NRN() from
http://www.sulprobil.com/html/nearest_rational_number.html
Select cells A1 and B1 together. Enter =NRN(PI(),999) as array formula
(with CTRL + SHIFT + ENTER).

Regards,
Bernd