hsandeep
Well-known Member
- Joined
- Dec 6, 2008
- Messages
- 1,213
- Office Version
- 2010
- Platform
- Windows
- Mobile
Posted on https://www.excelforum.com/excel-ge...d-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, 2nd largest=blank, 2nd smallest=blank
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, 2nd largest=-1, 2nd smallest=-20
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, 2nd largest=blank, 2nd smallest=blank
Note: 2nd largest & 2nd smallest can be same
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, 2nd largest=-8, 2nd smallest=-8
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, 2nd largest=blank, 2nd smallest=blank
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, 2nd largest=-1, 2nd smallest=-20
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, 2nd largest=blank, 2nd smallest=blank
Note: 2nd largest & 2nd smallest can be same
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, 2nd largest=-8, 2nd smallest=-8
How 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: