Run regression on filtered cells only?

**Conzo1000** · 02-16-2018, 07:27 PM

I have a large dataset with info on mutual funds (20,761 rows) and wish to run a regression on a sub-sample of it. In the screenshot below, in column P, I have input an "IF" function which displays a "1" when the fund in question is a Large Growth fund - I have 8 other columns with different fund types, each with their own "IF" function to display a "1" when the fund is that specific type. These 1's are used in my regression as dummy variables.

My question is, how do I run a regression on the rows that display a "1" for Large Growth funds, excluding the rows that contain a "0"? I have tried filtering the data and then running the regression - however my input range is still the whole dataset rather than just the data that is visible after filtering. The large growth funds are not close to each other (e.g. one could be in row 21, one could be in row 450, one in row 1211, etc) so I cannot go through the whole data manually to click on each row for the input range.

Is there a way I can run a regression using only the filtered results?

Screen Shot 2018-02-16 at 23.19.05.png

**MrShorty** · 02-16-2018, 09:24 PM

You don't say how you are trying to do the regression. Many of the regression tools, especially those based on the LINEST() function (including the data analysis-> regression utility) will need you to completely extract the desired data/remove the undesired data from the source range.

1) Something like autofilter only hides data, it does not remove the data. You might try using advanced filter (http://www.excel-easy.com/examples/advanced-filter.html or http://www.contextures.com/xladvfilter01.html ) where you have the option to copy the filtered list to another range.

2) Something formula that uses a complicated mix of INDEX() and MODE.MULT() and IF(): https://www.excelforum.com/excel-for...-function.html

3) Another formula based that uses the OFFSET() and MATCH() and COUNTIFS() functions https://www.excelforum.com/excel-gen...data-base.html

Or any other strategy that you like for extracting the data/removing unwanted data from the input range to the regression utility that you want to use.

Run regression on filtered cells only?

LinkBack

Thread Tools

Rate This Thread

Display

Run regression on filtered cells only?

Re: Run regression on filtered cells only?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Filtered Cells To Automatically Move Visible Cells To Top Of Table..?

Draw lines across an autofiltered range of cells, but not across the filtered out cells

[SOLVED] Count cells containing Dates (greater than 2014) based on Filtered Cells

Pasting into Filtered cells affects other cells also

Count unique cells after filtered cells

When I edit cells in a filtered range other cells also change. Why?

Stop Formulas working on hidden cells / filtered cells

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions