Ratio - How To Create Your Own Function/Formula

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 FanItems (18
Fans to 1 Item Sold) and FansRev (1 Fan to $4 Revenue)
Team 1 1016 1016 $24,195 0 0 Shows ratio where Fans = Items and FansRev
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(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))))))

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.

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