Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed with function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed with function
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! "Kevin" wrote: 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 recd, NOT the total number of days in the month. So, if a total of 93 records were recd 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 dont know how to do this. Can anyone help me with this? Thanks, Kevin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help needed with function
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))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))0)) No error checking! Biff "Kevin" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DAverage Function help needed | Excel Worksheet Functions | |||
Desperate Help needed with a function. | Excel Worksheet Functions | |||
function needed | Excel Worksheet Functions | |||
IF function help needed | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |