A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Ratio - How To Create Your Own Function/Formula



 
 
Thread Tools Display Modes
  #1  
Old January 27th, 2007, 03:07 AM posted to microsoft.public.excel.worksheet.functions
Dawg House Inc
external usenet poster
 
Posts: 16
Default 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!"
  #2  
Old January 27th, 2007, 08:30 AM posted to microsoft.public.excel.worksheet.functions
Harlan Grove
external usenet poster
 
Posts: 520
Default Ratio - How To Create Your Own Function/Formula

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

  #3  
Old February 14th, 2007, 01:40 AM posted to microsoft.public.excel.worksheet.functions
Dawg House Inc
external usenet poster
 
Posts: 16
Default Ratio - How To Create Your Own Function/Formula

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:11 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.