Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22
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 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(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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22
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)))


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merge data rows at fixed ratio AirBoss Excel Discussion (Misc queries) 13 December 19th 06 04:23 PM
create self-generating numbers with letters and numbers cxlough41 Excel Discussion (Misc queries) 11 January 4th 06 02:16 AM
Create database in excel? wirthless New Users to Excel 7 October 30th 05 03:57 PM
quickly create extra copies of a worksheet template in a workbook simon Excel Worksheet Functions 2 October 23rd 05 07:04 PM
Create a tabular control rkg Excel Discussion (Misc queries) 0 August 8th 05 09:25 PM


All times are GMT +1. The time now is 01:19 PM.

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

About Us

"It's about Microsoft Excel"