How to Integrate Large Data Sets in Excel

PDF download Download Article
PDF download Download Article

These instructions will show you how to approximate integrals for large data sets in Microsoft Excel. This can be particularly useful when analyzing data from machinery or equipment that takes a large number of measurements—for example, in this instruction set, data from a tensile testing machine is used. This guide can be applied to any type of measurement data that can be integrated.

Part 1
Part 1 of 3:

Preparing

PDF download Download Article
  1. 1
    Understand the basics of the trapezoid rule. This is how the integral will be approximated. Imagine the stress-strain curve above, but separated into hundreds of trapezoidal sections. Each section's area will be added to find the area under the curve.
  2. 2
    Load the data into Excel. You can do this by double-clicking on the .xls or .xlsx file that is exported by the machine.
    Advertisement
  3. 3
    Convert the measurements into a usable form, if needed. For this particular data set, it means converting the tensile machine measurements from "Travel" to "Strain", and "Load" to "Stress", respectively. This step may require different calculations or may not be needed at all, depending on the data from your machine.
    4. Advertisement
Part 2
Part 2 of 3:

Setting Up Your Dimensions

PDF download Download Article
  1. 1
    Determine which columns will represent the width and height of the trapezoid. Once again, this will be determined by the nature of your data. For this set, "Strain" corresponds to the width and "Stress" corresponds to the height.
  2. 2
    Click on a blank column and label it "Width". This new column will be used to store the width of each trapezoid.
  3. 3
    Select the empty cell below "Width" and type "=ABS(". Type it exactly as shown, and do not stop typing in the cell yet. Note that the "typing" cursor is still flashing.
  4. 4
    Click on the second measurement corresponding to the width, then press the - key.
  5. 5
    Click on the first measurement in the same column, and type in the closing parenthesis, and press ↵ Enter . The cell should now have a number in it.
  6. 6
    Select the newly created cell and move the mouse cursor to the bottom right corner of the cell directly below, until a cross appears.
    • Once it appears, left click, hold, and drag the cursor down .
    • Stop at the cell directly above the last measurement. Numbers should populate all the selected cells after.
  7. 7
    Click on a blank column and label it "Height" directly next to the "Width" column.
  8. 8
    Select the column underneath the "Height" label, and type "=0.5*(". Once again, do not exit the cell just yet.
  9. 9
    Click the first measurement in the column corresponding to the height, then press the + key.
  10. 10
    Click on the second measurement in the same column, and type the closing parenthesis, and press ↵ Enter . The cell should have a number in it.
  11. 11
    Click on the newly created cell. Repeat the same procedure you did before to apply the formula to all the cells in the same column.
    • Once again, stop at the cell before the last measurement. Numbers should appear in all the selected cells.
    12. Advertisement
Part 3
Part 3 of 3:

Calculating the Area

PDF download Download Article
  1. 1
    Click on a blank column and label it "Area" next to the "Height" column. This will store the area for each trapezoid.
  2. 2
    Click on the cell directly underneath "Area", and type "=". Once again, do not exit the cell.
  3. 3
    Click on the first cell in the "Width" column, and type an asterisk (*) directly after.
  4. 4
    Click on the first cell in the "Height" column, and press ↵ Enter . A number should now appear in the cell.
  5. 5
    Click on the newly created cell. Repeat the procedure you applied before again to apply the formula to all the cells in the same column.
    • Once again, stop at the cell before the last measurement. Numbers should appear in the selected cells after this step.
  6. 6
    Click on a blank column and label it "Integral" next to the "Area" column.
  7. 7
    Click on the cell below "Integral", and type in "=SUM(", and do not exit the cell.
  8. 8
    Click on the first cell under "Area", hold, and drag downwards until all the cells in the "Area" column are selected, then press ↵ Enter . A number under "Integral" should appear, and will be the answer.
    9. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      Warnings

      • If no numbers or an error appears in the cell, double-check to make sure the correct cells have been selected.
        Thanks
        Helpful 0 Not Helpful 0
      • Make sure you put proper units on the integration answer.
        Thanks
        Helpful 0 Not Helpful 0
      • Keep in mind that this is an approximation, and will be more accurate if there are more trapezoids (more measurements).
        Thanks
        Helpful 0 Not Helpful 0


      Advertisement

      Things You'll Need

      • Microsoft Excel (any version)
      • .XLS or .XLSX file containing data



      You Might Also Like

      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Unprotect an Excel Sheet
      The Best Ways to Make & Format Lists in Excel
      How to
      Make a Spreadsheet in Excel
      How to
      Create a Chart in Microsoft Excel: Step-by-Step Guide
      How to
      Create a Pie Chart in Microsoft Excel
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Use Macros in Excel
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      New to Excel? Here's Super Easy Tricks to Get You Started
      Microsoft Excel: 2 Ways to Open a Password Protected Spreadsheet
      Advertisement

      About This Article

      Thanks to all authors for creating a page that has been read 98,186 times.

      Is this article up to date?

      Advertisement