Results 1 to 6 of 6

20111027, 21:08 #1
 Join Date
 Mar 2002
 Location
 Waltham, MA, USA
 Posts
 160
 Thanks
 0
 Thanked 2 Times in 2 Posts
Reducing formulas, calculating fractions in Excel
Here's a formula I'm using in Excel, with integers inserted for one case:
=(114+(88*(1(97+38)/97))+(88+114)*(1+51*21/(87*16)1))/114
FYI, this describes one ratio of a rather complicated threesection planetary gear system of a bicycle hub. You may read more about it here: http://sheldonbrown.com/elan.html. Other ratios of this hub use the same formula with different integers representing different gear tooth counts.
The formula, with the values shown, reduces to
=5287177/2565456
Factoring 2565456 using division in Excel (divisors in the left column), I get
2565456
2 1282728
2 641364
2 320682
2 160341
3 53447
19 2813
29 97
Factoring 5287177, I get
5287177
7 755311
Sorry if that looks ragged, I can't figure out how to format it.
There's no point taking the factoring any further because I tried 19, 29 and 97, they are not factors of 755311 and so there is no common factor with 2565456
The decimal value, to 15 places, as many as Excel calculates (and maybe one more), is
2.060911198632910
To display a fraction, instead of factoring, Excel employs an approximation process which only produces results out to 3 places in the denominator (sometimes four, inconsistently), and which gives incorrect results if the denominator would have to be longer. Excel gives
2 12/197
for the result when the cell contains the decimal value or the calculated fraction, and
2 369/6058
when integer values are calculated in other cells and plugged into the longer formula!
Close, as the saying goes, but no cigar.
Is there a way (possibly using a macro) to get Excel to reduce a formula, and generate an exact fractional result using the least common denominator? Or can someone recommend other software which will correctly perform this rather mundane task?Last edited by jsallen; 20111027 at 21:17. Reason: Formating of posted message does not look like edit

20111028, 04:49 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
But does the exact reduced fraction:
2 156265 / 2565456
make more sense than just rounding to a decimal number: 2.06091
This has 6 significant digits which seems to be more than enough required precision to me. Typically 34 significant figures are sufficient and more is just "calculation diarrhea".
To get that fraction in Excel, format with extra characters It does not require VBA. A custom format of (it has more than enough digits to display the required 7):
# ?/???????????????
will display:
2 156265 / 2565456
You just need to ensure that the number of question marks in the denominator are larger than the number of digits in the exact number for an exact fraction.
Steve
PS since 15625 = 3*15*31253 and 256456 = 2^4*29*57*97 there is no more reduction possible so any fraction with a smaller length denominator would be an approximation, though the more digits the better the approximation (2, 2 5/82, 2 12/197, 2 369/6058, 2 4440/72893, 2 29477/483934)
PPS the least common denominator for the fractions can be seen more easily by reducing your formula:
= 1 88*38/(97*114) + (88/114 +1) * 51*21 / (87*16)
The LCD of 97*114, 114, and 87*16 or (2*57*97), (2*57), (2^4 * 3 *29) = 2^4 * 3*29*57*97 = 7696368.
But the ultimate question is not about the LCD, which you don't need to do the math, but you need to reduce the fraction, which doesn't need the LCD, you need to reduce the LCD based on the muliples of the numerator after they are summed. The numerator and the LCD have 3 as the common multiple so the final denominator is the LCD/3.Last edited by sdckapr; 20111028 at 07:09.

20111028, 13:24 #3
 Join Date
 Mar 2002
 Location
 Waltham, MA, USA
 Posts
 160
 Thanks
 0
 Thanked 2 Times in 2 Posts
Ah, I didn't know that Excel would calculate a fraction with more than 3 digits in the deonominator using a custom format. That feature was not well documented. Thanks.
As to the calculation diarrhea issue, gear ratios are one of few applications of mathematics in which the input numbers are exact, and so the result also can be exact. For purposes of calculating the rolling distance of a bicycle per turn of the wheel, you are correct, three digits after the decimal point are ample. That is all I offer in the table I provide for use in calibrating digital odometers or calculating drive ratios. Variations in tire pressure and load reduce precision more than that. However, for the purpose of checking calculations, it is helpful to compare the result as a reduced fraction against the decimal value, and so more precision is to advantage. In any case, Excel offers such precision for free.
Also it is an interesting point that the particular bicycle hub I am examining generates some very complicated fractional ratios. Such ratios, and the precision they can achieve, have other applications as well. One is in the Hammond Organ, whose genius was in very closely approximating the 12 ratios of the equaltempered musical scale using gearing, and driving the gearworks with a synchronous electric clock motor, avoiding the need for tuning. In musical scales, great precision is important because outoftuneness is audible as small differences between large numbers. Similar issues arise when using digital frequency dividers to generate musical scales  only the problems are even thornier because frequencies can only be divided, not added, subtracted or changed by an integer ratio as with gearing. High clock frequency is desirable in that application!Last edited by jsallen; 20111028 at 13:50.

20111028, 13:36 #4
 Join Date
 Mar 2002
 Location
 Waltham, MA, USA
 Posts
 160
 Thanks
 0
 Thanked 2 Times in 2 Posts
And here's another question, though: is there a custom format which will make Excel display an improper fraction (numerator more than the denominator), such as 3/2 or 187/186?

20111028, 14:35 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Don't put the pound (#) in it. Instead of:
# ?/????
Use:
?/????
Steve

20111031, 11:00 #6
 Join Date
 Mar 2002
 Location
 Waltham, MA, USA
 Posts
 160
 Thanks
 0
 Thanked 2 Times in 2 Posts
Thanks.
Another question. If I use the format ?/???? of # ?/????, but the fraction doesn't have that many places in the denominator, I've seen the fraction center in a cell as if those places were occupied  that is, it will be left of center. How do I overcome this without having to format to the exact number of places in the denominator and run the risk that an inaccurate result will be displayed if the input changes and requires more places in the denominator?