Calculate max value in specific range

[VirtualReal]

Hi all,


I've got a table with specific date values (start & end dates) like
this:

3/23/00 Start
3/26/00 End

etc.

note: the length of days will vary from time tot time.

--

I also have a table with all date values and corresponding numbers,
like this:

Column:
A B
Row:
1 3/23/00 1101.16
2 3/24/00 1106.16
3 3/25/00 1143.07 (=MAX)
4 3/26/00 1137.54

--

How can I calculate the max value from all those corresponding numbers
between a Start- & End date? (1143.07 in this case)
I've already tried the Vlookup and Max function but that only refers to
1 value (as far as I know)

Any suggestions? Thanks in advance!

- Jim

 

[Jerry W. Lewis]

=MAX(IF((A1:A4>startdate)*(A1:A4<enddate),B1:B4))

array entered (Ctrl+Shift+Enter)

Jerry