How to use AUTOFILTER to update FREQUENCY distribution

[zerocred]

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!

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

Excel 2003

 

[Gord Dibben]

Have you tried a PivotTable?

May be better suited.


Gord Dibben MS Excel MVP

 

[Lori]

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]

Thanks Lori - this does the trick exactly!

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)

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!

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

Excel 2003

 

[zerocred]

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!

Have you tried a PivotTable?

May be better suited.


Gord Dibben MS Excel MVP

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!

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

Excel 2003

 

[zerocred]

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!

 

[Lori]

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]

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.