Good Evening,
I have a formula for pulling the 1st largest, 2nd largest etc, but how do I break ties? Here is the formula (An array) for pulling the 3rd largest
=INDEX(Quotes!$AE$2:$AE$100,MATCH(LARGE(IF(Quotes!$AF$2:$AF$100='Quote Detail'!$C$1,Quotes!$AG$2:$AG$100,""),3),Quotes!$AG$2:$AG$100,0))
Thanks!
Hi,
A common technique is to add a very small amount. Untested in the absence of your workbook but perhaps something like
Formula:
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I'm having issues with that formula. Is there another way or should I use another formula maybe? I get the adding a very small amount concept, but the above formula isn't working.
Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!
Click on GO ADVANCED and use the paperclip icon to open the upload window.
The paperclip icon
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
Here is a sample spreadsheet attached as I think that would help.
=INDEX(Quotes!$AE$2:$AE$100,MATCH(LARGE(IF(Quotes!$AF$2:$AF$100="Yellow",Quotes!$AG$2:$AG$100,""),1),Quotes!$AG$2:$AG$100,0))
How would I break the ties within the Yellow fields? Thanks and sorry for not being more clear earlier.
Hi there. I think that this delivers what you need. the rows($1:1) bit gives you 1st, 2nd highest, meeting your criteria ad the +1/(Row(XXX)*10^10) bit makes each value totally unique.
It's an array formula...Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly braces yourself - it won't work...
Thanks so much!!!
Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks