Amend Formula range based on how many cells in range are blank.

[D_Spark]

In a workbook I have the following formula

=LINEST(B2:B4,A2:A4,FALSE,TRUE)


What Im want to achieve is to amend the formula so that the ranges move based on how many cells have values.


Eg1

If

B2 contains a value
B3 contains a value
B4 contains a value
B5 contains a value
B6 is blank
B7 is blank
B8 is blank
B9 is blank
B10 is blank
B11 is blank
B12 is blank

Then

The formula would be

=LINEST(B2:B5,A2:A5,FALSE,TRUE)


Eg2

If

B2 contains a value
B3 contains a value
B4 contains a value
B5 contains a value
B6 contains a value
B7 contains a value
B8 contains a value
B9 contains a value
B10 contains a value
B11contains a value
B12 is blank



Then

The formula would be

=LINEST(B2:B11,A2:A11,FALSE,TRUE)

 

[Richard Schollar]

Something like this maybe:

=LINEST(B2:INDEX(B2:B12,MATCH(9.9E+307,B2:B12,1)),A2:INDEX(A2:A12,MATCH(9.9E+307,B2:B12,1)),FALSE,TRUE)

 

[D_Spark]

Richard - Superb ! many thanks, this works perfectly !!!!!!