Calculate Area Between 2 Lines on Excel

[Sc0]

Hey, I am not sure if this is the right place to ask but I cannot find anything that works for me so far. I have 2 lines on a chart in excel and the raw data for the 2 lines. The data is based on the X axis being the date and the Y axis being the price. There are 2 Y axis lines with my price for an item, and my sales price. I am tracking this over the past year for example and want to calculate the area between the 2 lines when I was selling below cost and selling above cost.

What is the easiest way to get this number?

Thanks,

 

[KRice]

Did you fit lines to your data? Do you know the slopes and intercepts of the lines? If not, you can extract them with SLOPE and INTERCEPT. Then we know the equation of a line is y=mx+b and the area between the line and the x-axis is the integral of y with respect to x, or area = 0.5 * m * x^2 + b * x, evaluated between the x limits of interest. An example is shown below, two curves are shown, one for actual (A), the other for selling (S) price. They cross and we might be interested in knowing the area between the curves to the left of the intersection point (or somewhere close to it)...I've called that Section 1. We might also want the area to the right of this point...I've called that Section 2. So looking at the plot or the data table, the lower and upper dates for each section are input (G5:H6). The SLOPE and INTERCEPT formulas are used to extract slopes and intercepts for the two lines based on a linear regression of the data (see B2:C3). Then the areas between each line section and the x-axis are computed (A5:C6), and then to get the area between the two lines, the area associated with one line is subtracted from the area associated with the other. I've taken "positive" area to mean S>A, while negative area means A>S...see D5:E6.

MrExcel_20220805.xlsx
	A	B	C	D	E	F	G	H
1		A	S
2	slope	0.10703862	-0.084177
3	intercept	-4671.6124	3874.839
4		Area	Area				lower date	upper date
5	section 1	16054.9486	17726.298	1671.3494	($-days)		1/1/2022	6/1/2022
6	section 2	14754.0621	12920.112	-1833.95	($-days)		6/1/2022	10/1/2022
7
8	Date	Actual Cost	Selling Price
9	1/1/2022	100	120
10	2/1/2022	102	120
11	3/1/2022	104	120
12	4/1/2022	106	118
13	5/1/2022	110	116
14	6/1/2022	114	113
15	7/1/2022	118	110
16	8/1/2022	122	106
17	9/1/2022	124	102
18	10/1/2022	128	98
Sheet3

Cell Formulas
Range	Formula
B2:C2	B2	=SLOPE(B9:B18,$A9:$A18)
B3:C3	B3	=INTERCEPT(B9:B18,$A9:$A18)
B5:C5	B5	=(0.5*B$2*$H$5^2+B$3*$H$5)-(0.5*B$2*$G$5^2+B$3*$G$5)
D5:D6	D5	=C5-B5
B6:C6	B6	=(0.5*B$2*$H$6^2+B$3*$H$6)-(0.5*B$2*$G$6^2+B$3*$G$6)

 

[Sc0]

Did you fit lines to your data? Do you know the slopes and intercepts of the lines? If not, you can extract them with SLOPE and INTERCEPT. Then we know the equation of a line is y=mx+b and the area between the line and the x-axis is the integral of y with respect to x, or area = 0.5 * m * x^2 + b * x, evaluated between the x limits of interest. An example is shown below, two curves are shown, one for actual (A), the other for selling (S) price. They cross and we might be interested in knowing the area between the curves to the left of the intersection point (or somewhere close to it)...I've called that Section 1. We might also want the area to the right of this point...I've called that Section 2. So looking at the plot or the data table, the lower and upper dates for each section are input (G5:H6). The SLOPE and INTERCEPT formulas are used to extract slopes and intercepts for the two lines based on a linear regression of the data (see B2:C3). Then the areas between each line section and the x-axis are computed (A5:C6), and then to get the area between the two lines, the area associated with one line is subtracted from the area associated with the other. I've taken "positive" area to mean S>A, while negative area means A>S...see D5:E6.

MrExcel_20220805.xlsx
	A	B	C	D	E	F	G	H
1		A	S
2	slope	0.10703862	-0.084177
3	intercept	-4671.6124	3874.839
4		Area	Area				lower date	upper date
5	section 1	16054.9486	17726.298	1671.3494	($-days)		1/1/2022	6/1/2022
6	section 2	14754.0621	12920.112	-1833.95	($-days)		6/1/2022	10/1/2022
7
8	Date	Actual Cost	Selling Price
9	1/1/2022	100	120
10	2/1/2022	102	120
11	3/1/2022	104	120
12	4/1/2022	106	118
13	5/1/2022	110	116
14	6/1/2022	114	113
15	7/1/2022	118	110
16	8/1/2022	122	106
17	9/1/2022	124	102
18	10/1/2022	128	98
Sheet3

Cell Formulas
Range	Formula
B2:C2	B2	=SLOPE(B9:B18,$A9:$A18)
B3:C3	B3	=INTERCEPT(B9:B18,$A9:$A18)
B5:C5	B5	=(0.5*B$2*$H$5^2+B$3*$H$5)-(0.5*B$2*$G$5^2+B$3*$G$5)
D5:D6	D5	=C5-B5
B6:C6	B6	=(0.5*B$2*$H$6^2+B$3*$H$6)-(0.5*B$2*$G$6^2+B$3*$G$6)

View attachment 70971

Thanks I will try this later this week and let you know how it goes.