I've run into a brick wall... I'm trying to return the min and maximum value from a table for a given data set and date subject to the criteria that the date is within a certain range. Can someone help?
Using the attached worksheet, I want to fill in columns B & C on the first tab that return the min top and the max bottom as long as the date in column D is within the date range on the 2nd tab.
Thanks in advance,
In C2 and D2
top perf:
Formula:
Please Login or Register to view this content.
bottom perf:
Formula:Please Login or Register to view this content.
These are entered as arrays using Ctrl+Shift+Enter instead of exiting the cell with Enter.
Copy as far down as needed.
Thanks, but that doesn't quite do it-- I didn't explain well enough. The max / min values also have to be indexed to the part # in each of the sheets. Look at Part #8 on the Data tab-- the max and min values change based on the date. Is there a vlookup component that needs to be added somewhere?
Thanks!
Hi,
Both array formulas:
In B2 and copy down:
=MAX(IF(('Data to look up from'!$A$2:$A$69='Table to fill in'!A2)*('Data to look up from'!$B$2:$B$69<='Table to fill in'!D2)*('Data to look up from'!$C$2:$C$69>='Table to fill in'!D2),'Data to look up from'!$D$2:$D$69))
In C2 and copy down:
=MIN(IF(('Data to look up from'!$A$2:$A$69='Table to fill in'!A2)*('Data to look up from'!$B$2:$B$69<='Table to fill in'!D2)*('Data to look up from'!$C$2:$C$69>='Table to fill in'!D2),'Data to look up from'!$E$2:$E$69))
Regards
AWESOME! Thanks!
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks