Remove dados não numéricos de um array de excel

Question

Estou com problemas para usar o método LINEST com dados que contêm lacunas. Encomende e remova as lacunas à mão não é uma opção.

Eu encontrei isto:
=LINEST(ISNUMBER(X_4)*Y_4,IF(X_4<>0,ISNUMBER(Y_4)*X_4^{0,1,2},0),0)
de link
mas não lida com #N/A ou "" (em branco) apenas células nulas

Meus dados podem conter números, texto, #N/A e em branco. Eu preciso de alguma maneira para obter apenas os números, então eu posso usar LINEST .

Também encontrei essa possível solução, mas não consigo fazer isso funcionar, acho que há algum erro na fórmula:% =LINEST(IF(ISNUMBER(C1:C9),C1:C9,),IF(ISNUMBER(C1:C9),CHOOSE({1,2,3},1,A1:A9,B1:B9),),) de: link

Amostra de dados:

X   Y
1   16,0
2   18,0
""  #N/A //Here I mean a blank cell, but non null, like an empty string ""
4   41,0
5   48,0
6   61,0
    #DIV/0!

A fórmula deve ignorar qualquer linha não numérica, o resultado deve ser o mesmo de

X   Y
1   16
2   18
4   41
5   48
6   61

LINEST result: a = 9,279069767 b = 3,395348837 [ y(x) = ax+b ]

    

Answer 1

Experimente esta fórmula de matriz ¹ :

=LINEST(INDEX(A1:B6,N(IF(1,SMALL(IF(MMULT(0+(ISNUMBER(A1:B6)),{1;1})=2,ROW(A1:B6)-MIN(ROW(A1:B6))+1),ROW(INDIRECT("1:"&COUNT(1/(MMULT(0+(ISNUMBER(A1:B6)),{1;1})=2))))))),2),INDEX(A1:B6,N(IF(1,SMALL(IF(MMULT(0+(ISNUMBER(A1:B6)),{1;1})=2,ROW(A1:B6)-MIN(ROW(A1:B6))+1),ROW(INDIRECT("1:"&COUNT(1/(MMULT(0+(ISNUMBER(A1:B6)),{1;1})=2))))))),1))

¹ _{Fórmulas de matriz não são inseridas da mesma forma que as fórmulas 'padrão'. Ao invés de pressionar apenas ENTER , você primeiro pressiona CTRL e SHIFT , e somente então pressiona ENTER . Se você fez isso corretamente, você notará que o Excel coloca chaves entre {} ao redor da fórmula (embora não tente inserir manualmente você mesmo).}

    

Answer 2

Eu sou o autor do blog vinculado à pergunta.

Alterei o UDF do LinestGap para que ele ignore qualquer linha com erro "" ou célula vazia em qualquer coluna. A planilha revisada pode ser baixada em: link