I have a table of dates and values with a different number of rows for each
date, and i would like to average over each date no matter how many rows
there are for each date. the data look like:
7/1/06 1
7/1/06 2
7/1/06 3
7/2/06 5
7/2/06 3
7/3/06 9
7/4/06 1
7/4/06 2

so what i need to do is increment the date and calc the average of all the
corresponding values for that date, then go to the next date, etc. there are
26,000 rows of data. Thank you very much!

A not very sophisticated solution but here goes:

Assuming data is sorted by date and starts in row 2:

In C2 (say):


=IF(A2<A1,SUMIF($A$2:$A$9,A2,$B$2:$B$9)/COUNTIF($A$2:$A$9,A2),"")

Change ranges and copy down.

It will calculate average and put in column C against first occurence of a
given date. You can copy/paste special, filter (on "not blank" )to get list
with dates.

HTH


"little bear" wrote:

I have a table of dates and values with a different number of rows for each
date, and i would like to average over each date no matter how many rows
there are for each date. the data look like:
7/1/06 1
7/1/06 2
7/1/06 3
7/2/06 5
7/2/06 3
7/3/06 9
7/4/06 1
7/4/06 2

so what i need to do is increment the date and calc the average of all the
corresponding values for that date, then go to the next date, etc. there are
26,000 rows of data. Thank you very much!

Until someone comes up with a more simple solution you might want
to try the following monster:

=IF(NOT(ISERROR(IF(COUNTIF(A1:A$26000,A1)1,"",SUM IF(A:A,A1,B:B))/IF(COUNTIF(A1:A$26000,A1)1,"",COUNTIF(A:A,A1)))), IF(COUNTIF(A1:A$26000,A1)1,"",SUMIF(A:A,A1,B:B))/IF(COUNTIF(A1:A$26000,A1)1,"",COUNTIF(A:A,A1)),"" )

Hans



"little bear" skrev i en
meddelelse
...
I have a table of dates and values with a different number of rows
for each
date, and i would like to average over each date no matter how
many rows
there are for each date. the data look like:
7/1/06 1
7/1/06 2
7/1/06 3
7/2/06 5
7/2/06 3
7/3/06 9
7/4/06 1
7/4/06 2

so what i need to do is increment the date and calc the average
of all the
corresponding values for that date, then go to the next date,
etc. there are
26,000 rows of data. Thank you very much!

VBA solution:

Averages are written to "Sheet2" with dates


Sub Average_dates()
Dim lrow As Long, r As Long, rr As Long, dte As Date
Dim n As Integer
With Worksheets("Sheet1")
lrow = .Cells(Rows.Count, "A").End(xlUp).Row
r = 2
rr = 1
Do
dte = .Cells(r, "A")
n = Application.CountIf(.Range("A:A"), dte)
rr = rr + 1
Worksheets("Sheet2").Cells(rr, "A") = dte
Worksheets("Sheet2").Cells(rr, "B") = Application.Average(.Cells(r,
"B").Resize(n, 1))
r = r + n
Loop Until r lrow
End With

End Sub

"Hans Knudsen" wrote:

Until someone comes up with a more simple solution you might want
to try the following monster:

=IF(NOT(ISERROR(IF(COUNTIF(A1:A$26000,A1)1,"",SUM IF(A:A,A1,B:B))/IF(COUNTIF(A1:A$26000,A1)1,"",COUNTIF(A:A,A1)))), IF(COUNTIF(A1:A$26000,A1)1,"",SUMIF(A:A,A1,B:B))/IF(COUNTIF(A1:A$26000,A1)1,"",COUNTIF(A:A,A1)),"" )

Hans



"little bear" skrev i en
meddelelse
...
I have a table of dates and values with a different number of rows
for each
date, and i would like to average over each date no matter how
many rows
there are for each date. the data look like:
7/1/06 1
7/1/06 2
7/1/06 3
7/2/06 5
7/2/06 3
7/3/06 9
7/4/06 1
7/4/06 2

so what i need to do is increment the date and calc the average
of all the
corresponding values for that date, then go to the next date,
etc. there are
26,000 rows of data. Thank you very much!

that is so interesting! why does it work? I would think that the avg would
be over the entire range of values in column D rather than only the ones that
meet the date criterion in A2. thanks!

"Toppers" wrote:

A not very sophisticated solution but here goes:

Assuming data is sorted by date and starts in row 2:

In C2 (say):


=IF(A2<A1,SUMIF($A$2:$A$9,A2,$B$2:$B$9)/COUNTIF($A$2:$A$9,A2),"")

Change ranges and copy down.

It will calculate average and put in column C against first occurence of a
given date. You can copy/paste special, filter (on "not blank" )to get list
with dates.

HTH


"little bear" wrote:

I have a table of dates and values with a different number of rows for each
date, and i would like to average over each date no matter how many rows
there are for each date. the data look like:
7/1/06 1
7/1/06 2
7/1/06 3
7/2/06 5
7/2/06 3
7/3/06 9
7/4/06 1
7/4/06 2

so what i need to do is increment the date and calc the average of all the
corresponding values for that date, then go to the next date, etc. there are
26,000 rows of data. Thank you very much!

thank you very much. that is a perfect solution that i can actually
understand.

"Ragdyer" wrote:

Assume your datalist is in A1 to B1000.

Say you enter 7/1/06 at the top of a column, say D1.
Then drag it down to increment it and make a list of individual days, like:
7/1/06
7/2/06
7/3/06
.... etc.

Drag down as far as necessary.

Then in E1, enter this *array* formula:

=AVERAGE(IF(A$1:A$1000=D1,B$1:B$1000))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE, drag the formula down to copy as needed,
OR
*Double Click* on the fill handle of E1, to *automatically* copy the formula
down Column E, as far as you have entered your date list in Column D.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"little bear" wrote in message
...
I have a table of dates and values with a different number of rows for each
date, and i would like to average over each date no matter how many rows
there are for each date. the data look like:
7/1/06 1
7/1/06 2
7/1/06 3
7/2/06 5
7/2/06 3
7/3/06 9
7/4/06 1
7/4/06 2

so what i need to do is increment the date and calc the average of all the
corresponding values for that date, then go to the next date, etc. there
are
26,000 rows of data. Thank you very much!

Assume your datalist is in A1 to B1000.

Say you enter 7/1/06 at the top of a column, say D1.
Then drag it down to increment it and make a list of individual days, like:
7/1/06
7/2/06
7/3/06
.... etc.

Drag down as far as necessary.

Then in E1, enter this *array* formula:

=AVERAGE(IF(A$1:A$1000=D1,B$1:B$1000))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE, drag the formula down to copy as needed,
OR
*Double Click* on the fill handle of E1, to *automatically* copy the formula
down Column E, as far as you have entered your date list in Column D.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"little bear" wrote in message
...
I have a table of dates and values with a different number of rows for each
date, and i would like to average over each date no matter how many rows
there are for each date. the data look like:
7/1/06 1
7/1/06 2
7/1/06 3
7/2/06 5
7/2/06 3
7/3/06 9
7/4/06 1
7/4/06 2

so what i need to do is increment the date and calc the average of all the
corresponding values for that date, then go to the next date, etc. there
are
26,000 rows of data. Thank you very much!

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"little bear" wrote in message
...
thank you very much. that is a perfect solution that i can actually
understand.

"Ragdyer" wrote:

Assume your datalist is in A1 to B1000.

Say you enter 7/1/06 at the top of a column, say D1.
Then drag it down to increment it and make a list of individual days,
like:
7/1/06
7/2/06
7/3/06
.... etc.

Drag down as far as necessary.

Then in E1, enter this *array* formula:

=AVERAGE(IF(A$1:A$1000=D1,B$1:B$1000))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE, drag the formula down to copy as needed,
OR
*Double Click* on the fill handle of E1, to *automatically* copy the
formula
down Column E, as far as you have entered your date list in Column D.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"little bear" wrote in message
...
I have a table of dates and values with a different number of rows for
each
date, and i would like to average over each date no matter how many
rows
there are for each date. the data look like:
7/1/06 1
7/1/06 2
7/1/06 3
7/2/06 5
7/2/06 3
7/3/06 9
7/4/06 1
7/4/06 2

so what i need to do is increment the date and calc the average of all
the
corresponding values for that date, then go to the next date, etc.
there
are
26,000 rows of data. Thank you very much!