Ratio - How To Create Your Own Function/Formula

[Guest]

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:

<<-->>
Team(A) Fans(B) ItemsSold(C) Revenue(D) FanstoItemsSold(E) FanstoRevenue(F) WhatitShows(G)
Team 1 49199 2768 $220,901 0 0 Shows ratios for both where Fan>Items (18
Fans to 1 Item Sold) and Fans<Rev (1 Fan to $4 Revenue)
Team 1 1016 1016 $24,195 0 0 Shows ratio where Fans = Items and Fans<Rev
Team 2 1 0 $0 0 0 Shows how to handle 0 for Items and Revenue
Team 3 0 0 $0 0 0 Shows how to handle all 0s
Team 4 0 43036 $2,057,390 0 0 Shows ratio where Fans = 0 and Items and Rev
<> 0
Team 5 1009 111451 $1,546,526 0 0 Shows ratio where Fans < Items and Fans <
Rev
<<-->>

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(B2>C2, 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(B2>D2, ROUND(B2/D2, 0) & ":1", "1:" &
ROUND(D2/B2, 0))))))

To test this example:
1. Copy the above table between (but not including) the <<-->> separators -
do not alter the format
2. Flip over to Excel and in an empty worksheet, paste the values in cell A1.
3. Note that the cell values for Columns E and F are set to '0'
4. Copy and paste Formula 1 in cell E2
5. Copy cell E2 to E2:E7
7. Copy and paste Formula 2 in cell F2
8. Copy cell F2 to F2:F7

The explanations in the "What It Shows" column indicate what the different
ratios mean.

Hope you find this helpful.
Good luck.

 

[Harlan Grove]

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(B2>C2, 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(B2>C2,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(B2>D2, 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(B2>D2,ROUND(B2/D2,0)&":1","1:"&ROUND(D2/B2,0)))

 

[Guest]

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