IF statement - Only 1 maximum value, and not more if duplicates.

[CV12]

Hi,

In the sheet below I used an if statement to find the maximum value in some spilled data.
In this small example, there are duplicates of some values. Is it somehow possible to only get one maximum? It doesn't matter whether it is the first or the last one out of the duplicates.
The number '5' in either cell G11 or G12 should be #N/A

Thanks in advance.

Results.xlsm
	E	F	G	H	I	J
1
2			#N/A	1		1
3			#N/A	1		1
4			#N/A	2		2
5			#N/A	2		2
6			#N/A	3		3
7			#N/A	3		3
8			#N/A	4		4
9			#N/A	4		4
10			5	5		5
11			5	5		5
12
13
14
15			Need only 1 number 5
Sheet3

Cell Formulas
Range	Formula
G2:G11	G2	=(IF(H2#=MAX(H2#),H2#,NA()))
H2:H11	H2	=(J2:J11)
Dynamic array formulas.

 

[Jamie McMillan]

Hello,

In cell G2 type =IF(H2=LARGE($H$2:$H$10,1),H2,"#N/A")
In cell G3 type =IF(ISNUMBER(G2),"#N/A",IF(H3=LARGE($H$2:$H$10,1),H3,"#N/A"))

Copy cell G3 down to G11

Jamie

 

[CV12]

Hi Jamie,

Thanks for your reply. The formula works, but I would like to have it working with spilled data. Is that also possible?

Thanks

 

[Fluff]

How about

+Fluff 1.xlsm
	G	H	I	J
1
2	#N/A	1		1
3	#N/A	1		1
4	#N/A	2		2
5	#N/A	2		2
6	#N/A	3		3
7	#N/A	3		3
8	#N/A	4		4
9	#N/A	4		4
10	5	5		5
11	#N/A	5		5
12
Main

Cell Formulas
Range	Formula
G2:G11	G2	=LET(m,MAX(H2#),a,IF(H2#=m,ROW(H2#),""),IF((H2#=m)*(a=MIN(a)),m,NA()))
H2:H11	H2	=(J2:J11)
Dynamic array formulas.

 

[CV12]

Awesome, Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.