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.
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. |