I have positive numeric data in range B6:Z6, [no negative numbers] but it does have duplicates , including zeros, say
100, 56, 0, 6, 89, 47, 32, 0, 25, 66, 88, 66, 49, 23, 8, 37, 17, 21, 63, 57, 42, 99, 4, and so on
I need a formula to find Lowest to largest top 10 values excluding ZEROS in a single row.
Say, data should appear in different cell as
ROW HEADER ===>>> B C D E F G H I J K
FORMULA RESULT ===>>> 4 6 8 17 21 23 25 32 37 42
How to get above ?
Last edited by analystbank; 12-24-2020 at 06:31 AM.
There are instructions at the top of the page explaining how to attach your sample workbook.
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.
Sample Workbook attached. Read with #1 Please
If, say 15occurs twice and is amongst the bottom 10... does it appear ONCE or TWICE in the results?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
No answer as yet... This will return duplicates n times
=AGGREGATE(15,6,$B$6:$Z$6/($B$6:$Z$6>0),COLUMNS($B10:B10))
in B10, copied across.
Whereas this will return duplicated values only once.
=AGGREGATE(15,6,$B$6:$Z$6/(($B$6:$Z$6>0)*(COUNTIF($A10:A10,$B$6:$Z$6)=0)),1)
Once only, some compromise but have to live with it .Originally Posted by Glenn Kennedy
WORKS, PERFECT Sir.
In the second part of formula, you are referring to A10 [(COUNTIF($A10:A10 ] , If I have some text/value in A10, will it jeoparadise the result ? If not, it is serves my purpose, many thanks. After hearing from you, i will close this thread as SOLVED and add reuptation to your profile.
Providing that whatever is in A10 is NOT one of the numbers that can be returned, there will be no problem.
If it can, there may be a workaround. Is it a possibility?
Sir, No such possibility. A10 could have identifiable header / title what that row comprises.
Many thanks, once again. I am trying to decipher, what each component of formula is doing. Thanks
Last edited by AliGW; 12-24-2020 at 07:29 AM. Reason: PLEASE stop quoting unnecessarily!
Formula/evalute formula will step you through what's happening. If you want/need an explanation, just ask. I will be back in an hour.
Array formula , drag right
HTML Code:
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks