How to calculate definite integral in Excel

Let’s see how to calculate definite integral table of the given function using the Excel program in Microsoft Office.

You will need

• — a computer with installed MS Excel;
• — specified tabular function.

Manual

1. Suppose we have a table set some values. For example, let it be the cumulative radiation dose during the flight. For example, there was such an experiment: a man with a dosimeter was on a plane from point A to point B and periodically measured dosimeter dose rate (measured in microsieverts per hour). You probably are surprised, but in the normal plane man receives a dose of radiation 10 times more than the background level. But this effect is transient and therefore not dangerous. From the measurements we have table like this format: Time — dose.

2. The essence of this method is that the definite integral is the area under the graph of the desired size. In our example, if the flight lasted almost 2 hours, from 17:30 to 19:27 (see the figure), to find the accumulated dose, it is necessary to determine the area of the shape under the graph of dose — schedule table predetermined value.

3. To calculate the integral we will be the most simple but quite accurate method is the trapezoid method. Let me remind you, every curve can be divided into trapezoid. The sum of the areas of these trapezoids and will desired integral.
Area of a trapezoid is defined simply by the sum of the bases multiplied by the height. The reason in our case is a table of measured values of dose rate for 2 consecutive time interval, and the height is the difference of time between two measurements.

4. In our example of measuring the dose of radiation is given in µsv/h. Translate this into mSv/min, since the data are given with a frequency of 1 per minute. It is necessary to harmonize units of measure. We can’t take the integral over time measured in minutes from the value measured in hours.
To translate just split the dose rate in µsv/h by line 60. Add another column to our table. The illustration in the column «D» in line 2, enter «=C2/60». And then use the fill handle (drag with the mouse the black rectangle in the lower right corner of the cell) extend this formula to all other cells in column «D».

5. Now we need to find the areas of trapezoids for each time period. In column «E» will be calculated by the above formula for the area of trapezoids.
The half-sum of bases is half the sum of the two consecutive dose rate from column «D». Since the data are from a period of 1 time per minute, and we take the integral time, expressed in minutes, the height of each trapezium will be equal to one (time difference between every two sequential measurements, for example, 17ч31м — 17ч30м = 0ч1м).
Get a formula in cell E3: «=1/2*(D2+D3)*1». It is clear that «*1» can not write, I did it just for completeness. The picture explains everything more clearly.
Similarly, using the fill handle distributed by formula to the entire column. Now each cell in column «E» calculated the cumulative dose over 1 minute of flight.

6. It remains to find the sum of the calculated squares of the trapezoid. In cell «F2» to write the formula «=SUM(E:E)», and this will desired integral is the sum of all values in column «E».
You can do a little bit more difficult to determine the accumulated dose at different points of the flight. To do this, in cell «F4» will inscribe the formula «=SUM(E\$3:E4)» and fill handle will extend the entire column «F». The symbol «E\$3» tells Excel what to change the index of the first cell, from which count, not need.
Create the graph by columns «F» and «A», i.e., the change in dose of radiation over time. Clearly shows an increase in the integral, as it should be, and the final accumulated value for a two-hour flight radiation dose equal to about 4.5 microsievert.
So we just found the definite integral of the given function table in Excel to a real physical example.

Filed in: Tip