Blank Cells and LINEST

Tony Collins

New Member
Joined
Jun 5, 2009
Messages
2
I have been trying to use LINEST for the analysis of a large sets of data but have just noticed that this function does not allow for empty cells in the data set. Apart from the tredious possibility of removing the blank cells (which is not really possible for the large quantity of data involved) does anybody have any suggestions?

I have tried using the following but have not had any success:
=LINEST(IF(A3:A54=0,"",A3:A54),IF(B3:B54=0,"",B3:B54),,TRUE)

=LINEST(IF(ISNUMBER(A3:A54),A3:A54,""),IF(ISNUMBER(B3:B54),B3:B54,""))

I do not want the formula to take the empty cells as being 0 but to exclude them from the calculation.

All help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Phox

Well-known Member
Joined
Jul 26, 2004
Messages
522
Enter this as an array formula (Ctrl+Shift+Enter):
Code:
=LINEST(IF(A3:A54="",A3,A3:A54),IF(B3:B54="",B3,B3:B543))
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Another possibility:

=LINEST(N(OFFSET(A3:A54,SMALL(IF(ISNUMBER(A3:A54),ROW(A3:A54)-ROW(A3)),ROW(INDIRECT("1:"&COUNT(A3:A54)))),,1)),N(OFFSET(B3:B54,SMALL(IF(ISNUMBER(B3:B54),ROW(B3:B54)-ROW(B3)),ROW(INDIRECT("1:"&COUNT(B3:B54)))),,1)))

also with Ctrl+Shift+Enter.
 

Phox

Well-known Member
Joined
Jul 26, 2004
Messages
522
Enter this as an array formula (Ctrl+Shift+Enter):
Code:
=LINEST(IF(A3:A54="",A3,A3:A54),IF(B3:B54="",B3,B3:B543))
Econometrics class from years ago is flooding back to me... On second thought, while avoiding the error, this method may skew your results, so you should probably check the answer above, although I'm not sure what his solution is doing.
 

Tony Collins

New Member
Joined
Jun 5, 2009
Messages
2
Thank you both very much for the rapid replies.
However these solutions do not seem to be working, it appears that Phoxs suggestion has the problem of replacing the empty cell with the value in cell A3 ('value if TRUE' in logical test).
Mr Poulsoms suggestion appears close (even though its complexity baffles me, I don't really understand it), but only appears to work if the cells for corresponding x and y values are BOTH empty.
In my case the X valuse are constant while the Y values vary, with the Y values frequently containing empty cells or indeed 0.
I am surprised that EXCEL don't seem to have a means for the function to ignore empty cellls or those containing 0 when carrying out a LINEST calculation.
I'm actually not too sure if it is possible to regulate this problem with EXCEL.
Thank you.
 

Phox

Well-known Member
Joined
Jul 26, 2004
Messages
522
Stick around Tony. I'm sure Andrew can alter his formula to accomodate. I doubt he expected only one of the values would be blank.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,211
Andrew's formula can be modified as follows...

Code:
=LINEST(N(OFFSET(A3:A54,SMALL(IF(B3:B54<>0,ROW(B3:B54)-ROW(B3)),
     ROW(INDIRECT("1:"&COUNT(B3:B54)))),,1)),N(OFFSET(B3:B54,
          SMALL(IF(B3:B54<>0,ROW(B3:B54)-ROW(B3)),ROW(INDIRECT("1:"&
               COUNT(B3:B54)))),,1)))
...confirmed with CONTROL+SHIFT+ENTER. If, however, the so-called empty cells actually contain formula blanks (""), the formula would need to be modified further.

Edit: Actually, I guess it should be the other way around...

Code:
=LINEST(N(OFFSET(A3:A54,SMALL(IF(A3:A54<>0,ROW(A3:A54)-ROW(A3)),
     ROW(INDIRECT("1:"&COUNT(A3:A54)))),,1)),N(OFFSET(B3:B54,
          SMALL(IF(A3:A54<>0,ROW(A3:A54)-ROW(A3)),ROW(INDIRECT("1:"&
               COUNT(A3:A54)))),,1)))
 
Last edited:

Excelling_in_Excel

New Member
Joined
Mar 22, 2013
Messages
2
I was able to work Domenic's formula if the cell values were vertically placed. I tried to modified the formula(see below) for cell values placed horizontally, however I keep getting "#Value!". I'm not sure if my formula is correctly modified.

Code:
=LINEST(N(OFFSET(B15:K15,,SMALL(IF(B15:K15<>0,COLUMN(B15:K15)-COLUMN(B15)),
COLUMN(INDIRECT("1:"&COUNT(B15:K15)))),1,)),N(OFFSET(B16:K16,,
SMALL(IF(B15:K15<>0,COLUMN(B15:K15)-COLUMN(B15)),COLUMN(INDIRECT("1:"&
COUNT(B15:K15)))),1,)))
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Some of the ROW functions need to remain as ROW, even for horizontal ranges of data - try this version

=LINEST(N(OFFSET(B15:K15,,SMALL(IF(B15:K15<>0,COLUMN(B15:K15)-COLUMN(B15)),ROW(INDIRECT("1:"&COUNT(B15:K15)))),1,)),N(OFFSET(B16:K16,,SMALL(IF(B15:K15<>0,COLUMN(B15:K15)-COLUMN(B15)),ROW(INDIRECT("1:"&COUNT(B15:K15)))),1,)))
 

Excelling_in_Excel

New Member
Joined
Mar 22, 2013
Messages
2
Perfect.... essentially I am trying to calculate a multiple regression, with the "y" variable having blanks or "-" at times. If the "y" variables has blanks or "-" the regression ignores the calculation for that paired series. I did an independent multiple regression compared to my formula and I get different answers.

X variables : BQ11:DX12
Y variables : BQ18:DX18


=INDEX(LINEST(N(OFFSET(BQ18:DX18,,SMALL(IF(BQ18:DX18<>0,COLUMN(BQ18:DX18)-COLUMN(BQ18)),ROW(INDIRECT("1:"&COUNT(BQ18:DX18)))),1,)),N(OFFSET(BQ11:DX12,,SMALL(IF(BQ18:DX18<>0,COLUMN(BQ18:DX18)-COLUMN(BQ18)),ROW(INDIRECT("1:"&COUNT(BQ18:DX18)))),1,)),TRUE,TRUE),1,1)+INDEX(LINEST(N(OFFSET(BQ18:DX18,,SMALL(IF(BQ18:DX18<>0,COLUMN(BQ18:DX18)-COLUMN(BQ18)),ROW(INDIRECT("1:"&COUNT(BQ18:DX18)))),1,)),N(OFFSET(BQ11:DX12,,SMALL(IF(BQ18:DX18<>0,COLUMN(BQ18:DX18)-COLUMN(BQ18)),ROW(INDIRECT("1:"&COUNT(BQ18:DX18)))),1,)),TRUE,TRUE),1,2)
ctrl+shift+enter
 

Watch MrExcel Video

Forum statistics

Threads
1,102,676
Messages
5,488,213
Members
407,633
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top