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!!!

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!!!

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!!!

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!!!