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]

You're welcome!
Yes, it's challenging that many an answer comes with even more questions...

Let me take away the IFERROR as that only gives null when we run out of matches.

INDEX(I$3:I$239,
Getting column I value and I is relative so the formula can be copied right.

AGGREGATE(15,6,ROW($J$3:$J$239)-ROW($J$2)
Look at rows 3 to 239 but subtract 2 from the result so the INDEX gets the correct row.

/(ROUND($J$3:$J$239,0)=$O$3),
Divide each row number by the result of matching it with the search criteria. If rounded column J is equal to O3 (105 in our example) then it returns a logical 1, which divided into the current row being checked will result in the row number, a success. If the current row of J being looked at <>O3 then it gives a logical 0, which AGGREGATE ignores as we used the 6 option "Ignore error values" so the #DIV/0 is ignored and the next row checked.

ROW()-ROW($P$2))),"")
AGGREGATE(15 is the SMALL function so it's looking for the kth lowest number (in our case row). Setting k to ROW()-ROW($P$2) means this is 1 for the first result row (so we get the lowest row), 2 for the second (so we get the second lowest row), etc.

 

[msampson]

thank you for breaking it out and explaining further. That is so very helpful.

 

[msampson]

now I'm extending it all out to my real tables. I had to move everything right and insert 24 rows at the top to arrange things logically. I changed the formula in G4 to say
=IF(ROW()-MATCH(MAX($AD$27:AD27),$AD$27:AD27,0)-25>INDEX($AA$3:$AA$13,MAX($AD$27:AD27)),MAX($AD$27:AD27)+1,AD27)
the -1 becomes -25. But occasionally I get one row short.
the rows should be

but I'm getting 12, 11, 11 (should be 12), 20, 22, 23 (should be 24), 25, 14 (should be 15), 12 (should be 13), 20 (should be 21), 1

When I go back to the sample file and change F4:F14 to match what is pasted here I get the same errors without having moved right or down.

 

[msampson]

I figured that out. it's a rounding issue. The ones that are one short round up to the number in my list.

 

[Toadstool]

I had the Table Group number calculation wrong if you changed the location.
This should work for you:

MSampson2.xlsx
	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
25						Last row=	155
26					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
27	1	94.56706786	116.5211679	136.1229154		1	6	1	94.56706786	116.5211679	136.1229154			105	3.125	104.7250776	129.3994387	151.0527084
28	2.5	103.3438736	127.1744494	148.2592302	6	1	6	1.25	96.02986881	118.2967148	138.1456345				3.4375	105.4156797	130.5119334	152.4494475
29	5	108.8686899	136.0744065	159.4331431	8	1	6	1.5	97.49266976	120.0722617	140.1683536
30	10	119.10105	146.036832	170.0928255	7	1	6	1.75	98.95547072	121.8478087	142.1910728
31	25	130.5684167	163.3527475	189.87717	11	1	6	2	100.4182717	123.6233556	144.2137919
32	50	146.7740196	185.1069233	212.8016906	16	1	6	2.25	101.8810726	125.3989025	146.2365111
33	75	165.0290936	208.8683641	235.5755794	18	2	8	2.5	103.3438736	127.1744494	148.2592302			average of all matches	3.28125	105.0703787	129.955686	151.751078
34	90	184.8077731	234.353515	257.548435	20	2	8	2.8125	104.0344756	128.2869441	149.6559693
35	95	201.3133789	250.6737495	272.6511528	17	2	8	3.125	104.7250776	129.3994387	151.0527084
36	97.5	215.7068058	266.4703994	284.4668238	14	2	8	3.4375	105.4156797	130.5119334	152.4494475
37	99	227.4731512	284.6029525	301.0265374	12	2	8	3.75	106.1062817	131.624428	153.8461867
38						2	8	4.0625	106.7968838	132.7369226	155.2429258
39						2	8	4.375	107.4874858	133.8494173	156.6396649
40						2	8	4.6875	108.1780878	134.9619119	158.036404
41						3	7	5	108.8686899	136.0744065	159.4331431
42						3	7	5.71429	110.3304556	137.4976102	160.9559549
43						3	7	6.42857	111.7922213	138.9208138	162.4787666
New1Feb

Cell Formulas
Range	Formula
AE25	AE25	=SUM($AC$27:$AC$1023)+ROW($AE$26)
AE27	AE27	=AC28
AF27:AI27	AF27	=Y27
AM27:AP31	AM27	=IFERROR(INDEX(AF$27:AF$263,AGGREGATE(15,6,ROW($AG$27:$AG$263)-ROW($AG$26)/(ROUND($AG$27:$AG$263,0)=$AL$27),ROW()-ROW($AM$26))),"")
AD28:AD43	AD28	=IF(ROW()>$AE$25,"",IF(ROW()-MATCH(MAX($AD$27:AD27),$AD$27:AD27,0)-ROW($AD$26)+1>INDEX($AC$28:$AC$10023,MAX($AD$27:AD27)),MAX($AD$27:AD27)+1,AD27))
AE28:AE43	AE28	=IF($AD28="","",INDEX($AC$28:$AC$10023,MAX($AD$27:AD28)))
AF28:AI43	AF28	=IF($AD28="","",IF($AD27<>$AD28,INDEX(Y$27:Y$10023,$AD28),(((INDEX(Y$27:Y$10023,$AD28+1)-(INDEX(Y$27:Y$10023,$AD28)))/$AE28))+AF27))
AM33:AP33	AM33	=AVERAGE(AM27:AM31)
AC31:AC37	AC31	=ROUND(Z31-Z30,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AG27:AI10023	Expression	=AND($AD27<>$AD26,$AD27<>"")	text	NO
AF27:AF10023	Expression	=AND(AD27<>AD26,AD27<>"")	text	NO

 

[msampson]

that works. Thanks.

I'm searching online to try to understand combining index, match and max. All the examples are pulling max from a specific column. But I want to use the index to identify the column (matching someone's age when each age is a series of columns). So if someone is age 20 I need the max from column J, but if someone is age 25 then I need the max from column K. I can't seem to get match and max to pull from the correct age column which is variable for each row. If I put an array in for match or max it doesn't work.
The reason I want this is before, when we looked up 105 it was there no problem. But if someone has a value of 205 that is outside the range of the lookup table. So I planned to have a max allowed and if they are greater than the max, look up the max instead.

 

[Toadstool]

I'm not sure I'm following exactly so let me give an example not related to the existing sheet.

MAX can accept an array so we want to give it the correct column in this example which we can do with OFFSET.
So the base cell for OFFSET is $I$3, the first cell we might use for the MAX.
The second parameter is what rows to offset by, but we don't want to change from row 3 so we omit this parameter.
Next is the column offset, so I MATCH the age to $I$2:$L$2 and subtract 1 so I get a column offset of 0 through 3.
The next is height in rows so I've got 5 rows of data so it's 5.
The next is width in columns but we just want one column so it's omitted.

MSampson2.xlsx
	H	I	J	K	L
2	Age	18	20	21	25
3	21	1	6	11	16
4	Result	2	7	12	17
5	15	3	8	13	18
6		4	9	14	19
7		5	10	15	20
Sheet6

Cell Formulas
Range	Formula
H5	H5	=MAX(OFFSET($I$3,,MATCH(H3,$I$2:$L$2,0)-1,5))

 

[msampson]

beautiful! Thanks so much.
Maureen

 

[msampson]

Can I put a match inside the aggregate where it has row($J3:$j$239)? I want to match, not a static column, but one based on age. Then I'll be pulling everything in those same rows across all the columns.

Another option is what I did when I was first only matching the row of the first instance. I had one cell that returned the column (age) and a second cell that returned the row (where the match occurred). Then I referenced those two cells to pull all the columns across at that row. The problem now is that I want to match multiple rows with duplicate values for the match which is what the aggregate will do if I can make the match column match the age.

AGGREGATE(15,6,ROW($J$3:$J$239)-ROW($J$2)
Look at rows 3 to 239 but subtract 2 from the result so the INDEX gets the correct row.

/(ROUND($J$3:$J$239,0)=$O$3),
Divide each row number by the result of matching it with the search criteria. If rounded column J is equal to O3 (105 in our example) then it returns a logical 1, which divided into the current row being checked will result in the row number, a success. If the current row of J being looked at <>O3 then it gives a logical 0, which AGGREGATE ignores as we used the 6 option "Ignore error values" so the #DIV/0 is ignored and the next row checked.

 

[Toadstool]

I'm sorry but I'm not following. Can you install the XL2BB addon and give a worked example of what you want?