How to Chart Linear Area Between a Power Function and Its Derivative

Three Parts:The tutorialExplanatory Charts, Diagrams, PhotosHelpful Guidance

You will learn how to chart the Linear Area of a Power Function with coefficient and constant versus its Derivative in Microsoft Excel per the following steps, as Calculus may also be used in creating Microsoft Excel Imagery. Taking the difference between two functions is a different process, though you can use subtraction to show what is meant in simple terms. Actually, one needs to integrate the functions to find the areas under the curves and take the appropriate differences and sums at that point. However, this method works along the lines of the Arc Length method of finding the distance between two points, and with a reasonably small change in incrementing x, arc length should provide an approximation to the area between the curves, when one multiplies by the increment, so long as the lines don't cross or miss too much area by being at an angle instead of erect and side-by-side.


  • Become familiar with the basic chart to create:

Part 1
The tutorial

  1. 1
    This article assumes you know the Power Rule for taking derivatives in Calculus, but if not, please see the Related wikiHows below on How To Take Derivatives. In any case, the rule is for f(x) = x^n, the derivative f '(x) = nx^(n-1), and the n now in the coefficient is multiplied by any pre-existing coefficient. So, for example, the derivative of 6x^3 is 3*6x^(3-1) or 18x^2. The derivative of a constant C = 0. For this article you will also need to know how to use Microsoft Excel and it's a good practice to follow the steps exactly as given. Step 1 then is to open a new workbook and create two worksheets titled Data and Saves.
  2. 2
    Set the Preferences under the Excel menu:
    • In General, set R1C1 to Off and select Show the 10 Most Recent Documents .
    • In Edit, set all the first options to checked except Automatically Convert Date System . Set Display number of decimal places to blank (as integers are preferred). Preserve the display of dates and set 30 for 21st century cutoff.
    • In View, click on show Formula Bar and Status Bar and hover for comments of all Objects . Check Show grid lines and set all boxes below that to auto or checked.
    • In Chart, allow Show chart names and set Data markers on hover and leave the rest unchecked for now.
    • In Calculation, make sure Automatically is checked and Calculate before save is also checked. Set max change to .001 as goal-seeking is not done in this project. Check Save external link values and Use 1904 system
    • In Error checking, check all the options.
    • In Save, select Save preview picture with new files and Save Autorecover after 5 minutes
    • In Ribbon, keep all of them checked except Hide group titles and Developer .
  3. 3
    Go to cell A8 and do Freeze Panes under the Window menu. Select columns A:J and Format Cells Number Number Custom +0.0000;-0.0000;+0.0000, Font Size 9 or 10.
  4. 4
    Create the Defined Variables Upper Section (here's a picture):
    • We will be entering the function -5x^3 + 5 and the derivative -15x^2 with the derivative being calculated automatically. In that way, you can concentrate on the functions you wish to see the derivative (arc length) "differences" for.
    • Select row 1 and make Font bold.
    • Into cell A1, enter x, color blue.
    • Into cell B1, enter eg. y=x^3; y=3x^2 and make the eg. y=x^3 blue font bold and the y=3x^2 black font bold by selecting over them in the formula bar and using the Text Color Tool.
    • Into cell C1, enter Function and do Font color blue.
    • Into cell D1, enter Derivative and do Font color black.
    • Select cell range F1:J1 and make font color blue.
    • Into cell F1, enter F ... (for Function).
    • Into cell G1, enter d ... (for derivative).
    • Into cell H1, enter From a.
    • Into cell I1, enter To b.
    • Into cell J1, enter Increment.
    • Into cell J2, enter .25 and Insert Name Define Name Increment to cell $J$2 and format fill canary yellow for input.
    • Into cell I2, enter "=A362" w/o quotes. This is the end of the x Range of values.
    • Into cell H2, enter -30 and format font red and format fill canary yellow for input.
    • Into cell E2, enter Power. Into cell E3, enter Coefficient. Into cell E4, enter Constant.
    • Format Fill cell range F2:F4 canary yellow for input.
    • Input 3 into cell F2 and Insert Name Define Name F_Power to cell $F$2.
    • Input -5 into cell F3 and Insert Name Define Name F_Coefficient to cell $F$3.
    • Input 5 into cell F4 and Insert Name Define Name F_Constant to cell $F$4.
    • Input "=F_Power-1" into cell G2 and Insert Name Define Name d_Power to cell $G$2.
    • Input "=F_Power*F_Coefficient" into cell G3 and Insert Name Define Name d_Coefficient to cell $G$3.
    • Input "=F_Constant*0" into cell G4 and Insert Name Define Name d_Constant to cell $G$4.
    • Enter TEXT: into cell E5.
    • Input ="("&TEXT(F_Coefficient,"0.0000")&"*x^"&TEXT(F_Power,"0.0000")&" + "&TEXT(F_Constant,"0.0000")&")"&" - "&"("&TEXT(d_Coefficient,"0.0000")&"x^"&TEXT(d_Power,"0.0000")&" + 0"&")", including all quotation marks into cell F5.
    • Enter Paste Text: into cell E6. Copy the value in cell F5 and do Paste Special Value into cell F6; this will be the title for your Chart.
  5. 5
    Enter the Column Formulas:
    • Into cell A2, input "=H2" w/o quotes. You can Define Name for H2 later if you wish.
    • Into cell A3, input "=A2" w/o quotes.
    • Edit Go To cell range A4:A362 and enter into cell A4 the formula w/o quotes "=IF(A3=A2,A3+Increment,A3)" and Edit Fill Down. Format Fill Color Light Rose. Edit Go To cell range A2:A362 and Insert Name Define Name x to cell range $A$2:$A$362. Approximately 360 rows are being used because graduation to trigonometric functions is anticipated.
    • Edit Go To cell range B2:B362 and input to cell B2 the formula w/o quotes "=IF(A1<>A2,Function,Derivative)" and Edit Fill Down.
    • Edit Go To cell range C2:C362 and input to cell C2 the formula w/o quotes "=F_Coefficient*x^F_Power+F_Constant" and Edit Fill Down. Insert Name Define Name Function to cell range $C$2:C$362.
    • Edit Go To cell range D2:D362 and input to cell D2 the formula w/o quotes "=d_Coefficient*x^d_Power+d_Constant" and Edit Fill Down. Insert Name Define Name Derivative to cell range $D$2:D$362.
    • Select columns L:M and Format column width .35"
    • Edit Go To cell range M1:M362 and input 1 into cell M1 and do Edit Fill Series Column Linear Step Value 1, OK.
    • Edit Go To cell range L1:L362 and enter to cell L1 the following formula w/o quotes, "=IF(ODD(ROW())=ROW(),0,1)", and Edit Fill Down. Copy this cell range and do Paste Special Values right back over it.
    • Select rows 1:362 and do menu item Data Sort, and at the next to the bottom under Column when you click in its space, will be a zero -- select it because we are going to sort all the odd rows to the top because the derivatives are in the odd rows in column B, and we want to format all those bold black font. Click on the plus button at the bottom left of the Sort dialog box to bring up the Then by sort line. Click in the Column column and Then By Row and select the very bottommost 1 -- meaning we want to otherwise keep the rows in their original order after the preliminary sort. Click OK (shut your eyes first! Just kidding!). Order should be Smallest to Largest on both cases.
    • The derivatives in column B end below where in column M the last odd number, 361, appears. Select B2:B181 and Format Font black bold. Your nice sheet is a total wreck with errors all over the place, right?? Let's fix that.
    • Select rows 1:362 and do menu item Data Sort. See the little Minus symbol at the bottom left? We want to subtract out the top first sort by odd numbered rows, so select that line and then hit the Minus button. Order should still be Smallest to Largest on the remaining sort of the original row order. Close eyes. But hit OK first. Et voilá! Everything is cool.
  6. 6
    Calculate Arc Length
    • Select O1 and enter Arc Length. Edit Go To cell range O3:O362 and enter to O3 the formula "=SQRT((A3-A2)^2+(B3-B2)^2)" w/o quotes and Edit Fill Down. Enter to cell O363 "=SUM(O3:O362)" and Format Cell border dark blue bold outline. Enter to cell N363 Total Arc Length and Format Align right cell range N363:N365. Enter Limit a to Limit b / 361 to cell N364, and enter to cell N365 Arc Length Area. Enter "=B366" to cell O364 and enter to cell O365 "=O363*O364" w/o quotes. Enter "=A362-A2" to cell B365 and enter to cell C365 Limit a to Limit b. Enter to cell B366 "=B365/361" w/o quotes and enter to cell C366 Limit a to Limit b / 361. For the Integration Formulas and so forth, follow the formulas copied to the right in the picture in Tips where it discusses "The Total Area by Integration ..." in order to arrive at that total.

Part 2
Explanatory Charts, Diagrams, Photos

  • (dependent upon the tutorial data above)
  1. 1
    Create the Chart:
    • Because the column B y values vary between the Function and its Derivative, the chart draws lines connecting successive {x,y} pairs and so fills in the difference. We help the chart fill the space a bit by making the line a little weightier than normal, but not so much as to badly distort the results really.
    • Edit Go To cell range A2:B362 and using either Chart Wizard or the Charts option on the Ribbon, select Charts, All/Other, Scatter, Smooth Line Scatter. There's probably sufficient space to work with the chart that appears on the Data worksheet, or you can copy or cut it to a separate Charts worksheet.
    • Go to Paste Text in cell F6 and get the Chart Title; copy it. Select Chart Layout and select Chart Title and wand over the default text and paste in the text you copied previously. You may need to play with it a bit as far as location and font size, etc. to get it just how you prefer. Other than that, the assignment is complete. Ooops. Almost forgot. Do Chart Layout Current Selection Series 1 Format Selection. Make Line Weight 8 pt (or not -- it's up to you; 1 is fine if the increment is right for the range.) -- (it's a matter of whether you're after some artistic effect or the strict mathematical depiction; below in Tips I give the strict mathematical depiction for the example function and derivative).
  2. 2
    See Tips below for several more example charts.
  3. 3
    Save the upper data section that is sufficient to re-create the form, cell range A1:M7, by copying and pasting it to the Saves worksheet. Then again, just below that, make another copy bu this time do Copy Picture and Paste Picture with the shift key held down, or Paste Special Values -- either way, and then a copied and pasted picture of the chart. Be fastidious and methodical about your work and pay attention to details. Save the workbook every so often, just in case. Maintain a good backup system. Do Insert New Comment on original variable values and formulas by copying them from the formula bar so you can easily get back to where you started without having to dump the file and start over completely. This worksheet will be used in the near future by me as a building block to more advanced lessons, so think about a folder system for Calculus work please. That implies that almost all the previous work was either Geometry or Trig, with Algebra applied to both. There will be more of each in the future, God Willing.
  4. 4
    You're finished! Time is limited, unfortunately. The basic thrust so far has been to cover turning Math into Art; that is the specialty deserving attention. Why? Because it preserves the creative aspect of mathematics -- it's that simple. When looking at these forms, one can make out a neckline here, a man wearing a tee-shirt in this other one -- they are meant to stimulate the imagination. If math gets taught along the way, it's a great side benefit!

Part 3
Helpful Guidance

  1. 1
    Make use of helper articles when proceeding through this tutorial:


  • Strict Depiction for the Example Function

  • The Total Area by Integration = -1,193,906.25 and by Arc Length calculation the Arc Length Area = 1,192,937.13, or 99.9188% of the Area by Integration -- so in this case, they're very close and the Arc Length is a good approximation of Area:

  • Sample f(x) = 1/π x^3 + sqrt(2)

  • For x = ±15:
    f(x) to f\'(x) = x^3 +10 to 3x^2


  • For x = ±15:
    f(x) to f\'(x) = x^10 +10 to 10x^9

  • (x^(3:2)-14) to 4.5x^(1:2)

  • For x = ±1; Increment = .0111...:
    (3x^3 - 14) to (9x^2) banner

  • You may wish to see the Related WikiHows for the Article on Arc Length in order to have a check on the measure of AREA.


  • The "Increment" is related to "the change in x" you learn of when Calculus is introduced, but it is NOT the same thing as delta x or dx, which become so infinitesimal that they approach 0 and vacate, beyond the capacity of the computer.
  • ERRORS: If there are unplanned errors or error values that have otherwise not been discussed above, either the sheet in incomplete and needs further input or Lookup Tables for critical variables or perhaps there's a mistake somewhere along the line. If the instructions have been completed and there are still errors, select the cell that has the error value that is furthest left and topmost first. Look for a typo in a formula or unmatched parentheses. Possibly, a Defined Name is wrong -- they need to be input into the formulas exactly as they were defined. Do Insert Name Define to check. If there is a #DIV/0! error, the example does not, so look for a variable that somehow did not get filled in with a value perhaps. At any rate, what to do is select the cell with the error, and after checking all those typical errors, do Tools Auditing Trace Precedents and/or Trace Error. If fixing all the topmost leftmost errors does not fix the rest of your errors on the worksheet, one may need to do it the hard way, from the bottom right upwards then leftwards; that is the slow but sure way to fix all errors.
  • Also, errors in the chart data will most likely plot as zeros. This may be acceptable or desirable even. However, if too many lines (or curves) are returning to 0, it may indicate a logical flaw in the data -- or too many tiny values and then perhaps rescaling the chart is needed by inspecting the horizontal and vertical axes and changing them to zero in on the problem. Hover over or click on a data marker on the series plot and then do a search in the proper column by value for that value, and identify its precedents.

Sources and Citations

  • The source workbook used for this article was "Power Function Less Derivative.xlsx"

Article Info

Categories: Mathematics | Calculus