Maxif and min if

[SonuBhaiya]

Hello friends,
I would like create a max and min formula that would also meet some criteria.
Column A is for Date
Column E is for Price
Cell N5 is period start date
Cell N6 is period end date
I would like to know max and min price within the period.
The formula which i used was
=max(if($a$2:$a$2000>=n5,if($a$2:$a$2000<=n6, "N. A."), "N. A."))
and same formula for min function but the above formula returned value for whole 2000 period instead of look out period
Hope you people will help me out soon
Thanks

 

[Nishant94]

For Max:=

=AGGREGATE(14,6,$E$2:$E$2000/(($A$2:$A$2000>=N5)*($A$2:$A$2000<=N6)),1)

For Min:=

=AGGREGATE(15,6,$E$2:$E$2000/(($A$2:$A$2000>=N5)*($A$2:$A$2000<=N6)),1)

 

[Nishant94]

If you have Office 365 or Office 2016, then you can use this as well:

For Max:=

=MAXIFS($E$2:$E$2000,$A$2:$A$2000,">="&N5,$A$2:$A$2000,"<="&N6)

For Min:=

=MINIFS($E$2:$E$2000,$A$2:$A$2000,">="&N5,$A$2:$A$2000,"<="&N6)

 

[SonuBhaiya]

Thanks a lot...