Prev: SUMPRODUCT QUESTION?, Ratios
Next: SOS! how do I run Excel without running the VBA in AUTO_OPEN?
From: MrBill on 4 May 2010 00:13
repost: little more accurate:


hi, i am trying to add ratio's. not sure if wording all correctly.
but here it goes.

I will have multiple ratios, there might be 1, or 5 ratios:
1:2 1:5 1:20 5:3 10:1

I have the max value of numbers adjusted for ratio changes:
200

would rather have 1 ratio in 1 cell, as: 1/2, and 4 other cells: 1/5
1/20 5/3 10/1
but can put in separate cells
(rephrase: rather 5 cells, but can do 10)

0. what is a formula to caclulate product/sum? of ratios.

1. what is the uninflated max: eg (200 to 100 to 20 to 20/ (5/3) or
20/ 1.666 or: 12 x 10 = 120)
(i.e. max values needed to calculate other items, max is manageble/
realistic eg: 15, instead of 10,000)

although same formula might figure out an under, am more concerned with the
over-value.

some examples of equations I have found: (seems wrong, for 2/6 etc, when
item raised 2:6 or 1:3 times larger)
add all numerators together (or 1st number), add all denominators together
(2nd number)
2/6 + 7/8 = 9/14 (same as a ratio? maybe not)

??
Aleft + Bleft / Aright + Bright = value / 14
(Aleft + Bleft)/ (Aright + Bright) / Aright + Bright = (9/14) / 1, or
value??
..6248

max value eg 50 * .6248 = 31.24 -or- 30 (can round to 5)

wrapped around an axle, thanks in advance.

----------
problem? with the following? (besides only 2 inputs)
1/10 1/10, if high in numbers range was '10', would be raised 10x, twice:
or 1000?
below formulas both show ans to be: 1:1
guessing 1/10 not same as 1:10. focus is 1:10


Excel provides no direct way to display the ratio between two values?. eg
cell A1 = 3, B1 = 24. The ratio = 1:8.
Following displays the ratio between the values in cells A1 and B1:

=(LEFT(TEXT(A1/B1,"####/####"),FIND("/",TEXT(A1/B1,"####/####"))-1)&":"
&RIGHT(TEXT(A1/B1,"####/####"),LEN(TEXT(A1/B1,"####/####"))
-FIND("/",TEXT(A1/B1,"####/####"))))
The formula automatically reduces the "fraction" to the simplest form, and
it allows up to four characters on either side of the colon.

a much simpler formula that produces the same result, but does not have the
four-character limit:
=A1/GCD(A1,B1)&":"&B1/GCD(A1,B1)

GCD function is available only with the Analysis Toolpak Add-In installed.
Note: Be aware that the result of these formulas is a text string, not a
fractional value. For example, the ratio of 1:8 is not the same as 1/8.
GCD Returns the greatest common divisor of two or more integers. The
greatest common divisor is the largest integer that divides both number1 and
number2 without a remainder.


From: MrBill on 4 May 2010 00:21

ans?: numerator to 1, and divide max by sum of (adjusted) denominators?
 | 
Pages: 1
Prev: SUMPRODUCT QUESTION?, Ratios
Next: SOS! how do I run Excel without running the VBA in AUTO_OPEN?