Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Waltham, MA, USA
    Posts
    160
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Post 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 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?
    Last edited by jsallen; 2011-10-27 at 22:17. Reason: Formating of posted message does not look like edit

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 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.
    Last edited by sdckapr; 2011-10-28 at 08:09.

  3. #3
    2 Star Lounger
    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 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!
    Last edited by jsallen; 2011-10-28 at 14:50.

  4. #4
    2 Star Lounger
    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?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    2 Star Lounger
    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?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •