How to calculate an area on an Excel graph
julien
-
amine -
amine -
Hello everyone,
I need to calculate the area under a curve created in Excel, which mathematically involves calculating the integration of the curve. The problem is that this graph has too much variation to display a trendline and its equation.
What's the trick?
Does Excel do it?
I wish you a good day and thank you in advance,
Julien.
I need to calculate the area under a curve created in Excel, which mathematically involves calculating the integration of the curve. The problem is that this graph has too much variation to display a trendline and its equation.
What's the trick?
Does Excel do it?
I wish you a good day and thank you in advance,
Julien.
1 réponse
THIS IS JUST COPY PASTE!
Suppose you want to find the area under the curve of
y = x^2 for x varying from 1 to 2 and your data starts
in A2:
x x^2
1 1 *
1.1 1.21
1.2 1.44
1.3 1.69
1.4 1.96
1.5 2.25
1.6 2.56
1.7 2.89
1.8 3.24
1.9 3.61 **
2 4
Then, you place yourself in C2 (*) and enter the following formula:
=IF(B2*B3>=0;ABS(((B2+B3)/2)*(A3-A2));
ABS(((B2^2+B3^2)/(B2-B3)/2)*(A3-A2)))
which you copy down to C11 (**).
You then sum from C2 to C11.
This way, you obtain an approximation of the actual area
whose precision depends on the number of values
between 1 and 2.
In this case, the actual area is 2 and 1/3 and the
formula gives 2.335 (not bad, right?)
This formula that I finally understood is by
John Walkenbach.
This formula allows you to calculate the area of the
region bounded by the curve and the X-axis. It works
even if the curve crosses the X-axis.
Since then, I've found two others that calculate the area
of the region bounded by a curve and the X-axis if the curve
does not cross the X-axis.
Suppose you want to find the area under the curve of
y = x^2 for x varying from 1 to 2 and your data starts
in A2:
x x^2
1 1 *
1.1 1.21
1.2 1.44
1.3 1.69
1.4 1.96
1.5 2.25
1.6 2.56
1.7 2.89
1.8 3.24
1.9 3.61 **
2 4
Then, you place yourself in C2 (*) and enter the following formula:
=IF(B2*B3>=0;ABS(((B2+B3)/2)*(A3-A2));
ABS(((B2^2+B3^2)/(B2-B3)/2)*(A3-A2)))
which you copy down to C11 (**).
You then sum from C2 to C11.
This way, you obtain an approximation of the actual area
whose precision depends on the number of values
between 1 and 2.
In this case, the actual area is 2 and 1/3 and the
formula gives 2.335 (not bad, right?)
This formula that I finally understood is by
John Walkenbach.
This formula allows you to calculate the area of the
region bounded by the curve and the X-axis. It works
even if the curve crosses the X-axis.
Since then, I've found two others that calculate the area
of the region bounded by a curve and the X-axis if the curve
does not cross the X-axis.
Sorry, can you explain to me how you arrived at the final result (2.335), because I haven't found the same thing.
Thank you.