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
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 |
