Abominable....yes!
And while you are correct, that these formulas worked for me, I didn't
exhaustively test the formulae. I do thank you for testing/correcting them.
They certainly are more effective in your example...and a whole heck of a lot
less typing.

A lesson learned.

Thanks Harlan,
JCH
============


--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Harlan Grove" wrote:

Dawg House Inc wrote...
Well, after hunting on here for suggestions for how to create my own ratio
formula, I ended up (through trial and error and testing) manufacturing my
own formula.

So others don't end up in the same predicament, here's my solution with a
table reference example:
....

Noble sentiment, but your formulas are abominable.

Formula 1
=IF(B2 = 0, 0, IF(C2 = 0, 0, IF(LEN(ROUND(B2, 0)) LEN(ROUND(C2, 0)),
ROUND(B2/C2, 0) & ":" & C2/C2, IF(LEN(ROUND(B2, 0)) LEN(ROUND(C2, 0)),
B2/B2 & ":" & ROUND((C2/B2), 0), IF(B2C2, ROUND(B2/C2, 0) & ":1", "1:" &
ROUND(C2/B2, 0))))))

Formula returns 0 if either B2 or C2 is zero, so use a single IF call.

Note that if B2 were negative, say -5, while C2 were positive, say 3,
using string length comparisons would show the text representation of
B2 longer than that of C2. Is that really what you want? Well, maybe so
for you, but unlikely so for anyone else.

C2/C2 and B2/B2 would either be 1 (nonzero numbers), #DIV/0! (either
zero or blank), or #VALUE! (either nonnumeric text). If you want 1,
just use 1.

Or just rewrite as

=IF(OR(B2=0,C2=0),0,IF(B2C2,ROUND(B2/C2,0)&":1","1:"&ROUND(C2/B2,0)))

Formula 2
=IF(B2 = 0, 0, IF(D2 = 0, 0, IF(LEN(ROUND(B2, 0)) LEN(ROUND(D2, 0)),
ROUND(B2/D2, 0) & ":" & D2/D2, IF(LEN(ROUND(B2, 0)) LEN(ROUND(D2, 0)),
B2/B2 & ":" & ROUND((D2/B2), 0), IF(B2D2, ROUND(B2/D2, 0) & ":1", "1:" &
ROUND(D2/B2, 0))))))

Same comments replacing C2 with D2. Replace with

=IF(OR(B2=0,D2=0),0,IF(B2D2,ROUND(B2/D2,0)&":1","1:"&ROUND(D2/B2,0)))