Reducing formulas, calculating fractions in Excel

Topic

New Reply

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 three-section 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?

Reply | Quote

Replies

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 3-4 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.

Reply | Quote

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 equal-tempered 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 out-of-tune-ness 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!

Reply | Quote

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?

Reply | Quote

Don’t put the pound (#) in it. Instead of:

# ?/????

Use:
?/????

Steve

Reply | Quote

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?

Reply | Quote