help with macros for getting daily averages from 10 minute increment data - MS2000

[feoj]

hi - i am spending endless hours of cutting, copying, pasting and
averaging in excel and am hoping someone can help me with a macros. i
am using microsoft excel millenium edition.

i have many worksheets of stream flow data. this data is set up with a
date in one cell, the next cell notes the 10 minute increment, and the
next cell the average streamflow for that 10 minute increment of the
day. there are many entries for the same date because it is in 10
minute increments. what i need is a daily average for each day.

ex: of data set up
10/2/03 00:10 .25
10/2/03 00:20 .34
10/2/03 00:30 .76

etc., for a whole year, and i need daily averages

any help is GREATLY appreciated!!
jen

 

[Keith R]

How about an average array formula?
(untested)

=AVERAGE(IF(A1:A10000=37896,C1:C10000,"")

where 37896 = the numeric value excel uses for the date 10/02/03

and enter it as an array formula, e.g. [Ctrl-Shift-Enter]

If you enter it on a new sheet (referencing the source data sheet) you
could also use add a reference pointing to incremental date values (37896,
37897, 37898, etc) in adjacent cells, and autofill the formula down so you
end up with one line per day of the year
A B
37896 =AVERAGE(IF(Sheet1!A$1:A$10000=A1,Sheet1!C$1:C$10000,"")
37897 =AVERAGE(IF(Sheet1!A$1:A$10000=A1,Sheet1!C$1:C$10000,"")
37898 =AVERAGE(IF(Sheet1!A$1:A$10000=A1,Sheet1!C$1:C$10000,"")

or something like that...assuming all your source data is on one sheet....
Keith

 

[Bernie Deitrick]

Jen,

Use a pivot table - select all your data, then use Data | Pivot
Table...

Drag the date to the row field, and the data value to the data field.
Use either SUM or AVERAGE for the field setting, depending on whether
your desired average is the average of the 10 minute values or the sum
of the 10 minute values.

HTH,
Bernie
MS Excel MVP