How To Calculate A Definite Integral To Excel | The Science



The Science

How to calculate a definite integral to Excel

Let's see how to calculate the definite integral of a given function in tabular form using Excel program from the Microsoft Office.

How to calculate a definite integral to Excel

You will need:

- computer with MS Excel application; - Tabulated function.

Instruction how to calculate the definite integral in excel

Step 1:

For example, we have given a certain quantity of table. For example, let it be the cumulative dose of radiation during air travel. For example, there was such an experiment: a man with a dosimeter flew by plane from point A to point B, and periodically measured dose rate dosimeter (measured in microsieverts per hour). You may also be surprised by it, but in the normal plane flight to a person receives radiation dose 10 times higher than the background level. But the impact is short-term and therefore not dangerous. According to the results of measurements we have a table here in this format: Time - dose rate.

The table-set value

Step 2:

The essence of the method is that the definite integral - is the area under the graph of a desired magnitude of us. In our example, if the flight lasted almost 2 hours, from 17:30 to 19:27 (. See picture), then to find the accumulated dose, it is necessary to determine the area of ​​the figure below the graph dose - schedule table-set value.

The definite integral - the area under the figure

Step 3:

To calculate the integral we are the most simple, but pretty accurate method - the method of trapezoids. Let me remind you, each curve can be divided into a trapezoid. The sum of the areas of these trapezoids is the desired integral. The area of ​​a trapezoid is defined simply: half the sum of the bases multiplied by the height. The grounds in this case - it is the power table for the measured dose of 2 consecutive period of time, and the height - it is a time difference between the two measurements.

Calculating the area of ​​a trapezoid

Step 4:

In our radiation dose rate measurement example given in mSv / h. Translate it in Sv / min as data are given with a frequency of 1 per minute. It is necessary to harmonize the units. We can not take the time integral, measured in minutes, from the value measured in hours. To put simply divide the dose rate in mSv / h in detail at 60. Let's add one more column in our table. The illustration in the column "D" in row 2 is entered "C2 = / 60". And then using the fill handle (the mouse drag the black rectangle in the lower right corner of the cell) extend this formula to all other cells in the column "D".

Translation units

Step 5:

Now you need to find the area of ​​a trapezoid for each time interval. column "E" We will be calculated according to the formula given above the area of ​​trapezoids. Half the sum of the bases - it is half the sum of two consecutive dose rates from column "D". Since the data are from the period of 1 every minute, and we take the integral over time, expressed in minutes, the height of each trapezoid is equal to one (time difference between any two consecutive measurements, for example, 17ch31m - 17ch30m = 0ch1m). We get the formula in cell "E3": "= 1/2 * (D2 + D3) * 1". It is understood that "*1" You can not write, I did it just for the sake of completeness. Figure explains everything more clearly. Similarly, using the fill handle, extend the formula to the entire column. Now for each column of the cell "E" counted accumulated dose in 1 minute flight.

Calculating the area of ​​a trapezoid

Step 6:

It remains to find the sum of the calculated area of ​​the trapezoid. It can be in a cell "F2" write the formula "= SUM (E: E)"It is the desired integral - the sum of all values ​​in a column "E". It can be a little more difficult to determine the cumulative dose at different moments of the flight. For this purpose, the cell "F4" inscribe the formula: "= SUM (E $ 3: E4)" and the fill handle to extend the entire column "F". designation "E $ 3" tells Excel program that change the index of the first cell from which are conducting the account, it is not necessary. We construct a graph in columns "F" and "A"Ie change in the cumulative dose of radiation over time. It is clearly seen an increase in the integral, as it should be, and the final value of the accumulated over a two-hour flight radiation dose equal to about 4.5 microsieverts. So, we just found a definite integral of the function given in tabular form in Excel on a real physical example.

The calculation of the integral