largest, smallest, 2nd largest & 2nd smallest
I want to fill largest, smallest, 2nd largest (smaller than largest & non duplicate of largest) & 2nd smallest (larger than smallest & non duplicate of smallest) from an array B3:L42 (contiguous columns)
Arrays for largest: N3:N42
Arrays for smallest: O3:O42
Arrays for 2nd largest: P3:P42
Arrays for 2nd smallest: Q3:Q42
How to accomplish?
Thanks in advance.
Sheet1
B C D E F G H I J K L M N O P Q
2 Largest Smallest 2nd Largest 2nd Smallest
3 20 30 -40 5 -20 30 -5 -20 0 8 -100 30 -100 20 -20
For the largest, in N3 you could enter:
=MAX($B$3:$L$7)
in O3:
=MIN($B$3:$L$7)
in P3
=LARGE(B3:L7,COUNTIF(B3:L7,MAX(B3:L7))+1)
In Q3,
=SMALL($B$3:$L$7,COUNTIF($B$3:$L$7,MAX($B$3:$L$7))+1)
For P3 & Q3: By default, B3:L42 = 0 & in that case P3=#NUM! & Q3=#NUM!. How to avoid this?Originally Posted by Gregb11
I don't know what you mean. Can you upload a file to show what you're talking about? (I don't get the #NUM on my example).
Me either.(I don't get the #NUM on my example).
Dave
I mean the 4 formulas should ignore zero 0, if any, in the array B3:L42 (contiguous columns).
Workbook attached for your kind perusal.
2nd example in the Workbook attached
It's getting #Num because all the values are 0 so there is no 2nd largest etc.
If you want, you can wrap the formula with "If error")
Can all the 4 formulas be re-build to take care if all the values are same in the array B3:L42 (contiguous columns).
If all the values are same in the array, then answer should be blank / null (“”)
You want all the formulas changed, or just the 2nd largest/smallest? So if you had all 5's and one 0, you'd want 5 to be the highest, but if all the values were 5, you don't want 5, you want blank?
You want all the formulas changed, or just the 2nd largest/smallest? Yes new formulas for all 4 (largest, smallest, 2nd largest & 2nd smallest).
So if you had all 5's and one 0, you'd want 5 to be the highest Yes 5 will be highest; 5 will be lowest also in this case. for 2nd largest & 2nd smallest zero 0; zero 0 (since all 6's were not same)
but if all the values were 5, you don't want 5, you want blank? Yes blank for all 4
For blank if the same in range and to exclude zero: =if(countif($B$3:$L$7,B3)=count($B$3:$L$7),"",MAX(if($B$3:$L$7<>0,$B$3:$L$7))
Apply to all formulas
Click the * to say thanks.
I used CTRL+SHIFT+ENTER to confirm the formula, is it okay?
plus it gives correct answer only for 'largest'. For smallest, 2nd largest & 2nd smallest the answer remains 'same' as of 'largest'.
Attach your workbook for troubleshooting, please - the version in which you have tried to implement the formula.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Workbook Attached
You've used this in all four formulae:
MAX(IF($B$3:$L$7<>0,$B$3:$L$7)
That's why you are getting the same result.
For the smallest: =IF(COUNTIF($B$3:$L$7,C3)=COUNT($B$3:$L$7),"",MIN(IF($B$3:$L$7<>0,$B$3:$L$7)))
2nd smallest: =IF(COUNTIF($B$3:$L$7,E3)=COUNT($B$3:$L$7),"",SMALL($B$3:$L$7,COUNTIF($B$3:$L$7,MAX($B$3:$L$7))+1))
2nd largest: =IF(COUNTIF($B$3:$L$7,D3)=COUNT($B$3:$L$7),"",LARGE(B3:L7,COUNTIF(B3:L7,MAX(B3:L7))+1))
Last edited by AliGW; 11-01-2019 at 12:52 PM.
Work book attached
Out of 4 formulae, 2 are giving correct answers. 2nd Smallest & 2nd largest is giving wrong result.
When 2nd smallest value is found as zero 0, in a row of the array B3:L7, formula chooses zero & fills it as answer.
All 4 answers must be non zero i.e. <>0 else blank if ‘no choice’.
Surprisingly, if ‘duplicate / triplicate / quadruplicate / ………..’ value is found in largest, the 2nd smallest is giving wrong & ‘different’ answers every time.
2nd smallest formula is required to be modified.
Even 2nd largest formula chooses zero, it also need modification.
Your Workbook attached.
Provide more sample data - several lines that each illustrates one of the issues you are wanting to overcome.
The formulae I gave you worked on the sample you gave in that workbook. We can only work with what you give us.
Workbook with different combinations of datas attached
All C+S+E:
Excel 2016 (Windows) 32 bit
=IF(COUNTIF($B3:$L3,B3)=COUNT($B3:$L3),"",MAX(IF($B3:$L3<>0,$B3:$L3))) =IF(COUNTIF($B3:$L3,B3)=COUNT($B3:$L3),"",MIN(IF($B3:$L3<>0,$B3:$L3))) =IFERROR(IF(COUNTIF($B3:$L3,0)=COUNT($B3:$L3),"",LARGE(IF($B3:$L3<>0,$B3:$L3),COUNTIF($B3:$L3,MIN($B3:$L3))+1)),"") =IFERROR(IF(COUNTIF($B3:$L3,0)=COUNT($B3:$L3),"",SMALL(IF($B3:$L3<>0,$B3:$L3),COUNTIF($B3:$L3,MIN($B3:$L3))+1)),"")
Sheet: Sheet1
Edited reason given in the below posts
Last edited by bittubadri; 11-02-2019 at 06:34 AM.
Not yet.
Row #5: I made B5=700.
Largest (800) & Smallest (700) answer is correct.
But 2nd largest should be 700 instead of 800 (given by your formula).
2nd smallest should be blank instead of 800 (given by your formula).
Modified formula needed for 2nd largest & 2nd smallest, please
Largest & 2nd largest cannot be same
Smallest & 2nd smallest cannot be same
Look - you have not SHOWN these possibilities in your sample file. I asked you to provide a workbook illustrating what each of these combinations should look like. As you have failed to do so, and just keep adding new criteria to the problem, I shall not be offering any further help. Sorry. There are not enough hours in my day to keep coming back to find that the goalposts have shifted again.
I agree that it happened while providing 'possibilities' in the sample file because of 'quickness' to reply you back. But at least have sympathy because I posted my requirement in my original post #1 as ""non duplicate of largest"" for 2nd largest.
I think my plea should be considered.
Last edited by AliGW; 11-02-2019 at 07:11 AM. Reason: Please don't quote unnecessarily!
Not by me - I don't have time - sorry. Hopefully someone else will. Remember we are unpaid volunteers - it's up to us as individuals where we choose to give support. I have given plenty of support here to you and have now run out of time (and patience).
If you had given a decent sample workbook right at the outset, I dare say your problem would have been resolved very quickly. Good luck with it.
Posted this on https://www.mrexcel.com/forum/excel-...ml#post5365715
to get the solution
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks