Calculate max value in specific range
 

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

You have one response to your post in .excel.

NoSpamPlease wrote:

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

--

Dave Peterson

Two ways:

First: - Array formula - commit it by pressing Ctrl-Shift-Enter

Assuming dates & values are in A2:B20
Min date in cell D2
Max date in cell E2

=MAX(--(A2:A20=D2)*--(A2:A20<=E2)*B2:B20)


Second: - Filter the data (Date-Filter..)
Then use SUBTOTAL(4,B2:B20)


"NoSpamPlease" wrote:

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