Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average number between dates
I have a column of dates in Sheet1 Column A. In Sheet 2 Columns A thru J, I
have a list of random numbers. Is there a way to average each number that comes up. For example: Sheet1 has a date of 8/1/09 with a "1" on Sheet2.(=COUNTIF(Sheet1!B:F,"1") This gets me the number of "1"s Then the "1" does not show up again untill 8/5/09, then again on 8/6/09. A total of 3 "1"'s. I need a formula that shows the average time from the first "1" to the last "1". Thanks in advance!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average number between dates
Is it possible for a number to appear on a date more than once?
For example: 8/1/2009.....1.....5.....1.....3 1 appears twice on the same day. -- Biff Microsoft Excel MVP "Richard" wrote in message ... I have a column of dates in Sheet1 Column A. In Sheet 2 Columns A thru J, I have a list of random numbers. Is there a way to average each number that comes up. For example: Sheet1 has a date of 8/1/09 with a "1" on Sheet2.(=COUNTIF(Sheet1!B:F,"1") This gets me the number of "1"s Then the "1" does not show up again untill 8/5/09, then again on 8/6/09. A total of 3 "1"'s. I need a formula that shows the average time from the first "1" to the last "1". Thanks in advance!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average number between dates
No. There is never the same number on the same day. Thanks so much for your
help!! "T. Valko" wrote: Is it possible for a number to appear on a date more than once? For example: 8/1/2009.....1.....5.....1.....3 1 appears twice on the same day. -- Biff Microsoft Excel MVP "Richard" wrote in message ... I have a column of dates in Sheet1 Column A. In Sheet 2 Columns A thru J, I have a list of random numbers. Is there a way to average each number that comes up. For example: Sheet1 has a date of 8/1/09 with a "1" on Sheet2.(=COUNTIF(Sheet1!B:F,"1") This gets me the number of "1"s Then the "1" does not show up again untill 8/5/09, then again on 8/6/09. A total of 3 "1"'s. I need a formula that shows the average time from the first "1" to the last "1". Thanks in advance!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average number between dates
See if this does what you want...
A1:A20 = dates B1:E20 = random numbers H1 = lookup number = 1 H2 = formula = count =COUNTIF(B1:E20,H1) H3 = formula to get the average number of days Array entered** : =IF(H2<2,H2,MAX(1,(MAX(IF(B1:E20=H1,A1:A20))-MIN(IF(B1:E20=H1,A1:A20)))/H2)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Richard" wrote in message ... No. There is never the same number on the same day. Thanks so much for your help!! "T. Valko" wrote: Is it possible for a number to appear on a date more than once? For example: 8/1/2009.....1.....5.....1.....3 1 appears twice on the same day. -- Biff Microsoft Excel MVP "Richard" wrote in message ... I have a column of dates in Sheet1 Column A. In Sheet 2 Columns A thru J, I have a list of random numbers. Is there a way to average each number that comes up. For example: Sheet1 has a date of 8/1/09 with a "1" on Sheet2.(=COUNTIF(Sheet1!B:F,"1") This gets me the number of "1"s Then the "1" does not show up again untill 8/5/09, then again on 8/6/09. A total of 3 "1"'s. I need a formula that shows the average time from the first "1" to the last "1". Thanks in advance!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average days between dates | Excel Worksheet Functions | |||
Average price between dates | Excel Worksheet Functions | |||
Average price between dates | Excel Worksheet Functions | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Average between two dates | Excel Discussion (Misc queries) |