Dynamically change a table with formulas

[msampson]

I have a short summary table. I am trying to stretch it out to fill in blanks by interpolating between the existing cells.
I have a variable number of rows needed between each row of the summary table to the extended table. I am trying to make this with formulas so that when I switch to a different summary table it will adjust the number of rows between each and automatically drop down to the next row of the summary table for all the needed formulas across the extended table.
Another way to try to explain it is I'm using $ to fix cell positions in, say, 6 rows, but when I get to the next row in the summary I'll need to increment those $ down one to apply the formulas to the next row down. An then use that $ in, say 8 rows, before switching down again. I've uploaded a minisheet that does what I want to try to show it better.

sample.xlsx
	B	C	D	E	F	G	H	I	J	K
1	*	Col E pulls from D and needs to drop down a row when F switches to the next row in the summary table
2					number of rows needed	*	col B interpolated	col C interpolated	col D interpolated	col E interpolated
3	1	94.6	117	136		6	1	94.6	116.5	136.1
4	2.5	103.3	127	148	6	6	1.25	96.0	118.3	138.1
5	5	108.9	136	159	8	6	1.50	97.5	120.1	140.2
6	10	119.1	146	170	7	6	1.75	99.0	121.8	142.2
7	25	130.6	163	190	11	6	2.00	100.4	123.6	144.2
8	50	146.8	185	213	16	6	2.25	101.9	125.4	146.2
9	75	165.0	209	236	18	8	2.5	103.3	127.2	148.3
10	90	184.8	234	258	20	8	2.81	104.0	128.3	149.7
11	95	201.3	251	273	17	8	3.13	104.7	129.4	151.1
12	97.5	215.7	266	284	14	8	3.44	105.4	130.5	152.4
13	99	227.5	285	301	12	8	3.75	106.1	131.6	153.8
14						8	4.06	106.8	132.7	155.2
15						8	4.38	107.5	133.8	156.6
16						8	4.69	108.2	135.0	158.0
17						7	5	108.9	136.1	159.4
18						7	5.71	110.3	137.5	161.0
19						7	6.43	111.8	138.9	162.5
20						7	7.14	113.3	140.3	164.0
21						7	7.86	114.7	141.8	165.5
22						7	8.57	116.2	143.2	167.0
23						7	9.29	117.6	144.6	168.6
24						11	10	119.1	146.0	170.1
25						11	11.36	120.1	147.6	171.9
26						11	12.73	121.2	149.2	173.7
27						11	14.09	122.2	150.8	175.5
28						11	15.45	123.3	152.3	177.3
Sheet1

Cell Formulas
Range	Formula
G3	G3	=F4
H3:K3	H3	=B3
G4	G4	=F4
H4:H8	H4	=(($B$4-$B$3)/$G3)+H3
I4:K8	I4	=((C$4-C$3)/$G4)+I3
G5	G5	=F4
G6	G6	=F4
G7	G7	=F4
G8:G9	G8	=F4
H9:K9	H9	=B4
G10	G10	=F5
H10:H16	H10	=(($B$5-$B$4)/$G9)+H9
I10:K16	I10	=((C$5-C$4)/$G10)+I9
G11	G11	=F5
G12	G12	=F5
G13	G13	=F5
G14	G14	=F5
G15	G15	=F5
G16:G17	G16	=F5
H17:K17	H17	=B5
G18	G18	=F6
H18:H23	H18	=(($B$6-$B$5)/$G17)+H17
I18:K23	I18	=((C$6-C$5)/$G18)+I17
G19	G19	=F6
G20	G20	=F6
G21	G21	=F6
G22	G22	=F6
G23:G24	G23	=F6
H24:K24	H24	=B6
G25	G25	=F7
H25:H28	H25	=(($B$7-$B$6)/$G24)+H24
I25:K28	I25	=((C$7-C$6)/$G25)+I24
G26	G26	=F7
G27	G27	=F7
G28	G28	=F7
F7:F13	F7	=ROUND(C7-C6,0)

 

[Toadstool]

Hi MSampson,

I've done as you did and used fixed cell references for the first row but subsequent are all calculated.
I've assumed column F is manually entered as you didn't describe how many samples could be calculated.
I've added a new column "Table Group" so I can keep track of which source row to use.
It calculates all other data down to row 9999 but you can change that range if required.

I checked results to your example and all matched.

MSampson.xlsx
	B	C	D	E	F	G	H	I	J	K	L
2					number of rows needed	Table Group	*	col B interpolated	col C interpolated	col D interpolated	col E interpolated
3	1	94.56706786	116.5211679	136.1229154		1	6	1	94.56706786	116.5211679	136.1229154
4	2.5	103.3438736	127.1744494	148.2592302	6	1	6	1.25	96.02986881	118.2967148	138.1456345
5	5	108.8686899	136.0744065	159.4331431	8	1	6	1.5	97.49266976	120.0722617	140.1683536
6	10	119.10105	146.036832	170.0928255	7	1	6	1.75	98.95547072	121.8478087	142.1910728
7	25	130.5684167	163.3527475	189.87717	11	1	6	2	100.4182717	123.6233556	144.2137919
8	50	146.7740196	185.1069233	212.8016906	16	1	6	2.25	101.8810726	125.3989025	146.2365111
9	75	165.0290936	208.8683641	235.5755794	18	2	8	2.5	103.3438736	127.1744494	148.2592302
10	90	184.8077731	234.353515	257.548435	20	2	8	2.8125	104.0344756	128.2869441	149.6559693
11	95	201.3133789	250.6737495	272.6511528	17	2	8	3.125	104.7250776	129.3994387	151.0527084
12	97.5	215.7068058	266.4703994	284.4668238	14	2	8	3.4375	105.4156797	130.5119334	152.4494475
13	99	227.4731512	284.6029525	301.0265374	12	2	8	3.75	106.1062817	131.624428	153.8461867
14						2	8	4.0625	106.7968838	132.7369226	155.2429258
15						2	8	4.375	107.4874858	133.8494173	156.6396649
16						2	8	4.6875	108.1780878	134.9619119	158.036404
17						3	7	5	108.8686899	136.0744065	159.4331431
18						3	7	5.71429	110.3304556	137.4976102	160.9559549
19						3	7	6.42857	111.7922213	138.9208138	162.4787666
Sheet1

Cell Formulas
Range	Formula
H3	H3	=F4
I3:L3	I3	=B3
G4:G19	G4	=IF(ROW()-MATCH(MAX($G$3:G3),$G$3:G3,0)-1>INDEX($F$4:$F$9999,MAX($G$3:G3)),MAX($G$3:G3)+1,G3)
H4:H19	H4	=INDEX($F$4:$F$9999,MAX($G$3:G4))
I4:L19	I4	=IF($G3<>$G4,INDEX(B$3:B$9999,$G4),(((INDEX(B$3:B$9999,$G4+1)-(INDEX(B$3:B$9999,$G4)))/$H4))+I3)
F7:F13	F7	=ROUND(C7-C6,0)

 

[Toadstool]

Hi MSampson,

Did the above work for you?

Just in case you want to know the last row number you must copy down the G4:L4 formulae I've put it in H1 and suppressed results if there's formulae below. I've also put the conditional format colors in as per your results.

MSampson.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1						Last row=	131
2					number of rows needed	Table Group	*	col B interpolated	col C interpolated	col D interpolated	col E interpolated
3	1	94.56706786	116.5211679	136.1229154		1	6	1	94.56706786	116.5211679	136.1229154
4	2.5	103.3438736	127.1744494	148.2592302	6	1	6	1.25	96.02986881	118.2967148	138.1456345
5	5	108.8686899	136.0744065	159.4331431	8	1	6	1.5	97.49266976	120.0722617	140.1683536
6	10	119.10105	146.036832	170.0928255	7	1	6	1.75	98.95547072	121.8478087	142.1910728
7	25	130.5684167	163.3527475	189.87717	11	1	6	2	100.4182717	123.6233556	144.2137919
8	50	146.7740196	185.1069233	212.8016906	16	1	6	2.25	101.8810726	125.3989025	146.2365111
9	75	165.0290936	208.8683641	235.5755794	18	2	8	2.5	103.3438736	127.1744494	148.2592302
10	90	184.8077731	234.353515	257.548435	20	2	8	2.8125	104.0344756	128.2869441	149.6559693
11	95	201.3133789	250.6737495	272.6511528	17	2	8	3.125	104.7250776	129.3994387	151.0527084
12	97.5	215.7068058	266.4703994	284.4668238	14	2	8	3.4375	105.4156797	130.5119334	152.4494475
13	99	227.4731512	284.6029525	301.0265374	12	2	8	3.75	106.1062817	131.624428	153.8461867
14						2	8	4.0625	106.7968838	132.7369226	155.2429258
15						2	8	4.375	107.4874858	133.8494173	156.6396649
16						2	8	4.6875	108.1780878	134.9619119	158.036404
17						3	7	5	108.8686899	136.0744065	159.4331431
18						3	7	5.71429	110.3304556	137.4976102	160.9559549
19						3	7	6.42857	111.7922213	138.9208138	162.4787666
Sheet1 (2)

Cell Formulas
Range	Formula
H1	H1	=SUM($F$3:$F$999)+ROW($H$2)
H3	H3	=F4
I3:L3	I3	=B3
G4:G19	G4	=IF(ROW()>$H$1,"",IF(ROW()-MATCH(MAX($G$3:G3),$G$3:G3,0)-1>INDEX($F$4:$F$9999,MAX($G$3:G3)),MAX($G$3:G3)+1,G3))
H4:H19	H4	=IF($G4="","",INDEX($F$4:$F$9999,MAX($G$3:G4)))
I4:L19	I4	=IF($G4="","",IF($G3<>$G4,INDEX(B$3:B$9999,$G4),(((INDEX(B$3:B$9999,$G4+1)-(INDEX(B$3:B$9999,$G4)))/$H4))+I3))
F7:F13	F7	=ROUND(C7-C6,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J3:L9999	Expression	=AND($G3<>$G2,$G3<>"")	text	NO
I3:I9999	Expression	=AND(G3<>G2,G3<>"")	text	NO

 

[msampson]

thank you! This works beautifully. I'm starting to learn to use index and match rather than just simple vlookups, but I'm still fumbling with them somewhat.

column F is simply the difference between the data in the rows of the original chart. There will be no data below B12:E12 because B are percentiles so this is the given max.

My next puzzle with this will be how to look at these results and retrieve more than just the first instance of a number in a column. With all these decimals they are different from each other, but I will unfortunately only have whole number to search for.
For example, when they are rounded to whole numbers J11 and J12 are both 105. I would then need to retrieve both row numbers so that I can pull row 9 and row 10 for further use.

 

[Toadstool]

You're welcome!

I'm not following the additional requirement so can you provide a worked example?
Maybe start at column N and dummy up the search you want and the cells you'd like to see?

 

[msampson]

thank you. I would be looking in Column C for a value that may occur more than once. A regular lookup will grab the first one it sees only. I need to grab all of them and all the columns to the right.

I made a mistake here. See below

 

[msampson]

the colors are just to highlight what I would expect to get.

 

[msampson]

oops, copied the wrong rows above.

sample.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2					number of rows needed	table group	*	col B interpolated	col C interpolated	col D interpolated	col E interpolated			search col C for:	return I from match	return J from match	return K from match	return L from match
3	1	94.6	117	136		1	6	1	94.6	116.5	136.1			105	3.13	105	129	151
4	2.5	103.3	127	148	6	1	6	1.25	96	118	138				3.44	105	131	152
5	5	108.9	136	159	8	1	6	1.50	97	120	140
6	10	119.1	146	170	7	1	6	1.75	99	122	142			average of all matches	3.28	105	130	152
7	25	130.6	163	190	11	1	6	2.00	100	124	144
8	50	146.8	185	213	16	1	6	2.25	102	125	146
9	75	165.0	209	236	18	2	8	2.50	103	127	148
10	90	184.8	234	258	20	2	8	2.81	104	128	150
11	95	201.3	251	273	17	2	8	3.13	105	129	151
12	97.5	215.7	266	284	14	2	8	3.44	105	131	152
13	99	227.5	285	301	12	2	8	3.75	106	132	154
14						2	8	4.06	107	133	155
15						2	8	4.38	107	134	157
16						2	8	4.69	108	135	158
17						3	7	5.00	109	136	159
18						3	7	5.71	110	137	161
19						3	7	6.43	112	139	162
20						3	7	7.14	113	140	164
21						3	7	7.86	115	142	166
22						3	7	8.57	116	143	167
23						3	7	9.29	118	145	169
24						4	11	10.00	119	146	170
25						4	11	11.36	120	148	172
26						4	11	12.73	121	149	174
27						4	11	14.09	122	151	175
28						4	11	15.45	123	152	177
29						4	11	16.82	124	154	179
30						4	11	18.18	125	155	181
31						4	11	19.55	126	157	183
32						4	11	20.91	127	159	184
33						4	11	22.27	128	160	186
34						4	11	23.64	130	162	188
35						5	16	25.00	131	163	190
36						5	16	26.56	132	165	191
37						5	16	28.13	133	166	193
38						5	16	29.69	134	167	194
39						5	16	31.25	135	169	196
40						5	16	32.81	136	170	197
41						5	16	34.38	137	172	198
Sheet1

Cell Formulas
Range	Formula
H3	H3	=F4
I3:L3	I3	=B3
P3:S4	P3	=I11
G4:G41	G4	=IF(ROW()-MATCH(MAX($G$3:G3),$G$3:G3,0)-1>INDEX($F$4:$F$9999,MAX($G$3:G3)),MAX($G$3:G3)+1,G3)
H4:H41	H4	=INDEX($F$4:$F$9999,MAX($G$3:G4))
I4:I41	I4	=IF($G3<>$G4,INDEX(B$3:B$13,$G4),(((INDEX(B$3:B$13,$G4+1)-(INDEX(B$3:B$13,$G4)))/$H4))+I3)
J4:L41	J4	=IF($G3<>$G4,INDEX(C$3:C$9999,$G4),(((INDEX(C$3:C$9999,$G4+1)-(INDEX(C$3:C$9999,$G4)))/$H4))+J3)
P6:S6	P6	=AVERAGE(P3:P4)
F7:F13	F7	=ROUND(C7-C6,0)

 

[Toadstool]

Before I forget... If you want the last entry in column B to be reported then you'll need to enter "number of rows needed" into F14.

You say search in column C but you actually mean to search column J, right?

I see your data only ever has 2 rows containing the integer of any search criteria but I've allowed for 5 just in case. Add this section to the sheet:

MSampson.xlsx
	O	P	Q	R	S
2	search col C for:	return I from match	return J from match	return K from match	return L from match
3	105	3.125	104.7250776	129.3994387	151.0527084
4		3.4375	105.4156797	130.5119334	152.4494475
5
6
7
8
9	average of all matches	3.28125	105.0703787	129.955686	151.751078
New1Feb

Cell Formulas
Range	Formula
P3:S7	P3	=IFERROR(INDEX(I$3:I$239,AGGREGATE(15,6,ROW($J$3:$J$239)-ROW($J$2)/(ROUND($J$3:$J$239,0)=$O$3),ROW()-ROW($P$2))),"")
P9:S9	P9	=AVERAGE(P3:P7)

 

[msampson]

thank you!
now I get to try to teach myself to understand how aggregate, small, row etc. work.