M
MrBill
SUMPRODUCT QUESTION?, Ratios
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 add the full ratio in 1 cell, as: 1/2 1/5 1/20 5/3 10/1
but can put in separate cells
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)
2. I would like to know what the current "UN-INFLATED" "max" value should
be less than 200. (should be 120? here)
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
??
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
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.
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 add the full ratio in 1 cell, as: 1/2 1/5 1/20 5/3 10/1
but can put in separate cells
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)
2. I would like to know what the current "UN-INFLATED" "max" value should
be less than 200. (should be 120? here)
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
??
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
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.