From: MrBill on 3 May 2010 23:56 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. From: MrBill on 3 May 2010 23:59 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 (rephrase: rather 5 cells, but can do 10)  |  Pages: 1 Prev: Current dateNext: SUMPRODUCT QUESTION?, Ratios