| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LINEST with filtered data
Duh,
That seems to have sorted it. Thanks "Peo Sjoblom" wrote: You need to enter it with ctrl + shift & enter as opposed to enter only -- Regards, Peo Sjoblom "Shanx" wrote in message ... I think I understand what you are trying to do. Basically if the row is visible, the subtotal will produce a 1 (i.e. TRUE). Then you are using the slope() with nested if() to calculate the slope of the visible rows. I'm assuming that "=SLOPE(E2:E9,IF(F2:F9,D2:D9))" is functioning as an array function. Is that correct? When I try this formula the if() function doesn't seem to work correctly and returns #VALUE! This may be my inexperience with array functions Thanks, David "Lori" wrote: The subtotal function can return stats like count, mean and variance on filtered data. So one way to get regression stats is to add a helper column by filling down in column F: =SUBTOTAL(3,A2) Then you can use, =SLOPE(E2:E9,IF(F2:F9,D2:D9)) and similar formulas for INTERCEPT,RSQ, STEYX or FORECAST. "Shanx" wrote: I am wondering if there is a way to use the LINEST function with data that is being filtered. The purpose of this is for a large set of data (~5000 rows x 35 columns) where I have a xy scatter based on two columns. This graph also has a linear trendline with equations and r2 value on the graph. Currently I have used advanced filters to create subsets of data based on various columns (not the columns being graphed). For each of these subset that i have created (in a new location) i used LINEST and generated a new xy scatter plot, to which i have included the standard error and number of data points (from the LINEST calculation). With this large dataset, creating new subsets whenever new data is entered would be very time consuming. So, I would like to find a way to use LINEST to calculate the slope, intercept, r2 and number of features in the filtered data set (i.e. only the visible rows) My data would look something like this (on a larger scale obviously): Vendor Product Year PredictedValue Actual Value A X 2000 10 14 A X 2000 13 17 B X 2001 15 12 B X 2001 19 22 C Y 2000 14 19 C Y 2000 50 44 C Y 2001 40 33 C Y 2001 29 31 So basically, i may want to filter for all of one product, or year, or combination thereof (and others in my complete data set), and then have LINEST calculated for only the visible data. Hope this makes sense. Thanks in advance, David |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linest function: data not being selected in formula | Excel Worksheet Functions | |||
| Linest function: data selection problems | Excel Worksheet Functions | |||
| Linest/slope functions with with different data ranges | Excel Worksheet Functions | |||
| Linest: problems using broken reference data | Excel Worksheet Functions | |||
| How to use linest with variably sized data arrays? | Excel Worksheet Functions | |||
Hybrid Mode
