Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match function...random search? | Excel Worksheet Functions | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
How to Calculate a sum between a rolling data range. | Excel Discussion (Misc queries) | |||
Countif for specific cells rather than a range ???? | Excel Worksheet Functions | |||
Displaying value of specific cell within a range, with IF function...? | Excel Discussion (Misc queries) |