LINEST with filtered data

[Guest]

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

 

[Guest]

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,D29))

and similar formulas for INTERCEPT,RSQ, STEYX or FORECAST.

 

[Guest]

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,D29))" 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

 

[Peo Sjoblom]

You need to enter it with ctrl + shift & enter as opposed to enter only

 

[Guest]

Duh,

That seems to have sorted it.

Thanks

You need to enter it with ctrl + shift & enter as opposed to enter only

 

[RedSkyRoad]

Hi,

Please explain the "A2" in =SUBTOTAL(3,A2)

If my X-axis data is in H2:H1642 and the Y-axis is in J2:J1642, how would I set-up these formulae?