Lookups & removing errors while using linest()

[catgeo]

Hi,

I'm using linest to obtain the slope of a series of data.
My data is three colums of X, Y and 'category'.

e.g.
X Y 'category 1'
X Y 'category 1'
X Y 'category 2'
X Y 'category 3'
X Y 'category 3'
X Y 'category 2' ect. ect.

What I'm trying to do is look up the specific categories X and Y variables and find the slope of that 'category'.
So far I have got to the point where my linest function if calculating the slope of a X & Y array that contains the categories data but the rest of the array is zeros or an error. I need a way to filter out the zeros/errors.

My code so far is (or a variation of):

=LINEST( "X-array" *(( "category array" = "category n" )) , "Y-array" * (( "category array" = "category n")))

I've also tried using the aggregate (small) function, but after the aggregate (small) function calculates it returns the smallest number in the array and not the entire array, which I need for linest to calculate with.

Can anyone please help?
Thank you for you help

 

[XOR LX]

Hi,

Assuming the data as you give it is in A1:C6, and that you put your choice of category, e.g. "category 1", in E1, array formula**:

=LINEST(INDEX(A:A,N(IF(1,MODE.MULT(IF(C1:C6=E1,{1,1}*ROW(A1:A6)))))),INDEX(B:B,N(IF(1,MODE.MULT(IF(C1:C6=E1,{1,1}*ROW(A1:A6)))))))

https://excelxor.com/2016/02/16/criteria-with-statistical-functions-growth-linest-logest-trend/

Regards



**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[catgeo]

Awesome! Cheers mate works perfectly!!

 

[XOR LX]

You're welcome!

Cheers