LINEST with criteria

[rhinofeeder]

Hi,
I'm trying to figuring out if it's possible to use linest with a conditional array. I've attached a similar spreadsheet with less data than the one I'm using. Basically, I'd like the known y's and x's to come only from a unique reference (ex. WIDGET). If column A has multiple values with "WIDGET" and column B has multiple corresponding y data, how can I change LINEST to reference just 1 unique value from column A (ex Widget) but sort's through all of that values y data?
I have tried.

=LINEST(IF($A$2:$A$7=E2,$B$2:$B$7,""),IF($A$2:$A$7=$E$2,$C$2:$C$7,""))

Thanks in advance!
LINEST example.xlsx

[MrShorty]

I have not found a similar way to do this. The LINEST() function just cannot operate with blanks, text, or boolean values anywhere in either the known_y or known_x arrays. One way or another, you need to extract the desired data into another array/range.

The easiest approach might be a filter approach - advanced filter in particular. With the correct settings in the advanced filter, you can have Excel extract the desired subset of the data from the main database, then place that subset into another block of cells. LINEST() can then be entered referring to that block of cells. If you are unfamiliar with the advanced filter function, I would suggest a tutorial similar to this one: http://www.contextures.com/xladvfilter01.html

[Kaper]

Instead of LINEST (it requires y and x ranges to be continous) use two separate array formulas*:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

of course for SPROCKET in sample data you will get an error, because you have only one x;y point

*) ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.