array formula variable range size

[iceaxesun]

I am using a linear regression array formula that uses a range of three columns, X, Y, and Yerror. Works great. However, the number of data points is variable. Is there a way to make the array formula detect the size of the range, or to ignore empty cells at the end of the range.


The array formula is


=LSFit(C9:C17,D9:D17,E9:E17), an add-in that computes weighted least squares fit with errors.


I have tried LSFit(if(C9:C17<>"",C9:C17),if(D9:D17<>"",D9:D17),if(E9:E17<>"",E9:E17)), but this returns DIV/0 just the same if blank lines are included in the array formula.


Is there a way to accomplish this, or do I need to resize the ranges in the formula each time I have a different number of inputs?

I have done this with loops in VBA, but would rather keep this simple.

 

[pgc01]

Hi
Welcome to the board

There are several ways to do it. THis is one.

Let's assume the values start in row 9 go down in contiguous cells up to maximum row 50. You can

- calculate how many X values there are. For ex. in A1:

=COUNT(C9:C50)

- Use the formula:

=LSFit(C9:INDEX(C9:C50,A1),D9:INDEX(D9:D50,A1),E9:INDEX(E9:E50,A1))

 

[iceaxesun]

Sweet and simple. Thanks much!

I am wondering if this will work for chart series that include trailing blank cells, as well.

 

[pgc01]

You're welcome. Thanks for the feedback.

I am wondering if this will work for chart series that include trailing blank cells, as well.

It will if you use it in a named range. The chart series accepts named ranges as data sources.

Check this Jon Peltier article about Dynamic Charts:

Dynamic Charts | Peltier Tech Blog | Excel Charts