Create an XL Worksheet for Continued Fractions

In this article, you will learn to create an Excel workbook worksheet to mostly automatically calculate Continued Fractions. This article expands on lessons learned from the article "How to Start Working with Continued Fractions", so you may want to review that article first.

Steps

Previous Lessons Learned

  1. In the previous article on Continued Fractions, How to Start Working with Continued Fractions, some generalized continued fraction expansions ("cfe") were shown for pi, which make much more sense than the seemingly random string of digits in the decimal representation of π.
  2. The cfe for one of phi's roots was also shown and partially explained.
  3. The beginnings of the tutorial for this article were also started but not thoroughly explained.
  4. It would be a good idea to review and work that article first before this one.

The tutorial

  1. From the Desktop, select the green X for XL on the Dock to open Excel, and select File, Open a New Workbook from the menu. Or, open Excel from within your Applications folder, and select File, Open a New Workbook from the menu. Or, better yet, from the previous article on Continued Fractions, Start Working with Continued Fractions, open it and use the worksheet, Approach 2.
  2. Open Preferences from the "Excel" menuitem.
    • Recommended Settings: Set General to R1C1 Off and Show the 10 Most Recent Documents;
    • Edit - set all the Top options to checked except Automatically Convert Date System.
    • Display number of decimal places = blank (for integers preferred), Preserve display of dates and set 30 for 21st century cutoff;
    • View - show Formula Bar and status bar, hover for comments and all of Objects, Show gridlines and all boxes below that auto or checked;
    • Chart - show chart names and data markers on hover. Leave rest unchecked for now;
    • Calculation -- Automatically and calc before save, max change .000,000,000,000,01 w/o commas if you do goal-seeking a lot and save external link values and use 1904 system;
    • Error checking - check all; Save - save preview picture with new files and Save Autorecover after 5 minutes;
    • Ribbon -- all checked except Hide group titles and Developer (unless you plan to write macros).
  3. Open the "Continued Fractions" worksheet saved from the previous article, "How to Start Working with Continued Fractions", or whatever filename you saved it under. Label worksheet 1 "Approach 1" and label worksheet 2 "Approach 2", or something similar.
  4. Enter a 1 into cell A4 and label the number of computations to be done via also doing
    • Format Number Custom "00) " for the 1 in cell A4; and
    • Select cell range A4:A21 and do Edit Fill Series (and leave the settings at Columns, Linear, Step Value 1), OK.
  5. Enter the Column Heading into Rows 2 and 3:
    • Enter the label Fraction to cell B3 (just enter the word "Fraction", w/o quotes);
    • Enter the label Find "/" to cell C3 (please include the quote marks;
    • Enter the label Length to cell D3;
    • Enter the label Numerator to cell E3;
    • Enter the label Denominator to cell F3;
    • Enter the label String to cell G2 and the label Formula to cell G3;
    • Enter the label Decimal to cell H2 and the label Number to cell H3;
    • Enter the label Integer to cell I2 and the label Part to cell I3;
    • Enter the label Decimal to cell J2 and the label Part to cell J3;
    • Enter the label Rounded to cell K2 and the label Remainder to cell K3;
    • Enter the label Fraction to cell L2 and the label String to cell L3;
    • Enter the label Inverted to cell M2 and copy and paste it to N2;
    • Enter the label Numerator to cell M3 and the label Denominator to N3;
    • Enter the label A to cell O3 -- it stands for Answer;
    • Enter the label f to cell P3 -- it stands for formula;
    • Select rows R1:R3 and format Align Center and format Bold using the tool buttons;
    • Select columns B:P and format Align Center and Format Number 0 decimal places, use comma; and
    • Select columns H and J using the Command key and Format Cells Number 15 decimal places.
  6. Enter the cell formulas into row 4:
    • Without a preceding = sign, enter the label 415/93 into cell B4 -- it is from the Wikipedia article on Continued Fractions, so that you may check the results;
    • Enter, without semi-quotes, the formula '=FIND("/",B4)' into cell C4 -- the returned value should be 4;
    • Enter, without quotes, the formula "=LEN(B4)" into cell D4 -- the returned value should be 6;
    • Enter, without quotes, the formula "=VALUE(MID(B4,1,C4-1))" into cell E4 -- the returned value should be 415 (that is, the MID function starts with the 1st character, 4, and finds the string equal to the occurrence of "/", less 1 -- that is 3 characters, the numerator 415);
    • Enter, without quotes, the formula "=VALUE(MID(B4,C4+1,D4-C4))" into cell F4 -- the returned value should be 93, the denominator;
    • Enter, without surrounding semi-quotes but keeping the quotes internal to the formula, the formula '="="&E4&"/"&F4' into cell G4 -- the returned value should be the string =415/93, the fraction;
    • Warning: Tricky Part! For each String Formula in column G, you must manually copy it and do Edit Paste Values into cell to the right, eg. paste special values into H4 from G4, or H5 from G5, etc. And then in the Formula Bar, you must click past the right end of the formula with the mouse as if editing, then hit the Enter or Return key, This will convert the string into an actual formula! Otherwise, a macro might be used, but that is beyond the scope of this article. The result in H4 should be 4.46236559139785; the result in H5 should be 2.16279069767442; the result in H6 should be 6.14285714285714; and the result in H7 should be 7.00000 ... for your future reference;
    • Enter, without quotes, the formula "=INT(H4)" into cell I4 -- the returned value should be the Integer Part, 4 of the Decimal Number, 4.46236559139785;
    • Enter, without quotes, the formula "=H4-I4" into cell J4 -- the returned value should be the Decimal Part .46236559139785 of the Decimal Number, 4.46236559139785;
    • Enter, without quotes, the formula "=ROUND(J4*F4,0)" into cell K4 -- the returned value should be the Rounded Remainder, 43;
    • Enter, without semi-quotes, the formula '=K4&"/"&F4' into cell L4 -- the returned value should be the Fraction String, 43/93;
    • Enter, without quotes, the formula "=F4" into cell M4 -- the returned value should be the Inverted Numerator, 93;
    • Enter, without quotes, the formula "=K4" into cell N4 -- the returned value should be the Inverted Denominator, 43;
    • Enter, without the external semi-quotes but keeping the 2 internal quotes to the formula, the formula '=IF(I4<>0,I4,"")' into cell O4 -- the returned value should be the (first part of the) Answer, 4 -- Format Cell Fill Canary Yellow;
    • Enter, without the external semi-quotes but keeping the 2 internal quotes to the formula, the formula '=IF(O4<>"",P3+1,0)' into cell P4 -- the returned value should be the (first part of the) Max formula, 1;
    • Select cell range B4:P21 and Edit Fill Down;
    • Select cell range H4:H23 and Format Cells Border Bold Outline Dark Blue per cell.;
    • Select cell range H5:H21 and Edit Clear Contents -- this is where you will do the tricky part of pasting special the values and editing in the formula bar to turn the string into a live formula manually;
    • Enter, without the external semi-quotes but keeping the 2 internal quotes to the formula, the formula '=M4&"/"&N4' into cell B5 -- the returned value should be the Inverted Fraction(s);
    • Select cell range B5:B21 and do Edit Fill Down;
    • Select cell range B2:N21 and do Format Cells Border Dark Blue Boldest Border Surrounding Border to all the cells;
    • Select cell range B2:O21 and do Format Cells Border Dark Blue Boldest Border Surrounding Border to all the cells;
    • Select cell range B2:P23 and do Format Cells Border Dark Blue Boldest Border Surrounding Border to all the cells;
    • Select cell G22 and enter, without quotes but with the trailing space, "ANSWER: ", and do the same for cell G23;
    • Enter to cell H22 the formula, without surrounding semi-quotes but keeping all internal quote marks, '="["&O4&"; "&O5&", "&O6&", "&O7&", "&O8&", "&O9&", "&O10&", "&O11&", "&O12&", "&O13&", "&O14&", "&O15&", "&O16&", "&O17&", "&O18&", "&O19&", "&O20&", "&O21&"]"' -- the result of this formula will be [4; 2, 6, 7, , , , , , , , , , , , , , ];
    • Enter to cell H23 the formula, without surrounding semi-quotes but keeping all internal quote marks, '=MID(H22,1,MAX(P4:P20)*IF(MAX(P4:P20)>9,2.5,2)+MAX(P4:P20)-1)&IF(MAX(P4:P20)>9,"","]")' -- the result of this formula will be [4; 2, 6, 7], the Answer.
    • Go ahead now and do the tricky part for cells H5, H6 and H7 by copying the formulas in G5 and doing Paste Special Values to H5, then editing in the Formula Bar at the far right of the formula with the Edit Bar of the mouse, once, then clucking Enter (or Return). Do so for G6--H6 and G7--H7 as well. Check your results with the values given above "for future reference".
    • Your Final Answer then should be [4, 2, 6, 7].
    • Hooray! You finished the hard part!

Example: 2nd root of Phi

  1. Figure out the Answer for the second root of Phi. Phi as two roots, since quadratically its roots are determined by (1±sqrt(5))/2. In the previous article, "How to Start Working with Continued Fractions", we looked at the Continued Fraction for one root, 1.61803398874989, namely [1; 1, 1, 1, 1, 1, 1, 1, 1, ...] and found out it is the most irrational of all the irrational numbers, in that it converges the most slowly, due to the 1's in its Continued Fraction being the least possible divisors. Now you'll derive the Continued Fraction for the other root, -0.618033988749895, i.e. (1-(sqrt(5))/2.
    • Copy the mostly-outlined cell range A2:P23 and paste it to P28;
    • Select cell range H30:H47 and do Edit Clear Contents;
    • Enter to cell B30 the formula, '="-618033989/1 000 000 000"', omitting the external semi-quotes and spaces between the zeroes but keeping the internal quote marks;
    • Adjust column width for column B and any other columns that need adjusting;
    • Do the Tricky Part for cells G30:G47 to H30:H47. Here are your check values:
      • -0.618033989
      • 2.61803399046414
      • 1.61803398426194
      • 1.61803400049951
      • 1.618033957989
      • 1.61803406928298
      • 1.61803377791157
      • 1.61803454073199
      • 1.61803254364331
      • 1.61803777209691
      • 1.61802408387945
      • 1.61805992045296
      • 1.61796610151831
      • 1.6182117393544
      • 1.61756876542705
      • 1.61925287673592
      • 1.61484917966145
      • 1.62641511622512
  2. Check your final Answer: [-1; 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
    • Question your work: Is the Continued Fraction just calculated for -.618 033 989 000 000 going to be the same as that for -0.618 033 988 749 895 ?
  3. Use the means devised in the previous article, "How to Start Working with Continued Fractions", the long column -- or see worksheet Approach 1.
    • Copy cell A4 and paste it to cell Q3;
    • Select cell range Q3:Q49 and do Edit Fill Series, with Step Value 1, Columns, etc. as defaults, OK;
    • Enter to cell R2 the label PHI, root 2, and enter to cell S2 the formula =(1-sqrt(5))/2, then select R2:S2 and Format Cells Font Red, Bold and Fill Canary Yellow;
    • Format Cells for column Q Number 15 digits and adjust column width to 1.67";
    • Enter -1 into cell R3 and enter 2 into cell R4;
    • Select cell range R5:R49 and with R5 as the active cell, enter 1, and then do Edit Fill Down;
    • Enter to cell T48 the message, This formula is different
    • And enter to cell T49 the message, from this formula.
    • Enter to cell S49 the formula, =R48+1/R49 and Format Cells Fill Canary Yellow;
    • Enter to cell S48 the formula, =R47+1/S49
    • Copy cell S48 and select range S47:S4 and Paste;
    • Select cell S4 and Format Cells Font Bold, Dark Blue;
    • Select cell S3 and enter the formula, =S2-S4, and do Format Cells Number Custom "Difference = "0.000000
    • Having done everything correctly, you should have 0.000 000 000 000 000 difference between the answers in cells S2 and S4!
    • You did it! You proved that, even though the other formulated spreadsheet method does not converge, the answer that it arrives at is correct, as far as it goes that is.

Helpful Guidance

  1. Make use of helper articles when proceeding through this tutorial:
    • See the article How to Create a Spirallic Spin Particle Path or Necklace Form or Spherical Border for a list of articles related to Excel, Geometric and/or Trigonometric Art, Charting/Diagramming and Algebraic Formulation.
    • For more art charts and graphs, you might also want to click on Microsoft Excel Imagery, Mathematics, Spreadsheets or Graphics to view many Excel worksheets and charts where Trigonometry, Geometry and Calculus have been turned into Art, or simply click on the category as appears in the upper right white portion of this page, or at the bottom left of the page.

Tips

  • Just as an exercise, take the first 1000 digits of pi and put them into a column, to see what Continued Fraction will be created. See if you can make any sense of it. One answer is that when it is raised to e, , the result is close to 5/3. The number is 3.82865616205118* Here are the first 1000 digits of π (you have to convert them from text to values first, one by one, in a column, with a formula like =VALUE(MID(Objective,V3,1)) where the 1000 digit text string is the defined variable Objective and in V3 is a 1, but in V4 is a 3 -- to skip the decimal point, after that it's successive to 1000 in column V)(if XL fails you, try opening the copied portion of the file in a text doc first, like in Word, and inserting letters between groups of numbers):
  • http://mathforum.org/library/drmath/view/53892.html

Related Articles

References

Retrieved from "https://kipkis.com/index.php?title=Create_an_XL_Worksheet_for_Continued_Fractions&oldid=286914"