Averaging a Value Between Two Serial Dates
 

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

Averaging a Value Between Two Serial Dates
 

this has changed quite a bit since you last posted. previously you had time
on sheet two going across several columns.

You should be able to lookup your date/time values using Match

=Average(Offset(Sheet2!$a$1,Match(A2,Sheet2!$A:$A, 1)-1,1,Match(B2,Sheet2!$A:
$A,1)-Match(A2,Sheet2!$A:$A,1)+1,1))

then drag down your columns. You might have to fiddle with it because I
used a 3rd argument of 1 rather than 0 (exact match) to achieve the exact
range you want. But it should get you started.

sheet2 refers to the second sheet. The unqualified A2, B2 refer to the
first sheet and where the formula is located (assume C2 in this example).

--
Regards,
Tom Ogilvy


"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

Averaging a Value Between Two Serial Dates
 

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

Averaging a Value Between Two Serial Dates
 

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

Averaging a Value Between Two Serial Dates
 

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