average for new date

[Guest]

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!

 

[Guest]

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

 

[Hans Knudsen]

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,"",SUMIF(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" <[email protected]> skrev i en
meddelelse
news:[email protected]...

 

[Guest]

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

 

[Guest]

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!

 

[Guest]

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

 

[Ragdyer]

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.

 

[Ragdyer]

Appreciate the feed-back.