Dynamically change a table with formulas

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
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
BCDEFGHIJK
1*Col E pulls from D and needs to drop down a row when F switches to the next row in the summary table
2number of rows needed*col B interpolatedcol C interpolatedcol D interpolatedcol E interpolated
3194.61171366194.6116.5136.1
42.5103.3127148661.2596.0118.3138.1
55108.9136159861.5097.5120.1140.2
610119.1146170761.7599.0121.8142.2
725130.61631901162.00100.4123.6144.2
850146.81852131662.25101.9125.4146.2
975165.02092361882.5103.3127.2148.3
1090184.82342582082.81104.0128.3149.7
1195201.32512731783.13104.7129.4151.1
1297.5215.72662841483.44105.4130.5152.4
1399227.52853011283.75106.1131.6153.8
1484.06106.8132.7155.2
1584.38107.5133.8156.6
1684.69108.2135.0158.0
1775108.9136.1159.4
1875.71110.3137.5161.0
1976.43111.8138.9162.5
2077.14113.3140.3164.0
2177.86114.7141.8165.5
2278.57116.2143.2167.0
2379.29117.6144.6168.6
241110119.1146.0170.1
251111.36120.1147.6171.9
261112.73121.2149.2173.7
271114.09122.2150.8175.5
281115.45123.3152.3177.3
Sheet1
Cell Formulas
RangeFormula
G3G3=F4
H3:K3H3=B3
G4G4=F4
H4:H8H4=(($B$4-$B$3)/$G3)+H3
I4:K8I4=((C$4-C$3)/$G4)+I3
G5G5=F4
G6G6=F4
G7G7=F4
G8:G9G8=F4
H9:K9H9=B4
G10G10=F5
H10:H16H10=(($B$5-$B$4)/$G9)+H9
I10:K16I10=((C$5-C$4)/$G10)+I9
G11G11=F5
G12G12=F5
G13G13=F5
G14G14=F5
G15G15=F5
G16:G17G16=F5
H17:K17H17=B5
G18G18=F6
H18:H23H18=(($B$6-$B$5)/$G17)+H17
I18:K23I18=((C$6-C$5)/$G18)+I17
G19G19=F6
G20G20=F6
G21G21=F6
G22G22=F6
G23:G24G23=F6
H24:K24H24=B6
G25G25=F7
H25:H28H25=(($B$7-$B$6)/$G24)+H24
I25:K28I25=((C$7-C$6)/$G25)+I24
G26G26=F7
G27G27=F7
G28G28=F7
F7:F13F7=ROUND(C7-C6,0)
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
thank you for breaking it out and explaining further. That is so very helpful.
 

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
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
1612203814877.png

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

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
I figured that out. it's a rounding issue. The ones that are one short round up to the number in my list.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MSampson2.xlsx
YZAAABACADAEAFAGAHAIAJAKALAMANAOAP
25Last row=155
26number of rows neededTable Group*col B interpolatedcol C interpolatedcol D interpolatedcol E interpolatedsearch col C for:return I from matchreturn J from matchreturn K from matchreturn L from match
27194.56706786116.5211679136.122915416194.56706786116.5211679136.12291541053.125104.7250776129.3994387151.0527084
282.5103.3438736127.1744494148.25923026161.2596.02986881118.2967148138.14563453.4375105.4156797130.5119334152.4494475
295108.8686899136.0744065159.43314318161.597.49266976120.0722617140.1683536    
3010119.10105146.036832170.09282557161.7598.95547072121.8478087142.1910728    
3125130.5684167163.3527475189.8771711162100.4182717123.6233556144.2137919    
3250146.7740196185.1069233212.801690616162.25101.8810726125.3989025146.2365111
3375165.0290936208.8683641235.575579418282.5103.3438736127.1744494148.2592302average of all matches3.28125105.0703787129.955686151.751078
3490184.8077731234.353515257.54843520282.8125104.0344756128.2869441149.6559693
3595201.3133789250.6737495272.651152817283.125104.7250776129.3994387151.0527084
3697.5215.7068058266.4703994284.466823814283.4375105.4156797130.5119334152.4494475
3799227.4731512284.6029525301.026537412283.75106.1062817131.624428153.8461867
38284.0625106.7968838132.7369226155.2429258
39284.375107.4874858133.8494173156.6396649
40284.6875108.1780878134.9619119158.036404
41375108.8686899136.0744065159.4331431
42375.71429110.3304556137.4976102160.9559549
43376.42857111.7922213138.9208138162.4787666
New1Feb
Cell Formulas
RangeFormula
AE25AE25=SUM($AC$27:$AC$1023)+ROW($AE$26)
AE27AE27=AC28
AF27:AI27AF27=Y27
AM27:AP31AM27=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:AD43AD28=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:AE43AE28=IF($AD28="","",INDEX($AC$28:$AC$10023,MAX($AD$27:AD28)))
AF28:AI43AF28=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:AP33AM33=AVERAGE(AM27:AM31)
AC31:AC37AC31=ROUND(Z31-Z30,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AG27:AI10023Expression=AND($AD27<>$AD26,$AD27<>"")textNO
AF27:AF10023Expression=AND(AD27<>AD26,AD27<>"")textNO
 

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
HIJKL
2Age18202125
321161116
4Result271217
515381318
6491419
75101520
Sheet6
Cell Formulas
RangeFormula
H5H5=MAX(OFFSET($I$3,,MATCH(H3,$I$2:$L$2,0)-1,5))
 

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
I'm sorry but I'm not following. Can you install the XL2BB addon and give a worked example of what you want?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,079
Messages
5,639,941
Members
417,119
Latest member
adityaj252

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top