I need help with ratios

[Guest]

I need to take information in 2 different cells, divide it, and show the
result as a ratio 2:1.
I know how to set the formula to divide the 2 cells, but the result is a
percentage, not in the format I need to see, which is 2:1.
Help?

 

[Guest]

Hi,

Assuming Cells A2 divided by B2, how about:
=concatenate(A2/B2,":1")

HTH,

Alain

 

[Rick Rothstein \(MVP - VB\)]

I think this will do what you want...

=A1/GCD(A1,B1)&":"&B1/GCD(A1,B1)

The GCD (greatest common divisor) function requires the Analysis ToolPak add
in (Tools/Add-Ins from Excel's menu bar).

Rick

 

[JE McGimpsey]

one way:

Format/Cells/Number/Custom 0\:\1

 

[Guest]

=A1/B1

Then:

Format > Cells... > Number > Custom > General":1"

 

[Rick Rothstein \(MVP - VB\)]

That doesn't seem to work correctly... try it with 12 and 8 as the two
numbers.

Rick

 

[RagDyer]

Just add the provision for more digits.

#.#\:\1
#.##\:\1
OR
#.#":1"
#.##":1"

BTW - try yours with 100 and 33

Not wrong ... but doesn't equate to "1"

 

[JE McGimpsey]

What do you mean by work correctly"? The key thing is the

\:\1

You could prefix it with any other number format, e.g.:

0.0\:\1

or

0 #/#\:\1

or even

##0.0E+0\:\1

 

[Rick Rothstein \(MVP - VB\)]

The format you posted will return 2:1 for 12 and 8, but that is not their
correct ratio. Adding the extra decimal places in the format solves that
problem; but I still have a question about the ':1' result.

Okay, now, perhaps I misread the OP's request (or more than likely, read too
much into it); but in my experience when presenting the x:y ratio (odds?) of
two numbers, you do that with whole numbers for the x and y. So, my example
of 12 and 8 would be shown in a ratio of 3:2 and "pronounced" three-to-two;
whereas your result would be 1.5:1 (one-and-one-half to one). So the
question is whether the OP wants the ratio to always be against ':1' or not.
Unfortunately, she did not give an example by which we could decide.

Rick

 

[Rick Rothstein \(MVP - VB\)]

BTW - try yours with 100 and 33

100 and 33 are what is called relatively prime, so they do not contain and
common divisors; hence, they cannot be reduced any further.

Not wrong ... but doesn't equate to "1"

That may be a misunderstanding on my part. See my response to JE in this
sub-thread for an explanation of why I posted what I did.

Rick

 

[JE McGimpsey]

Ah. You're absolutely right. I made the opposite assumption from you,
and there's no good reason for it.

If the OP wants integral divisors, and doesn't mind the result being
Text rather than a number (probably a good assumption):

=SUBSTITUTE(TEXT(A1/A2,"0/0"),"/",":")

(I don't know if that's been suggested already - didn't see it in *this*
thread).

 

[Rick Rothstein \(MVP - VB\)]

Ah. You're absolutely right. I made the opposite assumption from you,

and there's no good reason for it.

Actually, there can be a good reason for that format too. I was a Civil
Engineer during my "working life" and side-slopes were always given as
ratios against ':1'... 1.5:1 side slope for example; but that was a
specialized usage. As I said, the real problem is the OP gave us no context
to base an answer on.

If the OP wants integral divisors, and doesn't mind the result being
Text rather than a number (probably a good assumption):

=SUBSTITUTE(TEXT(A1/A2,"0/0"),"/",":")

I like that formula better than mine as it does not rely on the Analysis
ToolPak the way mine does.

Rick