Dynamic range in LINEST?

If your data is the entire column, other than header(s) and footer(s), Numbers has a really slick range reference method where you use just the column letter. Instead of "B2:B20" or "B2:C20" you use "B" or "B:C" to reference the entire column(s). It will include new rows automatically and won't break if you delete rows. You can do the same kind of thing with entire rows .For rows it is "2:2" for all of row 2 or "2:5" for rows 2 through 5.

I am surprised LINEST does not ignore blank rows. But it does not. So here is a formula that will include rows in the formula as you add data to them.

=LINEST(OFFSET(C$1,1,0,COUNTA(C),1),OFFSET(B$1,1,0,COUNTA(B),1))

Richatom wrote:

Thanks - but I need to have empty rows at the bottom of the table to fill.

Why? If you go to the last body row in the table and hit return, you'll immediately get a new row to input new values. The formulas will adjust automatically for the new row.

SG

Also, the SLOPE, INTERCEPT, and FORECAST functions do ignore blank cells. If you are not using the other statistics or forcing a 0 intercept, they might do what you need.

Hi Richatom,

Yes. This screen shot is where we would expect a value of 1 for the slope:

The formula refers to all the body rows in C and B

Now add another row:

The formula still refers to all the body rows (it automatically includes row 9).

Regards,

Ian.