Problem with complex array formula for input to LINEST

[Wattcommunity]

I'm a newbie to the forum so hi everyone

My question concerns a problem/challenge I have to smooth a contingency table of data in two directions. An approach I'm researching involves splines.

The tables may have missing data, and for ease of copying down, I want to accommodate edge effects.

The task is to get two arrays to feed in to a LINEST array functions. The input arrays for x and y need to be manipulated so they themselves have no blank values. I've managed to get to a point pretty close to the input data using an array function of the form:

=IFERROR(INDEX($B12:$B14,SMALL(IF(ISNUMBER(B12:B14),ROW(B$1:B$3)),{1;2})),"") function [1]

The image shows this nicely. Each function return an array of length 3 - my spline. Blanks go to the bottom. However I need to feed these arrays in to LINEST, by shortening the arrays to omit the blanks. So it would seem I simply need to extract a sub-array, of length equal to the number of numeric values whicj I can always no using a simple count on the input cell range.

I can do this if the array is cast on to the worksheet using, for example: =INDEX(K13:K15,{1;2}), will get me the top two rows where K13:K15 contains the values returned from the function [1].

However, none of this works when I then insert my formula in to LINEST. Oddly I get no error, just zero for m.

So, how do I get I get an array, cropped to the length of populated values, in a form in which LINEST is happy with?


Thanks in advance

Summary

The image shows my array formula working as intended, finding only the cells with y values, correctly matched to the x value. I now need to get a x and y arrays of length equal to the number of y values, and feed these in to LINEST.

Capture.PNG

[XLent]

the below would give you an array of 1 to n where n determined by count

Please Login or Register  to view this content.

thereafter I'm afraid I'm a little lost ... you might need to post a sample file with expected results, manually derived, or wait for someone with sufficient "smarts".

[Wattcommunity]

Thanks XLent, that is indeed a useful to get an array of the required length

I'll battle on but further thoughts on the original problem welcome

[XOR LX]

Hi,

Try my post here:

https://excelxor.com/2016/02/16/crit...-logest-trend/

Give me a shout if you have difficult adapting it to your circumstances.

Regards

[Wattcommunity]

Hi XOR LX

thanks for your response; your detailed exposition of solving the problem was like music to my ears. I'd seen some references to using mode.mult to get an array of numeric values to index but been unable to follow them and your article was so crystal clear so many many thanks.

For others interested, my need was to omit non-numerics on the y value. Here's my solution using XOR LIs download on the problem of criteria with LINEST and other statistical functions

Firstly, I inserted a new column B2:B11 with x values {12,49,70,29,10,96,9,36,34,39}

Cell J3 (ignoring non-numerics):

=LINEST(INDEX(C:C,N(IF(1,MODE.MULT(IF(ISNUMBER(C2:C11)=TRUE,{1,1}*ROW(C2:C11)))))))

Cell K3:L3 ignoring x and y values with no corresponding y (ensuring x,y's are paired)

=LINEST(INDEX(C:C,N(IF(1,MODE.MULT(IF(ISNUMBER(C2:C11)=TRUE,{1,1}*ROW(C2:C11)))))),INDEX(B:B,N(IF(1,MODE.MULT(IF(ISNUMBER(C2:C11)=TRUE,{1,1}*ROW(B2:B11)))))))

Note if were possible to get a y with no x it will fail.


Capture.PNG

[XOR LX]

Glad to hear it!

Cheers