Find intersection of two independent ranges and sum values at every coordinate

[AEAA]

Hi All,

I have the definition of several ranges of straight lines: 1, 2, 3, 4 and 5, where I define the x- and the y- coordinates only for the start and end points of the lines. I would like to have a "Desired" range that sums the values in every coordinate of the range. I would like to do this without having to discretize every range to have matching x and y coordinates.

In the chart I provide the desired outcome done by hand, but would like to know if there is a way of doing this in a smarter way using either VBA, PowerQuery or regular excel logic (the latter preferred).

NOTE: If there is an easy way of finding the intersection values when the two lines cross, please let me know. I have found solutions which require user-defined functions in VBA and would prefer avoiding that.

Thank you very much for your help

Overlap of plots.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	x	Bottom	Top
2	-11.15	-5.7E-14	5.68E-14
3	-8.61895	-287.44	1280.386
4	-7.92687	-491.343	2454.368
5	-6.71974	-503.124	3804.976
6	-5.89574	-2779.7	4025.165
7	-4.6473	-2972.49	3236.937
8	-3.44744	-3141.59	2454.369
9	-1.2251	-3141.59	2170.434
10	-0.5	-2563.97	2454.368
11	1.094949	-2487.27	1286.426
12	3.85	-1926.56	2189.084
13	5.165191	-1381.53	2907.325
14	6.479609	-1443.55	3598.47
15	7.35	-569.949	3384.546
16	8.675	-436.288	2844.094
17	10	-302.628	2199.361
18	10	-302.628	1524.097
19	11.50939	-203.128	1017.497
20	13	0	0
21
22	1			Desired (Roughly)
23	x	y		x	y
24	-11.15	0		-11.15	0
25	-11.15	1571		-11.15	1571
26	-8.61895	1571		-10.0579	1571
27	-8.61895	0		-9.12788	4025
28				-8.61895	4025
29	2			-8.61894	2454
30	x	y		-8.21974	2454
31	-10.0579	0		-7.52974	4025
32	-9.12788	2454		-6.71974	4025
33	-8.61895	2454		-6.71974	0
34	-8.61895	0
35
36	3
37	x	y
38	-8.61894	0
39	-8.61894	2454
40	-6.71974	2454
41	-6.71974	0
42
43	4
44	x	y
45	-8.21974	0
46	-7.52974	785
47	-6.71974	785
48	-6.71974	0
49
50	5
51	x	y
52	-8.21974	0
53	-7.52974	785
54	-6.71974	785
55	-6.71974	0
Sheet1

 

[BSALV]

i saw the following questions here on the site, but i was wondering what are you trying to do, what are you trying to prove ?
You add lines and you want to calculate the intersection and do something with it ?
Can you explain what lines you are adding and why ?

 

[BSALV]

intersection of 2 lines

Map1
	A	B	C	D
1	x1	y1	x2	y2
2	-11,15	1.571,00	-10,06	0,00
3	-8,62	1.571,00	-9,13	2.454,00
4	slope	0,00		2.638,71
5	intercept	1.571,00		26.545,42
6
7			x	y
8	UDF --------->		-9,46	1.571,00
9	Formula ---->		-9,46	1.571,00
Blad1

Cell Formulas
Range	Formula
B4,D4	B4	=SLOPE(B$2:B$3,A$2:A$3)
B5,D5	B5	=INTERCEPT(B$2:B$3,A$2:A$3)
C8:D8	C8	=Intersection_2Lines(B4,B5,D4,D5)
C9	C9	=(D5-B5)/(B4-D4)
D9	D9	=+C9*B4+B5
Dynamic array formulas.

Function Intersection_2Lines(Slope1, Intercept1, Slope2, Intercept2)
     x = (Intercept2 - Intercept1) / (Slope1 - Slope2)
     y = Slope1 * x + Intercept1
     Intersection_2Lines = Array(x, y)
End Function

 

[AEAA]

i saw the following questions here on the site, but i was wondering what are you trying to do, what are you trying to prove ?
You add lines and you want to calculate the intersection and do something with it ?
Can you explain what lines you are adding and why ?

Thank you for the reply BSALV.

I have a set of "functions" composed of straight lines with different slopes and I want to add them all up to create my desired red chart. The only way of doing so is to have one x-coordinate range that includes all functions and the y-value of each function at every location. The solution I managed to come up with was to create one stacked column of all x-values and then finding the y-corresponding value by using linear interpolation of each function.

The formula and VBA function that you attached works if the SLOPE and INTERCEPT are part of the same equation, but if I have a piece-wise defined function it does not work.

 

[BSALV]

try me !
What are those red, blue and green lines in #1 ?
Compaired to you data, how do you make them ?

PS. Can you give data with the XL2BB-tool but
X = integer(actual x *100) and Y=integer(actual y)
Normally that has no influence on the result.
(problems with the translation of the comma and the point in the XL2BB-tool, not your fault).

 

[AEAA]

try me !
What are those red, blue and green lines in #1 ?
Compaired to you data, how do you make them ?

So basically the Dark blue, light blue, orange, gray and green lines represent functions 1 through 5. These functions are simply x and y coordinate as you see in A31:B34 for function 2 for example. So I have plotted all these basic lines defined by 3 straight lines with different slopes.

And then I have another function for example Function 1:

If you overlap these 2 functions you have the Red "Desired" function, which is the sum of these 2:

You can see that at coordinate -9.12788 my result is 4025 which is 1571 (Function 1 is straight from -11.15 to -8.61895) + 2454 (Function 2 value at -9.12788).

In order to plot this red lines I need to have therefore all the key points. The interpolation would be useful since I can ask what is the value for Function 1 at x = -9.12788 (undefined) and sum all values at this coordinate.

Does that make sense? Thanks.

 

[KRice]

See if this does what you want. Given the initial listing in A:B, I would consider using Power Query to transform the data into the table shown in columns D:F (let me know if you need help with that). Then aggregate the x data across all curves and sort it to create a list of x points where at least one of the curves has data (column H). Create table headings for an Initial Helper table that correspond to curve numbers (I24:M24). In this helper table, we examine whether two y-values exist for each x value for each curve. Where two values exist (e.g. a vertical line, so (x,0) and (x,y) pairs are shown), the maximum y value is placed in the helper table. Where two values do not exist for each x value, that represents a known data point, and that y value is also placed in the table. Where we have blanks between y values in the helper table, we know we need to interpolate...that's what the Final Helper table does. It looks forward and backward to determine whether a y value can be found in both directions (meaning that the cell is sandwiched between a pair of known points, and a linear interpolation is performed. This fills in the missing blanks, and sums across the rows of the Final Helper table produce the Results list (column V)...I think this returns what you want, except for possibly the end points, which can be added with separate formulas.

aeaa_20220414.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
22	Given:			PQ to create this list				Initial Helper							Final Helper							Results
23	1
24	x	y		x	y	Curve		x	1	2	3	4	5		x	1	2	3	4	5
25	-11.15	0		-11.15	0	1		-11.15	1571						-11.15	1571						1571
26	-11.15	1571		-11.15	1571	1		-10.0579		0					-10.0579	1571	0					1571
27	-8.618945	1571		-8.618945	1571	1		-9.12788		2454					-9.12788	1571	2454					4025
28	-8.618945	0		-8.618945	0	1		-8.61895	1571	2454					-8.61895	1571	2454					4025
29				-10.05788	0	2		-8.61894			2454				-8.61894			2454				2454
30	2			-9.127881	2454	2		-8.21974				0	0		-8.21974			2454	0	0		2454
31	x	y		-8.618945	2454	2		-7.52974				785	785		-7.52974			2454	785	785		4024
32	-10.05788	0		-8.618945	0	2		-6.71974			2454	785	785		-6.71974			2454	785	785		4024
33	-9.127881	2454		-8.618944	0	3
34	-8.618945	2454		-8.618944	2454	3
35	-8.618945	0		-6.719738	2454	3
36				-6.719738	0	3
37	3			-8.219738	0	4
38	x	y		-7.529738	785	4
39	-8.618944	0		-6.719738	785	4
40	-8.618944	2454		-6.719738	0	4
41	-6.719738	2454		-8.219738	0	5
42	-6.719738	0		-7.529738	785	5
43				-6.719738	785	5
44	4			-6.719738	0	5
45	x	y
46	-8.219738	0
47	-7.529738	785
48	-6.719738	785
49	-6.719738	0
50
51	5
52	x	y
53	-8.219738	0
54	-7.529738	785
55	-6.719738	785
56	-6.719738	0
Sheet2

Cell Formulas
Range	Formula
I24:M24,P24:T24	I24	=TRANSPOSE(SORT(UNIQUE($F$25:$F$44)))
H25:H32,O25:O32	H25	=SORT(UNIQUE($D$25:$D$44))
I25:M32	I25	=LET(fwd,INDEX($E$25:$E$44,XMATCH(1,($D$25:$D$44=$H25)*($F$25:$F$44=I$24),0,1)),rev,INDEX($E$25:$E$44,XMATCH(1,($D$25:$D$44=$H25)*($F$25:$F$44=I$24),0,-1)),IF(ISNUMBER(fwd),IF(fwd=rev,fwd,MAX(fwd,rev)),""))
P25:T31	P25	=LET(fwdx,INDEX($H26:$H$32,XMATCH(1,--(I26:I$32<>""),0,1)),revx,INDEX($H$25:$H25,XMATCH(1,--(I$25:I25<>""),0,-1)),fwdy,INDEX(I26:I$32,XMATCH(1,--(I26:I$32<>""),0,1)),revy,INDEX(I$25:I25,XMATCH(1,--(I$25:I25<>""),0,-1)),m,(revy-fwdy)/(revx-fwdx),b,fwdy-m*fwdx,IF(ISNUMBER(I25),I25,IF(AND(ISNUMBER(fwdy),ISNUMBER(revy)),m*$H26+b,"")))
P32:T32	P32	=LET(fwdx,INDEX($H$32:$H33,XMATCH(1,--(I$32:I33<>""),0,1)),revx,INDEX($H$25:$H32,XMATCH(1,--(I$25:I32<>""),0,-1)),fwdy,INDEX(I$32:I33,XMATCH(1,--(I$32:I33<>""),0,1)),revy,INDEX(I$25:I32,XMATCH(1,--(I$25:I32<>""),0,-1)),m,(revy-fwdy)/(revx-fwdx),b,fwdy-m*fwdx,IF(ISNUMBER(I32),I32,IF(AND(ISNUMBER(fwdy),ISNUMBER(revy)),m*$H33+b,"")))
V25:V32	V25	=SUM(P25:T25)
Dynamic array formulas.

 

[AEAA]

See if this does what you want. Given the initial listing in A:B, I would consider using Power Query to transform the data into the table shown in columns D:F (let me know if you need help with that). Then aggregate the x data across all curves and sort it to create a list of x points where at least one of the curves has data (column H). Create table headings for an Initial Helper table that correspond to curve numbers (I24:M24). In this helper table, we examine whether two y-values exist for each x value for each curve. Where two values exist (e.g. a vertical line, so (x,0) and (x,y) pairs are shown), the maximum y value is placed in the helper table. Where two values do not exist for each x value, that represents a known data point, and that y value is also placed in the table. Where we have blanks between y values in the helper table, we know we need to interpolate...that's what the Final Helper table does. It looks forward and backward to determine whether a y value can be found in both directions (meaning that the cell is sandwiched between a pair of known points, and a linear interpolation is performed. This fills in the missing blanks, and sums across the rows of the Final Helper table produce the Results list (column V)...I think this returns what you want, except for possibly the end points, which can be added with separate formulas.

aeaa_20220414.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
22	Given:			PQ to create this list				Initial Helper							Final Helper							Results
23	1
24	x	y		x	y	Curve		x	1	2	3	4	5		x	1	2	3	4	5
25	-11.15	0		-11.15	0	1		-11.15	1571						-11.15	1571						1571
26	-11.15	1571		-11.15	1571	1		-10.0579		0					-10.0579	1571	0					1571
27	-8.618945	1571		-8.618945	1571	1		-9.12788		2454					-9.12788	1571	2454					4025
28	-8.618945	0		-8.618945	0	1		-8.61895	1571	2454					-8.61895	1571	2454					4025
29				-10.05788	0	2		-8.61894			2454				-8.61894			2454				2454
30	2			-9.127881	2454	2		-8.21974				0	0		-8.21974			2454	0	0		2454
31	x	y		-8.618945	2454	2		-7.52974				785	785		-7.52974			2454	785	785		4024
32	-10.05788	0		-8.618945	0	2		-6.71974			2454	785	785		-6.71974			2454	785	785		4024
33	-9.127881	2454		-8.618944	0	3
34	-8.618945	2454		-8.618944	2454	3
35	-8.618945	0		-6.719738	2454	3
36				-6.719738	0	3
37	3			-8.219738	0	4
38	x	y		-7.529738	785	4
39	-8.618944	0		-6.719738	785	4
40	-8.618944	2454		-6.719738	0	4
41	-6.719738	2454		-8.219738	0	5
42	-6.719738	0		-7.529738	785	5
43				-6.719738	785	5
44	4			-6.719738	0	5
45	x	y
46	-8.219738	0
47	-7.529738	785
48	-6.719738	785
49	-6.719738	0
50
51	5
52	x	y
53	-8.219738	0
54	-7.529738	785
55	-6.719738	785
56	-6.719738	0
Sheet2

Cell Formulas
Range	Formula
I24:M24,P24:T24	I24	=TRANSPOSE(SORT(UNIQUE($F$25:$F$44)))
H25:H32,O25:O32	H25	=SORT(UNIQUE($D$25:$D$44))
I25:M32	I25	=LET(fwd,INDEX($E$25:$E$44,XMATCH(1,($D$25:$D$44=$H25)*($F$25:$F$44=I$24),0,1)),rev,INDEX($E$25:$E$44,XMATCH(1,($D$25:$D$44=$H25)*($F$25:$F$44=I$24),0,-1)),IF(ISNUMBER(fwd),IF(fwd=rev,fwd,MAX(fwd,rev)),""))
P25:T31	P25	=LET(fwdx,INDEX($H26:$H$32,XMATCH(1,--(I26:I$32<>""),0,1)),revx,INDEX($H$25:$H25,XMATCH(1,--(I$25:I25<>""),0,-1)),fwdy,INDEX(I26:I$32,XMATCH(1,--(I26:I$32<>""),0,1)),revy,INDEX(I$25:I25,XMATCH(1,--(I$25:I25<>""),0,-1)),m,(revy-fwdy)/(revx-fwdx),b,fwdy-m*fwdx,IF(ISNUMBER(I25),I25,IF(AND(ISNUMBER(fwdy),ISNUMBER(revy)),m*$H26+b,"")))
P32:T32	P32	=LET(fwdx,INDEX($H$32:$H33,XMATCH(1,--(I$32:I33<>""),0,1)),revx,INDEX($H$25:$H32,XMATCH(1,--(I$25:I32<>""),0,-1)),fwdy,INDEX(I$32:I33,XMATCH(1,--(I$32:I33<>""),0,1)),revy,INDEX(I$25:I32,XMATCH(1,--(I$25:I32<>""),0,-1)),m,(revy-fwdy)/(revx-fwdx),b,fwdy-m*fwdx,IF(ISNUMBER(I32),I32,IF(AND(ISNUMBER(fwdy),ISNUMBER(revy)),m*$H33+b,"")))
V25:V32	V25	=SUM(P25:T25)
Dynamic array formulas.

Thanks KRice ! This is sort of what I was producing myself, and it definitely gets the final result I was expecting. Also very little use of PQ which is good as I wanted to keep this simple.

 

[KRice]

Glad to help. PQ is probably the easiest way to transform your source data into single flat columns. Do your component curves always consist of four points? And is there always a blank row between each group? If there is a consistent pattern, you could probably do the transformation with some formulas. About how many component curves are you dealing with in a typical application?

 

[KRice]

Here is one idea for transforming your initial set of data into a flat table with formulas, assuming the same structure is maintained throughout:

aeaa_20220414.xlsx
	D	E	F
47	x	y	Curve
48	-11.15	0	1
49	-11.15	1571	1
50	-8.61895	1571	1
51	-8.61895	0	1
52	-10.0579	0	2
53	-9.12788	2454	2
54	-8.61895	2454	2
55	-8.61895	0	2
56	-8.61894	0	3
57	-8.61894	2454	3
58	-6.71974	2454	3
59	-6.71974	0	3
60	-8.21974	0	4
61	-7.52974	785	4
62	-6.71974	785	4
63	-6.71974	0	4
64	-8.21974	0	5
65	-7.52974	785	5
66	-6.71974	785	5
67	-6.71974	0	5
Sheet2

Cell Formulas
Range	Formula
D48:D67	D48	=FILTER($A$23:$A$56,MOD(SEQUENCE(ROWS($A$23:$A$56)),7)>=3)
E48:E67	E48	=FILTER($B$23:$B$56,MOD(SEQUENCE(ROWS($A$23:$A$56)),7)>=3)
F48:F67	F48	=QUOTIENT(ROWS(F$48:F48)-1,4)+1
Dynamic array formulas.