Use min or max dynamically from a cell in formula in another cell

[tanvirabid3]

I want to find the minimum or maximum from a range. I want to choose min or max on a dynamic basis. This means that if my reference value is above 0, I want to use maximum. If my reference value is below 0, I want to use minimum. I have this formula =IF(M6>0,"max","min"). In the next cell, I am trying to use max or min and apply it on a referenced range. How can I do this without having to manually put max or min. Thanks for your help.

 

[Peter_SSs]

Perhaps I'm missing something but don't you just want something like this?

=IF(M6>0,MAX(N2:N8),MIN(N2:N8))

 

[tanvirabid3]

Perhaps I'm missing something but don't you just want something like this?

=IF(M6>0,MAX(N2:N8),MIN(N2:N8))

Hi Peter, the formula is good. Actually, I did not explain clearly what I am trying to do. Please see the image. If the reference value is below 0, I want the lowest five values. If the reference value is above zero, I am looking for the top five values. I have tried to build on your formula in cell B25. But there are errors. Please take a look.

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this helps.

23 07 16.xlsm
	A	B	C
3	Ref	-1	1
4
5
6		1	1
7		5	5
8		2	2
9		6	6
10		4	4
11		8	8
12		5	5
13		9	9
14		4	4
15		7	7
16
17
18		1	9
19		2	8
20		4	7
21		4	6
22		5	5
23
Top Bottom

Cell Formulas
Range	Formula
B18:C22	B18	=TAKE(SORT(B6:B15,,-SIGN(B3)),5)
Dynamic array formulas.

 

[tanvirabid3]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this helps.

23 07 16.xlsm
	A	B	C
3	Ref	-1	1
4
5
6		1	1
7		5	5
8		2	2
9		6	6
10		4	4
11		8	8
12		5	5
13		9	9
14		4	4
15		7	7
16
17
18		1	9
19		2	8
20		4	7
21		4	6
22		5	5
23
Top Bottom

Cell Formulas
Range	Formula
B18:C22	B18	=TAKE(SORT(B6:B15,,-SIGN(B3)),5)
Dynamic array formulas.

Hi Peter, actually I couldn't use the formula because the take and sort functions don't work on my version of excel. I tried the following:

=IF(B$3>0,LARGE(B$6:B$15,$A6),IF(B$3<0,SMALL(B$6:B$15,$A6)))

A6 has the value of 1, A7 of 2 and so on. As I drag down, it gives me the values on an ascending or descending basis (subject to the reference value being below or above zero).

Please validate. But I think it works. Thank you.

 

[Peter_SSs]

the take and sort functions don't work on my version of excel.

Do you actually have MS365 as shown in your profile? If so, I would have thought that you would at least have the SORT function?
Have you looked to apply any available updates?

 

[tanvirabid3]

Actually I now have Microsoft Office Professional Plus 2013, Thanks Peter for your help.

 

[Peter_SSs]

Actually I now have Microsoft Office Professional Plus 2013,

In that case you probably should fix your account details?

Here is another option to consider

23 07 16.xlsm
	A	B	C
3	Ref	-1	8
4
5
6		1	1
7		5	5
8		2	2
9		6	6
10		4	4
11		8	8
12		5	5
13		9	9
14		4	4
15		7	7
16
17
18		1	9
19		2	8
20		4	7
21		4	6
22		5	5
Top Bottom (2)

Cell Formulas
Range	Formula
B18:C22	B18	=AGGREGATE(IF(B$3>0,14,15),6,B$6:B$15,ROWS(B$18:B18))

 

[tanvirabid3]

In that case you probably should fix your account details?

View attachment 95404

Here is another option to consider

23 07 16.xlsm
	A	B	C
3	Ref	-1	8
4
5
6		1	1
7		5	5
8		2	2
9		6	6
10		4	4
11		8	8
12		5	5
13		9	9
14		4	4
15		7	7
16
17
18		1	9
19		2	8
20		4	7
21		4	6
22		5	5
Top Bottom (2)

Cell Formulas
Range	Formula
B18:C22	B18	=AGGREGATE(IF(B$3>0,14,15),6,B$6:B$15,ROWS(B$18:B18))

Will do, thank you so much.