min/max over moving range

[puzzlelover22]

Hi Everyone,

Currently, I have two cells that allow users to pick a start and end date (by way of a drop down list) to view different types of data. I'd like to write a formula that returns the maximum value over that range. The range can change, which is the tricky part. Any ideas?

[Cerbera]

Hi,

Can you provide a workbook with some sample data in so that we can see how it is laid out?

If you can define the range, you can then simply use =MAX(A1:A10), etc. Obviously the range (in this case A1:A10) is what you need to determine.

In order to advise on how to do that, it would help to see how the data is laid out. Often using a dynamic named range (e.g. =OFFSET($A$1,0,0,COUNTA($A:$A),1)) is a good way around this, but will need to see the data to confirm it.

[puzzlelover22]

Forum sample.xlsx

Thanks for the help! I've attached an example (hopefully)

[Tony Valko]

I didn't download your file.

Try to adapt this to suit your needs.

A1:A100 = dates
B1:B100 = number values

D1 = drop down start date
E1 = drop down end date

This array formula** for the max value within the date range:

=MAX(IF(A1:A100>=D1,IF(A1:A100<=E1,B1:B100)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Ursul]

As mentioned in the previous post his is what you are looking for (I was just working on this when that post showed up), but you would need to change the latter part of the formula to show "C" or "D" if looking for West Virginia or US results.

MIN & MAX.xlsx

[boopathiraja]

Hi, Check it out the attachmet

[kvsrinivasamurthy]

Pl see attached file.
Formula

Please Login or Register  to view this content.

[FlameRetired]

Does this look like what you are after?

[FlameRetired]

Some second thoughts on the file I posted above. My original solution took your example range to be Q1 2006 inclusive.

This would produce a range of 2 years plus one quarter........an unlikely query. So, I'm posting an updated version of that file.