recording a macro for a dynamic range using the LINEST formula

[mjfox]

Hi,
Does anyone know how I can create a macro that will reference a dynamic cell range in a formula that uses an array? For example,
I am establishing regression coefficients for a second order polynomial using the LINEST formula. This works fine when I define the ranges for the array as written below:

{=LINEST(C4:C19,A4:B19,,TRUE)} where the cell range C4:C19 are my Y values, and the cell range A4:B19 are my X and X squared values for the second order polynomial.

However, I expect to be adding rows of data periodically, where I’d like to set up a macro to automatically define this array to the last row with ‘something in it’ and update my regression coefficients with a push of a button (for ease of other users). In my research on how to do this, I saw this referred to as a ‘dynamic range’ where the offset and counta functions are one way to make this work. Potentially, an INDEX function could work according to some of the forums, but I’m not sure how to write that in VB or record it in a macro. Here is what I tried, but to no avail thus far:

{=LINEST(OFFSET($C$4,0,0,COUNTA($C:$C),1),OFFSET($A$4,0,1,COUNTA($A:$B),1),,TRUE)}

Maybe a fresh look without starting with my failed script is a better approach. The data is pasted below, where the value "20.77" is in cell C:4 for reference. FYI, CFS is "cubic feet per second" (river flow), and WSE is "water surface elevation," which are the X and Y values for the regression.

Thanks!
-Martin

						A*X^2 + B*X + C = Y
			Pred	Pred	Regression coefficients
CFS	CFS SQ	WSE	WSE	CFS	A	B	C
373​	1.39E+05​	20.77​	20.86​	373​	-1.34595E-07​	0.003602061​	19.5366292​
468​	2.19E+05​	21.04​	21.19​	468​	9.03062E-09​	9.18168E-05​	0.11430899​
624​	3.89E+05​	21.67​	21.73​	624​
665​	4.42E+05​	21.97​	21.87​	665​
853​	7.28E+05​	22.75​	22.51​	853​
1010​	1.02E+06​	23.16​	23.04​	1010​
1240​	1.54E+06​	23.95​	23.80​	1240​
1350​	1.82E+06​	24.3​	24.15​	1350​
1590​	2.53E+06​	24.68​	24.92​	1590​
1650​	2.72E+06​	25.21​	25.11​	1650​
2040​	4.16E+06​	26.35​	26.32​	2040​
2095​	4.39E+06​	26.2​	26.49​	2095​
3030​	9.18E+06​	29.07​	29.22​	3030​
7910​	6.26E+07​	39.8​	39.61​	7910​
9200​	8.46E+07​	41.46​	41.28​	9200​
9750​	9.51E+07​	41.6​	41.86​	9750​

 

[Domenic]

Actually, I would suggest that you convert your range for your source data into a Table (Ribbon >> Insrert >> Tables >> Table). This way your range will automatically adjust as rows are added or delete.

 

[mjfox]

Thanks Domenic. I tried that, but the LINEST function does not seem to update to new data being added. Do I merely define my range (e.g. C5:20, A5:B20) or do I need to do something special because it is in a table?

 

[Domenic]

Once you've converted your range into a Table, you'll need to amend your formula so that it uses structured references. Have you done so?

 

[mjfox]

No, I don't know how to do that. Can you please show me an example, say with "={LINEST(C5:C20,A5:B20,,TRUE)}?

 

[Domenic]

Sure, in the following example, I converted the range into a Table, and the table is named "Table1".

mjfox.xlsm
	A	B	C	D	E	F	G	H	I
1	CFS	CFS SQ	WSE	Pred WSE	Pred CFS
2	373	139000	21	21	373		-1.34621E-07	0.003602479	19.53615899
3	468	219000	21	21	468		9.00176E-09	9.15334E-05	0.113954024
4	624	389000	22	22	624		0.999393375	0.190441692	#N/A
5	665	442000	22	22	665		10708.51816	13	#N/A
6	853	728000	23	23	853		776.7538905	0.471484496	#N/A
7	1010	1020000	23	23	1010
8	1240	1540000	24	24	1240
9	1350	1820000	24	24	1350
10	1590	2530000	25	25	1590
11	1650	2720000	25	25	1650
12	2040	4160000	26	26	2040
13	2095	4390000	26	26	2095
14	3030	9180000	29	29	3030
15	7910	62600000	40	40	7910
16	9200	84600000	41	41	9200
17	9750	95100000	42	42	9750
Sheet1

Cell Formulas
Range	Formula
G2:I6	G2	=LINEST(Table1[WSE],Table1[[CFS]:[CFS SQ]],,TRUE)
Dynamic array formulas.

Note the following...

1) When writing your formula, you start with typing =LINEST( , and then you select your first range. You'll see that Excel automatically enters the structured reference for you. And then you continue with the rest of your formula.

2) Since it looks like you're not using dynamic Excel, you'll need to select G2:I6, enter the formula, and then confirm with CONTROL+SHIFT+ENTER.

3) When adding a new row, the formula returns #VALUE! until all of the relevant values have been entered for that row.

Hope this helps!

 

[mjfox]

Wow, it works! Genius! And even better, it automatically updates the LINEST coefficients with each new record, so I don't need to include the process of refreshing that function into a macro. I do note that my cell reference does not show any reference to 'table 1' and such like yours does; it merely looks the same (=LINEST(C5:C22,A5:B22,,TRUE)) but the range changes with each record addition. Will this be a problem in any way?
Thanks so much Domenic!

 

[Domenic]

Maybe the option for using table names in formulas is unchecked/de-selected. Have a look at...

Ribbon >> File tab >> Options >> Formulas >> Working with formulas >> Use table names in formulas

If it's unchecked, check it. Or, if you prefer, leave it unchecked.

 

[mjfox]

Is this option for table names also under the formula tab? I am not proficient enough with VB to trust interjecting this code into a recorded macro :>/

I will be replicating this process into multiple worksheets, each one referencing a new set of data for a unique table. Therefore, it may indeed be more transparent if I can see the referenced table name to ensure that I am processing the correct data each. However, by manually referencing each table range separately as above per worksheet, perhaps it identifies each source table automatically? Maybe I don't need to use table names in formulas if it works without them? advantage/disadvantage?

 

[Domenic]

Is this option for table names also under the formula tab? I am not proficient enough with VB to trust interjecting this code into a recorded macro :>/

No, not that I can see. Was that option checked or unchecked in Options?

I will be replicating this process into multiple worksheets, each one referencing a new set of data for a unique table. Therefore, it may indeed be more transparent if I can see the referenced table name to ensure that I am processing the correct data each. However, by manually referencing each table range separately as above per worksheet, perhaps it identifies each source table automatically? Maybe I don't need to use table names in formulas if it works without them? advantage/disadvantage?

First, as you've already seen, the range automatically adjusts as data is added or removed. Secondly, once understood, using structured references instead of cell references is a lot easier to work with. You don't have to worry about referencing the exact range of cells. Also, formulas automatically fill down the entire column. Lastly, you have all sorts of table styles available to you. So you can quickly change the look of your table, depending on your taste. You might find it a little difficult at first, but eventually you'll see that it's not too difficult at all. There maybe other advantages/disadvantages, but these are the ones I can think of at the moment.