Converting statistics

[therd]

I have a master spreadsheet with data from several years and that data is
linked to a statistics spreadsheet. I need to pull data from 2006 and their
adjoining stats. Is there a way to convert the overall stats from the master
spreadsheet into just 2006 data?

 

[Gary''s Student]

Use AutoFilter to display only those rows with 2006 data. Then copy/paste.

 

[therd]

I can filter the data from the master spreadsheet. I need to convert the
master statistics on my statistics worksheet to just show stats for 2006.

 

[Shane Devenshire]

Hi,

You are going to need to show us an example of the data layout on the source
sheet and on the summary sheet so we can be more helpful.

We need to know what statistics you are calculating. For example it the
data is layed out with a column for dates and another for values you could
use something like this

Replace =AVERAGE(B1:B1000) with the array

=AVERAGE(IF((A1:A1000>=--"1/1/2009")*(A1:A1000<=--"12/31/2009"),B1:B1000,""))

To make it an array press Shift+Ctrl+Enter to enter it.


=

 

[Shane Devenshire]

Hi again,

In 2007 you could use

=AVERAGEIFS(B1:B1000,A1:A1000,">="&C1,A1:A1000,"<="&D1)

I have used a reference to cells C1 and D1, inwhich you would enter the date
1/1/2009 and 12/31/2009. You could also use cell references in the previous
formula I sent. I'm just showing two approaches.

 

[therd]

In the Master Spreadsheet I keep statistics of the county that each customer
is from. The statistics are kept on a separate worksheet within the same
workbook and the formula is such: =COUNTIF(Roster!N:N,"Mason"). When I cut
and paste the filtered 2006 customers in a spreadsheet called Roster 2006 and
also copy the statistics page into the same workbook, do I have to change
each link in the statistics one at a time to
=COUNTIF(Roster2006!N:N,"Mason"), or can all link changes be done
simultaneously?