Maximum and Minimum between two serial numbers

[theLEARNER1991]

Hi friends,

I wanted to find maximum and minimum between two dates, date is in column a7 to a2000, highest sales point for day is in column e7 to e2000,lowest sales point for day is in column f7 to f2000,starting date is computed in column o7 to o2000 and end date is in p7 to p2000.
maximum sales between o7 and p7 should in cell q7 minimum sales between minimum in same period should be in cell r7
maximum sales between o8 and p8 should in cell q8 minimum sales between minimum in same period should be in cell r8 and so on till row 2000...
I tried to use nested function but couldnt use it
the functin I used is
=MAX(IF($A$7:$A$2000>=O7,IF($A$7:$A$2000<=P7,$E$7:$E$2000))) But when i tried to enter function as nested function (by pressing ctrl+shift+enter) nothing happended.
I am using ms excel 2016.

hope you people would help me in this regard

Thanks in advance

 

[shaowu459]

the formula seem OK. please check the date in column A, are they really sored as Date, can you change its apperance by formating cells? are there hour and minute information in column A, for example 2020-5-19 11:00:00.

or maybe you can try:
=MAX(IF($A$7:$A$2000-O7>=0,IF($A$7:$A$2000-P7<=0,$E$7:$E$2000)))
CTRL+SHIFT+ENTER finish

 

[theLEARNER1991]

the formula seem OK. please check the date in column A, are they really sored as Date, can you change its apperance by formating cells? are there hour and minute information in column A, for example 2020-5-19 11:00:00. or maybe you can try: =MAX(IF($A$7:$A$2000-O7>=0,IF($A$7:$A$2000-P7<=0,$E$7:$E$2000))) CTRL+SHIFT+ENTER finish

Thanks for reply
But there seems to be a problem in using nested function ctrl +shift +enter would do nothing[/QUOTE]

 

[shaowu459]

Thanks for reply
But there seems to be a problem in using nested function ctrl +shift +enter would do nothing

[/QUOTE]
Maybe we need your qorkbook to see what goes wrong.

 

[KRice]

Here is one alternative approach:

MrExcel20200516_b.xlsx
	A			D	E	F	G								O	P	Q	R
5
6	Date				High	Low									start	end	max sales	min sales
7	1/1/2020				1000	994									1/8/2020	1/10/2020	1005	999
8	1/2/2020				1001	1000									1/1/2020	1/3/2020	1002	994
9	1/3/2020				1002	996									1/5/2020	1/12/2020	1006	993
10	1/4/2020				1003	998
11	1/5/2020				1004	1002
12	1/6/2020				1005	996
13	1/7/2020				1006	1002
14	1/8/2020				1005	999
15	1/9/2020				1004	999
16	1/10/2020				1003	1001
17	1/11/2020				1002	993
18	1/12/2020				1001	1000
19	1/13/2020				1000	990
Sheet13

Cell Formulas
Range	Formula
Q7:Q9	Q7	=AGGREGATE(14,6,($E$7:$E$19)/(($A$7:$A$19>=$O7)*($A$7:$A$19<=$P7)),1)
R7:R9	R7	=AGGREGATE(15,6,($F$7:$F$19)/(($A$7:$A$19>=$O7)*($A$7:$A$19<=$P7)),1)

I suppose this could be done with MAXIFS and MINIFS also:

MrExcel20200516_b.xlsx
	S	T
6	max sales	min sales
7	1005	999
Sheet13

Cell Formulas
Range	Formula
S7	S7	=MAXIFS($E$7:$E$19,$A$7:$A$19,">="&$O7,$A$7:$A$19,"<="&$P7)
T7	T7	=MINIFS($F$7:$F$19,$A$7:$A$19,">="&$O7,$A$7:$A$19,"<="&$P7)

 

[theLEARNER1991]

Here is one alternative approach:

MrExcel20200516_b.xlsx
	A			D	E	F	G								O	P	Q	R
5
6	Date				High	Low									start	end	max sales	min sales
7	1/1/2020				1000	994									1/8/2020	1/10/2020	1005	999
8	1/2/2020				1001	1000									1/1/2020	1/3/2020	1002	994
9	1/3/2020				1002	996									1/5/2020	1/12/2020	1006	993
10	1/4/2020				1003	998
11	1/5/2020				1004	1002
12	1/6/2020				1005	996
13	1/7/2020				1006	1002
14	1/8/2020				1005	999
15	1/9/2020				1004	999
16	1/10/2020				1003	1001
17	1/11/2020				1002	993
18	1/12/2020				1001	1000
19	1/13/2020				1000	990
Sheet13

Cell Formulas
Range	Formula
Q7:Q9	Q7	=AGGREGATE(14,6,($E$7:$E$19)/(($A$7:$A$19>=$O7)*($A$7:$A$19<=$P7)),1)
R7:R9	R7	=AGGREGATE(15,6,($F$7:$F$19)/(($A$7:$A$19>=$O7)*($A$7:$A$19<=$P7)),1)

I suppose this could be done with MAXIFS and MINIFS also:

MrExcel20200516_b.xlsx
	S	T
6	max sales	min sales
7	1005	999
Sheet13

Cell Formulas
Range	Formula
S7	S7	=MAXIFS($E$7:$E$19,$A$7:$A$19,">="&$O7,$A$7:$A$19,"<="&$P7)
T7	T7	=MINIFS($F$7:$F$19,$A$7:$A$19,">="&$O7,$A$7:$A$19,"<="&$P7)

Thanks...
Aggregate function worked perfectly fine
Maxifs and minifs is not available in version which I use...
Thanks a lot for your help...

 

[theLEARNER1991]

Maybe we need your qorkbook to see what goes wrong.
[/QUOTE]
Thanks for your reply...
Used aggregate function it worked perfectly fine
For some reason array function is not working in my excel and maxifs & minifs is not available in my version.
Sorry for the late reply had a bit of medical emergency...

 

[KRice]

Thanks for the feedback, and I'm glad you found a solution.