Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use AUTOFILTER to update FREQUENCY distribution
I have the following data and have created the FREQUENCY distribution below it.
Ideally, what I want is to be able to see the frequency distribution and use AUTOFILTER on the dates to see the FREQUENCY distribution is for various dates or combinations of dates. However, the frequency distribution doesn't seem to get updated when the autofilter is applied. Ideas and comments welcome! Code:
date category 01/01/2008 1 01/01/2008 1 01/01/2008 2 01/01/2008 4 02/02/2008 1 02/02/2008 2 02/02/2008 3 02/02/2008 3 03/03/2008 2 03/03/2008 4 Freq distribution: cat frequency 1 3 2 3 3 2 4 2 Ideal output when filtered on the a single date Filtered for date: 01/01/2008 cat frequnecy 1 2 2 1 3 0 4 1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use AUTOFILTER to update FREQUENCY distribution
Have you tried a PivotTable?
May be better suited. Gord Dibben MS Excel MVP On Wed, 10 Dec 2008 16:50:01 -0800, zerocred wrote: I have the following data and have created the FREQUENCY distribution below it. Ideally, what I want is to be able to see the frequency distribution and use AUTOFILTER on the dates to see the FREQUENCY distribution is for various dates or combinations of dates. However, the frequency distribution doesn't seem to get updated when the autofilter is applied. Ideas and comments welcome! Code:
date category 01/01/2008 1 01/01/2008 1 01/01/2008 2 01/01/2008 4 02/02/2008 1 02/02/2008 2 02/02/2008 3 02/02/2008 3 03/03/2008 2 03/03/2008 4 Freq distribution: cat frequency 1 3 2 3 3 2 4 2 Ideal output when filtered on the a single date Filtered for date: 01/01/2008 cat frequnecy 1 2 2 1 3 0 4 1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use AUTOFILTER to update FREQUENCY distribution
You could add a "count" column using =SUBTOTAL(3,A2) filled down from row 2,
this returns 1 if the row is visible and 0 if it is filtered. Now you can fill down in the "freqüency" column use something like: =SUMIF(category,cat,count) "zerocred" wrote: I have the following data and have created the FREQUENCY distribution below it. Ideally, what I want is to be able to see the frequency distribution and use AUTOFILTER on the dates to see the FREQUENCY distribution is for various dates or combinations of dates. However, the frequency distribution doesn't seem to get updated when the autofilter is applied. Ideas and comments welcome! Code:
date category 01/01/2008 1 01/01/2008 1 01/01/2008 2 01/01/2008 4 02/02/2008 1 02/02/2008 2 02/02/2008 3 02/02/2008 3 03/03/2008 2 03/03/2008 4 Freq distribution: cat frequency 1 3 2 3 3 2 4 2 Ideal output when filtered on the a single date Filtered for date: 01/01/2008 cat frequnecy 1 2 2 1 3 0 4 1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use AUTOFILTER to update FREQUENCY distribution
Thanks Lori - this does the trick exactly!
"Lori" wrote: You could add a "count" column using =SUBTOTAL(3,A2) filled down from row 2, this returns 1 if the row is visible and 0 if it is filtered. Now you can fill down in the "freqüency" column use something like: =SUMIF(category,cat,count) "zerocred" wrote: I have the following data and have created the FREQUENCY distribution below it. Ideally, what I want is to be able to see the frequency distribution and use AUTOFILTER on the dates to see the FREQUENCY distribution is for various dates or combinations of dates. However, the frequency distribution doesn't seem to get updated when the autofilter is applied. Ideas and comments welcome! Code:
date category 01/01/2008 1 01/01/2008 1 01/01/2008 2 01/01/2008 4 02/02/2008 1 02/02/2008 2 02/02/2008 3 02/02/2008 3 03/03/2008 2 03/03/2008 4 Freq distribution: cat frequency 1 3 2 3 3 2 4 2 Ideal output when filtered on the a single date Filtered for date: 01/01/2008 cat frequnecy 1 2 2 1 3 0 4 1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use AUTOFILTER to update FREQUENCY distribution
I did but the it turns ou that the numbers are actually decimals so there are
too many and they need to be divided up into categories. There are about 65000 rows in the real spreadsheet! "Gord Dibben" wrote: Have you tried a PivotTable? May be better suited. Gord Dibben MS Excel MVP On Wed, 10 Dec 2008 16:50:01 -0800, zerocred wrote: I have the following data and have created the FREQUENCY distribution below it. Ideally, what I want is to be able to see the frequency distribution and use AUTOFILTER on the dates to see the FREQUENCY distribution is for various dates or combinations of dates. However, the frequency distribution doesn't seem to get updated when the autofilter is applied. Ideas and comments welcome! Code:
date category 01/01/2008 1 01/01/2008 1 01/01/2008 2 01/01/2008 4 02/02/2008 1 02/02/2008 2 02/02/2008 3 02/02/2008 3 03/03/2008 2 03/03/2008 4 Freq distribution: cat frequency 1 3 2 3 3 2 4 2 Ideal output when filtered on the a single date Filtered for date: 01/01/2008 cat frequnecy 1 2 2 1 3 0 4 1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use AUTOFILTER to update FREQUENCY distribution
A little snag: I used the =SUBTOTAL(3,A2) which can identify the column is visible or not (1 or 0) - however, the =SUMIF(category,cat,count) only works for whole numbers/values in the category list, my data has floating point numbers so the SUMIF didn't work. Workaround: What I did was create a column (say D2) =SUBTOTAL(3,A2) and another (say e2) =IF(D2=1, A2, -10)... The -10 forces the hidden values out of range of my distribution (0.0-1.0) and can be filtered out in the =FREQUENCY () part. Using =IF(D2=1, A2, "") - setting it to an empty cell didn't work as the =FREQUENCY() fell over with #error. It works but it takes several minutes to run (if you change any filter) as the spreadsheet is about 65000 rows. Thanks for the help! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use AUTOFILTER to update FREQUENCY distribution
Yes, the FREQUENCY method should work or you could try one of these:
=SUMIF(category,"<"&cat+1,count)-SUMIF(category,"<"&cat,count) =SUM(SUMIF(category,"<"&cat+{1,0},count)*{1,-1}) but as you say such formulas are very slow with large datasets. Maybe you could add an INT(category) column for use with a pivot table? With the volume of data you have, I would suggest saving it to a text file or database and using a query along the lines of: SELECT [Category],COUNT([date]) FROM MyDataTable GROUP BY [Category] HAVING [Date]=[Choose a date?] where you could enter the date parameter into a cell. "zerocred" wrote: A little snag: I used the =SUBTOTAL(3,A2) which can identify the column is visible or not (1 or 0) - however, the =SUMIF(category,cat,count) only works for whole numbers/values in the category list, my data has floating point numbers so the SUMIF didn't work. Workaround: What I did was create a column (say D2) =SUBTOTAL(3,A2) and another (say e2) =IF(D2=1, A2, -10)... The -10 forces the hidden values out of range of my distribution (0.0-1.0) and can be filtered out in the =FREQUENCY () part. Using =IF(D2=1, A2, "") - setting it to an empty cell didn't work as the =FREQUENCY() fell over with #error. It works but it takes several minutes to run (if you change any filter) as the spreadsheet is about 65000 rows. Thanks for the help! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use AUTOFILTER to update FREQUENCY distribution
Thats a couple of good ideas - the data originally comes from a Access
database, in fact it is way over 65,000 records! I can probably try rounding the floating numbers to 1 decimal place or so (and/or scale them up to INTs if necessary) and then do it all in a query. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Median for frequency distribution | Excel Worksheet Functions | |||
How can i do a frequency distribution by machine automatically? | Excel Worksheet Functions | |||
Frequency distribution | Excel Worksheet Functions | |||
Frequency distribution | Excel Discussion (Misc queries) | |||
Sorting - Frequency distribution. | Excel Discussion (Misc queries) |