Posted on https://www.excelforum.com/excel-general/1294641-largest-smallest-2nd-largest-and-2nd-smallest-2.html#post5221789

but could not got complete answer

I need 4 formulae for: largest, smallest, 2nd largest (smaller than largest & non duplicate of largest) & 2nd smallest (larger than smallest & non duplicate of smallest) from B3:L3 (contiguous columns) in N3, O3, P3, Q3

Conditions #1 : If all values in B3:L3 are zero 0 or null, then all 4 formulae should give answers as blank.

Conditions #2 : largest & 2nd largest cannot be same, similarly smallest & 2nd smallest cannot be same.

Example: If B3=500, C3=600, D3=600, E3=600, F3=600, G3=600, H3=600, I3=600, J3=600, K3=600, L3=600 then largest=600, smallest=500, 2

Conditions #3 : Any of the 4 answers cannot be zero. This condition #3 requires more clarifications which are:

Example: If B3=0, C3=800, D3=-800, E3=-20, F3=-9, G3=-2, H3=-1, I3=0, J3=0, K3=0, L3=0 then largest=800, smallest=-800, 2

Conditions #4 : If all the values are same (but <>0 or <>””), then Example: If B3=-5, C3=-5, D3=-5, E3=-5, F3=-5, G3=-5, H3=-5, I3=-5, J3=-5, K3=-5, L3=-5 then largest=-5, smallest=-5, 2

Note: 2

Example: If B3=0, C3=800, D3=-8, E3=0, F3=-9, G3=0, H3=0, I3=0, J3=0, K3=0, L3=0 then largest=800, smallest=-9, 2

How to accomplish?

Thanks

<tbody>

</tbody>Correct answer for P5="",Q5=""

Correct answer for N7=-5,O7=-5

but could not got complete answer

I need 4 formulae for: largest, smallest, 2nd largest (smaller than largest & non duplicate of largest) & 2nd smallest (larger than smallest & non duplicate of smallest) from B3:L3 (contiguous columns) in N3, O3, P3, Q3

Conditions #1 : If all values in B3:L3 are zero 0 or null, then all 4 formulae should give answers as blank.

Conditions #2 : largest & 2nd largest cannot be same, similarly smallest & 2nd smallest cannot be same.

Example: If B3=500, C3=600, D3=600, E3=600, F3=600, G3=600, H3=600, I3=600, J3=600, K3=600, L3=600 then largest=600, smallest=500, 2

^{nd}largest=blank, 2^{nd}smallest=blankConditions #3 : Any of the 4 answers cannot be zero. This condition #3 requires more clarifications which are:

Example: If B3=0, C3=800, D3=-800, E3=-20, F3=-9, G3=-2, H3=-1, I3=0, J3=0, K3=0, L3=0 then largest=800, smallest=-800, 2

^{nd}largest=-1, 2^{nd}smallest=-20Conditions #4 : If all the values are same (but <>0 or <>””), then Example: If B3=-5, C3=-5, D3=-5, E3=-5, F3=-5, G3=-5, H3=-5, I3=-5, J3=-5, K3=-5, L3=-5 then largest=-5, smallest=-5, 2

^{nd}largest=blank, 2^{nd}smallest=blankNote: 2

^{nd}largest & 2^{nd}smallest can be sameExample: If B3=0, C3=800, D3=-8, E3=0, F3=-9, G3=0, H3=0, I3=0, J3=0, K3=0, L3=0 then largest=800, smallest=-9, 2

^{nd}largest=-8, 2^{nd}smallest=-8How to accomplish?

Thanks

B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |

2 | Largest | Smallest | 2nd Largest | 2nd Smallest | ||||||||||||

3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||

4 | ||||||||||||||||

5 | 500 | 600 | 600 | 600 | 600 | 600 | 600 | 600 | 600 | 600 | 600 | 600 | 500 | 600 | 600 | |

6 | 0 | 800 | -800 | -20 | -9 | -2 | -1 | 0 | 0 | 0 | 0 | 800 | -800 | -1 | -20 | |

7 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | |||||

8 | 0 | 800 | -8 | 0 | -9 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | -9 | -8 | -8 | |

9 | 80 | 32 | 10 | 20 | 30 | 10 | 50 | 0 | -9 | 80 | -9 | 80 | -9 | 50 | 10 | |

10 | 800 | 800 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 |

<tbody>

</tbody>

Correct answer for N7=-5,O7=-5

Last edited: