LINEST using Filtered Data

[dabluebery]

Hi. 14 years since I've posted here -- maybe a record? I've been reading threads that have similar goals but I'm handicapped by (a) the differences and (b) my ability. As far as my ability goes, I know more than anyone I know about Excel but less than everyone here.

The end goal of my spreadsheet is to predict the cost of something for a client instantly, compared with a lengthy estimating process that can take two weeks depending backlog. I use a table of past quotes with various criteria to try and find/filter similar quotes, and then use regression analysis to come up with a formula using a trendline and spit out a new price.

Until now I've been scraping the trendline formula from the graph's text-box. Yuck. I want to build the spreadsheet to extract these values in formulas, compare multiple regression analyses (usually 'power' works, but I'd use whatever has best r squared values), and basically automate this process. Enter LINEST array function.

The problem is that sometimes I'm using large sections of the table and sometimes I'm filtering down to 4 or 5 rows. I can't make LINEST work as an array function using filtered data and I'm going to be adding new rows of sample data continuously. In the example I've filtered for rows with less than 12 widgets (filter Row B), and my scatter plot is always going to be Row F as the (Y value) and Row E as the (X value) If I try to LINEST the data in this table directly (it would be a different sheet), I get errors.

This is where I'm at. I see advice in similar threads that ranges from using index() and match(), to VBA, to copying the filtered data into a dummy array, to using advanced filtering. I'm in over my head, and would be open to advice that simplifies this process, especially if I'm able to avoid VBA. Thank You and please be gentle, I've been combing similar threads but can't make sense of it considering I might be approaching this problem incorrectly. Most of my excel I learned 20+ years ago in HS.

Widget Analysis.xlsx
	A	B	C	D	E	F	G	H
1	Record #	Widgets	Widget Size	Cost	Widgets* Size	Cost per (widget * size)	Region	Container Type
22	21	6	26.50	$ 195,529.00	159	$ 1,229.74	USA	D
36	35	6	2.00	$ 100,388.00	12	$ 8,365.67	AFRICA	S
37	36	6	5.00	$ 110,897.00	30	$ 3,696.57	USA	S
38	37	4	2.00	$ 78,979.00	8	$ 9,872.38	EUROPE	S
39	38	4	5.00	$ 88,364.00	20	$ 4,418.20	AUSTRALIA	S
58	57	8	20.00	$ 234,937.00	160	$ 1,468.36	EUROPE	D
74	73	10	5.00	$ 189,495.00	50	$ 3,789.90	AUSTRALIA	S
90	89	6	5.00	$ 144,889.00	30	$ 4,829.63	ASIA	S
91	90	9	5.00	$ 159,456.00	45	$ 3,543.47	AFRICA	S
119	118	10	6.00	$ 96,742.00	60	$ 1,612.37	AUSTRALIA	S
148	147	4	8.00	$ 67,655.00	32	$ 2,114.22	EUROPE	S
149	148	6	8.00	$ 82,061.00	48	$ 1,709.60	AUSTRALIA	S
170	169	10	8.00	$ 166,468.00	80	$ 2,080.85	ASIA	S
171	170	8	10.00	$ 305,688.00	80	$ 3,821.10	AFRICA	S
183	182	4	10.50	$ 99,904.00	42	$ 2,378.67	EUROPE	S
184	183	6	10.50	$ 136,035.00	63	$ 2,159.29	AUSTRALIA	S
202	201	10	12.00	$ 267,100.00	120	$ 2,225.83	USA	S
Data

 

[cyrilbrd]

Hi,
Just a thought, why not using Name Manager to target your known_ys and your known_xs?
then use something along:
Y_list =OFFSET(tab_name!$A$2,0,0,COUNTIF(tab_name!$A:$A,">-1"))
Given that your data of interest is in A and that the first cell is in row 2.
Repeat for X.
Thus giving =LINEST(Y_list,X_list,TRUE,TRUE)

 

[dabluebery]

Thank you for the response, I will try this and see how it goes.