software

Hello,

I keep a list of records that come in on a daily basis in a worksheet
called 'Records'. The date is in the first column and other information
about that record is in columns 2 through 10. On any particular day I
might have several records, but I don't have entries every day.

On another worksheet, I have functions which total up the number of
records that come in each month. This total is computed using equations
like this (this example is for January 2006):

=SUMPRODUCT(--(MONTH('Records'!$A$6:$A$10007)=1),--(YEAR('Records'!$A$6:$A$10007)=2006))

(I rec'd help from this newsgroup for this function)

What I would now like to do is add another function that calculates the
average records per day, but using the number of days that records were
rec’d, NOT the total number of days in the month. So, if a total of 93
records were rec’d on 10 days in January 2005, the total would be
divided by 10, NOT 31, giving a daily average of 9.3. This number for
the denominator can be arrived at by finding the number of “unique”
dates matching the particular month from column 1 of the master
worksheet, but I don’t know how to do this.

Can anyone help me with this?

Thanks,

Kevin

there will only be a date if you have received data??But you receive more
than one data sets per day?
--
paul
remove nospam for email addy!
quot;Kevinquot; wrote:

gt; Hello,
gt;
gt; I keep a list of records that come in on a daily basis in a worksheet
gt; called 'Records'. The date is in the first column and other information
gt; about that record is in columns 2 through 10. On any particular day I
gt; might have several records, but I don't have entries every day.
gt;
gt; On another worksheet, I have functions which total up the number of
gt; records that come in each month. This total is computed using equations
gt; like this (this example is for January 2006):
gt;
gt; =SUMPRODUCT(--(MONTH('Records'!$A$6:$A$10007)=1),--(YEAR('Records'!$A$6:$A$10007)=2006))
gt;
gt; (I rec'd help from this newsgroup for this function)
gt;
gt; What I would now like to do is add another function that calculates the
gt; average records per day, but using the number of days that records were
gt; rec’d, NOT the total number of days in the month. So, if a total of 93
gt; records were rec’d on 10 days in January 2005, the total would be
gt; divided by 10, NOT 31, giving a daily average of 9.3. This number for
gt; the denominator can be arrived at by finding the number of “unique”
gt; dates matching the particular month from column 1 of the master
gt; worksheet, but I don’t know how to do this.
gt;
gt; Can anyone help me with this?
gt;
gt; Thanks,
gt;
gt; Kevin
gt;

Hi!

Do you need to specify the year as part of the criteria?

In the sample formula posted you use the year.

This formula will count the unique date entries for Jan 2006:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=SUM(N(FREQUENCY(IF((MONTH(A1:A20)=1)*(YEAR(A1:A20 )=2006),MATCH(A1:A20,A1:A20,0)),MATCH(A1:A20,A1:A2 0,0))gt;0))

So, to get the average, just combine the 2 formulas: (still array entered)

=SUMPRODUCT(--(MONTH(A1:A20)=1),--(YEAR(A1:A20)=2006))/SUM(N(FREQUENCY(IF((MONTH(A1:A20)=1)*(YEAR(A1:A20) =2006),MATCH(A1:A20,A1:A20,0)),MATCH(A1:A20,A1:A20 ,0))gt;0))

No error checking!

Biff

quot;Kevinquot; gt; wrote in message
...
gt; Hello,
gt;
gt; I keep a list of records that come in on a daily basis in a worksheet
gt; called 'Records'. The date is in the first column and other information
gt; about that record is in columns 2 through 10. On any particular day I
gt; might have several records, but I don't have entries every day.
gt;
gt; On another worksheet, I have functions which total up the number of
gt; records that come in each month. This total is computed using equations
gt; like this (this example is for January 2006):
gt;
gt; =SUMPRODUCT(--(MONTH('Records'!$A$6:$A$10007)=1),--(YEAR('Records'!$A$6:$A$10007)=2006))
gt;
gt; (I rec'd help from this newsgroup for this function)
gt;
gt; What I would now like to do is add another function that calculates the
gt; average records per day, but using the number of days that records were
gt; rec’d, NOT the total number of days in the month. So, if a total of 93
gt; records were rec’d on 10 days in January 2005, the total would be divided
gt; by 10, NOT 31, giving a daily average of 9.3. This number for the
gt; denominator can be arrived at by finding the number of “unique” dates
gt; matching the particular month from column 1 of the master worksheet, but I
gt; don’t know how to do this.
gt;
gt; Can anyone help me with this?
gt;
gt; Thanks,
gt;
gt; Kevin