Home Application Office information

How to Integrate Large Data Sets in Excel

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

Part 1 of 3:

Preparing

  1. images 1 of How to Integrate Large Data Sets in Excel
    Images 1 of How to Integrate Large Data Sets in Excel
    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. images 2 of How to Integrate Large Data Sets in Excel
    Images 2 of How to Integrate Large Data Sets in Excel
    Load the data into Excel. You can do this by double-clicking on the .xls or .xlsx file that is exported by the machine.
  3. images 3 of How to Integrate Large Data Sets in Excel
    Images 3 of How to Integrate Large Data Sets in Excel
    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.
Part 2 of 3:

Setting Up Your Dimensions

  1. images 4 of How to Integrate Large Data Sets in Excel
    Images 4 of How to Integrate Large Data Sets in Excel
    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. images 5 of How to Integrate Large Data Sets in Excel
    Images 5 of How to Integrate Large Data Sets in Excel
    Click on a blank column and label it "Width". This new column will be used to store the width of each trapezoid.
  3. images 6 of How to Integrate Large Data Sets in Excel
    Images 6 of How to Integrate Large Data Sets in Excel
    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. images 7 of How to Integrate Large Data Sets in Excel
    Images 7 of How to Integrate Large Data Sets in Excel
    Click on the second measurement corresponding to the width, then press the - key.
  5. images 8 of How to Integrate Large Data Sets in Excel
    Images 8 of How to Integrate Large Data Sets in Excel
    images 9 of How to Integrate Large Data Sets in Excel
    Images 9 of How to Integrate Large Data Sets in Excel
    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. images 10 of How to Integrate Large Data Sets in Excel
    Images 10 of How to Integrate Large Data Sets in Excel
    Select the newly created cell and move the mouse cursor to the bottom right corner of the cell directly below, until a cross appears.
    1. Once it appears, left click, hold, and drag the cursor down.
      images 11 of How to Integrate Large Data Sets in Excel
      Images 11 of How to Integrate Large Data Sets in Excel
    2. Stop at the cell directly above the last measurement. Numbers should populate all the selected cells after.
      images 12 of How to Integrate Large Data Sets in Excel
      Images 12 of How to Integrate Large Data Sets in Excel
  7. images 13 of How to Integrate Large Data Sets in Excel
    Images 13 of How to Integrate Large Data Sets in Excel
    Click on a blank column and label it "Height" directly next to the "Width" column.
  8. images 14 of How to Integrate Large Data Sets in Excel
    Images 14 of How to Integrate Large Data Sets in Excel
    Select the column underneath the "Height" label, and type "=0.5*(". Once again, do not exit the cell just yet.
  9. images 15 of How to Integrate Large Data Sets in Excel
    Images 15 of How to Integrate Large Data Sets in Excel
    Click the first measurement in the column corresponding to the height, then press the + key.
  10. images 16 of How to Integrate Large Data Sets in Excel
    Images 16 of How to Integrate Large Data Sets in Excel
    images 17 of How to Integrate Large Data Sets in Excel
    Images 17 of How to Integrate Large Data Sets in Excel
    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. images 18 of How to Integrate Large Data Sets in Excel
    Images 18 of How to Integrate Large Data Sets in Excel
    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.
    1. Once again, stop at the cell before the last measurement. Numbers should appear in all the selected cells.
      images 19 of How to Integrate Large Data Sets in Excel
      Images 19 of How to Integrate Large Data Sets in Excel
Part 3 of 3:

Calculating the Area

  1. images 20 of How to Integrate Large Data Sets in Excel
    Images 20 of How to Integrate Large Data Sets in Excel
    Click on a blank column and label it "Area" next to the "Height" column. This will store the area for each trapezoid.
  2. images 21 of How to Integrate Large Data Sets in Excel
    Images 21 of How to Integrate Large Data Sets in Excel
    Click on the cell directly underneath "Area", and type "=". Once again, do not exit the cell.
  3. images 22 of How to Integrate Large Data Sets in Excel
    Images 22 of How to Integrate Large Data Sets in Excel
    Click on the first cell in the "Width" column, and type an asterisk (*) directly after.
  4. images 23 of How to Integrate Large Data Sets in Excel
    Images 23 of How to Integrate Large Data Sets in Excel
    images 24 of How to Integrate Large Data Sets in Excel
    Images 24 of How to Integrate Large Data Sets in Excel
    Click on the first cell in the "Height" column, and press ↵ Enter. A number should now appear in the cell.
  5. images 25 of How to Integrate Large Data Sets in Excel
    Images 25 of How to Integrate Large Data Sets in Excel
    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.
    1. Once again, stop at the cell before the last measurement. Numbers should appear in the selected cells after this step.
  6. images 26 of How to Integrate Large Data Sets in Excel
    Images 26 of How to Integrate Large Data Sets in Excel
    Click on a blank column and label it "Integral" next to the "Area" column.
  7. images 27 of How to Integrate Large Data Sets in Excel
    Images 27 of How to Integrate Large Data Sets in Excel
    Click on the cell below "Integral", and type in "=SUM(", and do not exit the cell.
  8. images 28 of How to Integrate Large Data Sets in Excel
    Images 28 of How to Integrate Large Data Sets in Excel
    images 29 of How to Integrate Large Data Sets in Excel
    Images 29 of How to Integrate Large Data Sets in Excel
    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.
4.2 ★ | 9 Vote
« PREV : How to Make Tables...
How to Use the Sum... : NEXT »
Read More
Maybe you are interested