The layout has certainly changed for the better.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tom Ogilvy" wrote in message
...
Bob has a good thought (better than mine), but for an average, you would
need to do


=Average(If((Sheet2!$A$1:$A$20000=Sheet1!A1)*(She et2!$A$1:$A$20000<=Sheet1!
B1),Sheet2
!$B$1:$B$20000))

Entered with Ctrl+shift+Enter rather than just enter since it is an array
formula. Obviously change 20000 to reflect the extent of your data.


Since it is a single column condition you could also do


=(Sumif(Sheet2!$A$1:$A$20000,"="&Sheet1!A1,Sheet2 !$B$1:$B$20000)-Sumif(Shee

t2!$A$1:$A$20000,""&Sheet1!B1,Sheet2!$B$1:$B$2000 0))/=(Countif(Sheet2!$A$1:
$A$20000,"="&Sheet1!A1)-Counif(Sheet2!$A$1:$A$20000,""&Sheet1!B1))

If you have a lot of rows on Sheet1 and a lot of rows on Sheet2, you will
find this longer formula much faster to calculate than the array formula
which can get very slow.

In either case, then copy the formula down the column in sheet1

--
Regards,
Tom Ogilvy




"Bob Phillips" wrote in message
...
Chris,

Try



=SUMPRODUCT(--(Sheet2!A1:A20=Sheet1!A1),--(Sheet2!A1:A20<=Sheet1!B1),Sheet2
!B1:B20)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ChrisM" wrote in message
...
Hi all,

I have two spreadsheets. The first spreadsheet contains two columns,
one
for
start date/time (in serial format) and the second for stop date/time
(in
serial format).

The second spreadsheet contains two columns. One containing a serial
date/time and the other containing a meteorological value (eg.
temperature).

I want Excel to read the start and stop date/time from the first
spreadhseet
and then search the second spreadsheet for all temperature values
which
were
recorded during this time frame.

Lastly, I want Excel to average these values.

Is this possible using a single Excel function?? Or will I have to use
many
nested functions?? Would it be easier to use VBA and if so where would
I
start?? Any help would be greatly appreciated. Thanks

Chris