How to Integrate Large Data Sets in Excel

Step-by-Step Guide

1. 1

   Step 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

   Step 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. , 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. , 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. , This new column will be used to store the width of each trapezoid. , Type it exactly as shown, and do not stop typing in the cell yet.
   
   Note that the "typing" cursor is still flashing. ,, The cell should now have a number in it. , 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. ,, Once again, do not exit the cell just yet. ,, The cell should have a number in it. , 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. , This will store the area for each trapezoid. , Once again, do not exit the cell. ,, A number should now appear in the 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. ,,, A number under "Integral" should appear, and will be the answer.
3. 3

   Step 3: Convert the measurements into a usable form

4. 4

   Step 4: if needed.

5. 5

   Step 5: Determine which columns will represent the width and height of the trapezoid.

6. 6

   Step 6: Click on a blank column and label it "Width".

7. 7

   Step 7: Select the empty cell below "Width" and type "=ABS(".

8. 8

   Step 8: Click on the second measurement corresponding to the width

9. 9

   Step 9: then press the - key.

10. 10

    Step 10: Click on the first measurement in the same column

11. 11

    Step 11: and type in the closing parenthesis

12. 12

    Step 12: and press ↵ Enter.

13. 13

    Step 13: Select the newly created cell and move the mouse cursor to the bottom right corner of the cell directly below

14. 14

    Step 14: until a cross appears.

15. 15

    Step 15: Click on a blank column and label it "Height" directly next to the "Width" column.

16. 16

    Step 16: Select the column underneath the "Height" label

17. 17

    Step 17: and type "=0.5*(".

18. 18

    Step 18: Click the first measurement in the column corresponding to the height

19. 19

    Step 19: then press the + key.

20. 20

    Step 20: Click on the second measurement in the same column

21. 21

    Step 21: and type the closing parenthesis

22. 22

    Step 22: and press ↵ Enter.

23. 23

    Step 23: Click on the newly created cell.

24. 24

    Step 24: Click on a blank column and label it "Area" next to the "Height" column.

25. 25

    Step 25: Click on the cell directly underneath "Area"

26. 26

    Step 26: and type "=".

27. 27

    Step 27: Click on the first cell in the "Width" column

28. 28

    Step 28: and type an asterisk (*) directly after.

29. 29

    Step 29: Click on the first cell in the "Height" column

30. 30

    Step 30: and press ↵ Enter.

31. 31

    Step 31: Click on the newly created cell.

32. 32

    Step 32: Click on a blank column and label it "Integral" next to the "Area" column.

33. 33

    Step 33: Click on the cell below "Integral"

34. 34

    Step 34: and type in "=SUM("

35. 35

    Step 35: and do not exit the cell.

36. 36

    Step 36: Click on the first cell under "Area"

37. 37

    Step 37: and drag downwards until all the cells in the "Area" column are selected

38. 38

    Step 38: then press ↵ Enter.

Detailed Guide

About the Author