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 determine the MODE of 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

hi Chris

I think what you need is a CSE formula as explained here
-www.mrexcel.com/tip011.shtml

The formula probably goes something like this

=MODE(IF(E2:E14=A2,IF(E2:E14<=B2,F2:F14,""),""))

Should make some more sense if you have a look at the link above.

HTH

Barry


"ChrisM" wrote:

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 determine the MODE of 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