dabluebery
Board Regular
- Joined
- Sep 22, 2003
- Messages
- 83
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.
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 | ||||||||||
