If there are blank cells in the data area of LINEST, how can I SKIP them but still get a result？

[Natalie12138]

I am using "=@INDEX(LINEST(E$12:E$16,$C$12:$C$16,,TRUE),,2)" now , but I find if I delete a few data , it would turn out to be #VALUE; I want to know how can I get the results without deleting the whole row? Should I add the IF functions?

 

[Domenic]

You can use the FILTER function to filter the range for blank cells...

=@INDEX(LINEST(FILTER(E$12:E$16,E$12:E$16<>""),FILTER($C$12:$C$16,C$12:C$16<>""),,TRUE),,2)

Hope this helps!

 

[Natalie12138]

Thanks for your help! But there is a problem that when I delete a data which means the cell turn to blank, the results doesn't change along with the data and go wrong,, if there are any ways to make this possible? Thanks a lot!!!

1201.xlsx
	C	D	E
12	10	1.6898	1.6801
13	20	1.6814	1.6719
14	25	1.6773
15	40	1.6641	1.6549
16	50	1.6535	1.6445
17	70	1.6363	#VALUE!
18	85	1.6227	#VALUE!
19	R2	0.99785	#VALUE!
20	a	-9.01E-04	#VALUE!
21	b	1.6994	#REF!
Dn

Cell Formulas
Range	Formula
D17:E18	D17	=$C17*D$20+D$21
D19:E19	D19	=INDEX(LINEST(D$12:D$16,$C$12:$C$16,,TRUE),3)
D20:E20	D20	=INDEX(LINEST(D$12:D$16,$C$12:$C$16,,TRUE),1)
D21	D21	=INDEX(LINEST(D$12:D$16,$C$12:$C$16,,TRUE),,2)
E21	E21	=@INDEX(LINEST(FILTER(E$12:E$16,E$12:E$16<>""),FILTER($C$12:$C$16,C$12:C$16<>""),,TRUE),,2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AG1:AG4,D37:H37,D60:H60,I5:I1048576,D19:H19	Cell Value	between 0.001 and 0.995	text	NO

 

[Domenic]

The formula is designed to return a #REF! error value when a cell is blank in either column. If this is not the desired behaviour, how do you want handle such situations? Do you want to exclude any row where a cell in either column is blank? If so, try...

=@LET(data,FILTER(C12:E16,(C12:C16<>"")*(E12:E16<>"")),INDEX(LINEST(INDEX(data,0,3),INDEX(data,0,1),,TRUE),,2))

However, if you're planning to copy the formula across, try the following instead...

=@LET(data,FILTER($C$12:E$16,($C$12:$C$16<>"")*(E$12:E$16<>"")),INDEX(LINEST(INDEX(data,0,COLUMNS(data)),INDEX(data,0,1),,TRUE),,2))

Hope this helps!

 

[Natalie12138]

Thank you for your help !!!! I have tried what you said and I got perfect results in calculating "a" and "b "(D20,E20.E21,E21) . but there a problem when I try this to calculate R2( Coefficients of determination in LINEST function) .If there are any ways to get the right results of R2?

This is what I tried in E19:

=@LET(data,FILTER($C$12:E$16,($C$12:$C$16<>"")*(E$12:E$16<>"")),INDEX(LINEST(INDEX(data,0,COLUMNS(data)),INDEX(data,0,1),,TRUE),,3))

HUGLY appreciated !!!
Thanks again!!!!

 

[Domenic]

I'm not a statistician, but I think this is probably what you want...

=LET(RowsToInclude,(C12:C16<>"")*(D12:D16<>"")*(E12:E16<>""),RangeY,FILTER(E12:E16,RowsToInclude),RangeX,FILTER(C12:D16,RowsToInclude),INDEX(LINEST(RangeY,RangeX,,TRUE),,3))

Or, to get rid of #N/A error values...

=LET(RowsToInclude,(C12:C16<>"")*(D12:D16<>"")*(E12:E16<>""),RangeY,FILTER(E12:E16,RowsToInclude),RangeX,FILTER(C12:D16,RowsToInclude),IFERROR(INDEX(LINEST(RangeY,RangeX,,TRUE),,3),""))

Is this what you want?

 

[Natalie12138]

You are soooo kind !! Thank u so much for your replies!! However , I got a SPILL error when I tried this two formulas, I guess maybe my English is too poor that I couldn't express what I want exactly , so I add a mini chart below .

As you know ,I used the LINEST to get R2 results at first ,and my goal is to keep R2 >0.995, so sometimes I need to delete some bad data, but when I delete a single cell, the results turn to VALUE error, and when I delete the whole row, the results are good. So I wonder if there are functions that could still get the results when I delete a single cell, which means the results change along with the deletion. (like I delete E53 in the chart, and I want E59,E60 still show the calculation results right after the deletion) Could you give some help, please?

1201.xlsx
	C	D	E
53	20	0.1696
54	25	0.1664	0.1600
55	40	0.1554	0.1482
56	50	0.1464	0.1414
57	70	0.1322	0.1275
58	85	0.1210	0.1164
59	R2	0.99932	#VALUE!
60	R2 (cal.)	0.99976	#VALUE!
Dn

Cell Formulas
Range	Formula
D53:D55,D56:E58	D53	=D13-D31
D59:E59	D59	=INDEX(LINEST(D$53:D$55,$C$53:$C$55,,TRUE),3)
D60:E60	D60	=INDEX(LINEST(D$48:D$58,$C$48:$C$58,,TRUE),3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D59:H59	Cell Value	between 0.001 and 0.995	text	NO
AG1:AG4,D37:H37,D60:H60,I5:I1048576,D19:H19	Cell Value	between 0.001 and 0.995	text	NO

Thanks again!!!!!

 

[Domenic]

Just to be clear, in your example, cell E53 is blank. In this case, I'm assuming that you want to exclude Row 53 (cell C53 and E53) from the calculation, correct?

Also, can you please provide the actual results that you expect for cell E59 and E60?

 

[Natalie12138]

Thank you for your time ! Yes, I want to exclude Row 53 without deleting the row when I turn E53 to blank.
The original data of E53 is 0.1634,after I delete the cell, the expected results are : E59: 1.00000 ; E60 : 0.99957 ,which are the results after I delete Row53(which means C54 turn to C53, D54 turn to D53...)

Thanks again!!!!!!!!

 

[Domenic]

In that case, try...

=LET(data,FILTER($C$53:$E$55,($C$53:$C$55<>"")*($E$53:$E$55<>"")),INDEX(LINEST(INDEX(data,0,3),INDEX(data,0,1),,TRUE),3,1))

And the same thing for your other one, just change the ranges accordingly.

Hope this helps!