Hi all,
Is there an easy way to get cell reference or row/col number of large formula?
I want as result row number, where LARGE formula found nth top value.
LARGE(A1:A4,1) > result A4 or row-4
LARGE(A1:A4,2) > result A2 or row-2
LARGE(A1:A4,3) > result A3 or row-3
A
2
4
4
5
MATCH function not working properly with two or more same number (example 4), always return 2. Thanks
Try
in Column E
=A2+COUNTIF($A$2:A2,A2)*0.001
Copy down
in C2
=MATCH(LARGE($E$2:$E$5,ROWS($1:1)),$E$2:$E$5,0)
JohnTopley thanks. If there is no other solution to keep one cell formula, I will have to use additional column.
In C2, copied down, use this array formula:
=MATCH(LARGE($A$2:$A$5-ROW($A$2:$A$5)/10^10,ROWS($1:1)),$A$2:$A$5-ROW($A$2:$A$5)/10^10,0)
Array Formulae are a little different from ordinary formulae 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 brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use 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 brackets yourself - it won't work...
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Glenn Kennedy thanks, this is what I want
You're welcome...
Otherway use countif without use array formula
Copy downFormula:
Please Login or Register to view this content.
Regard
To help you by my post? it would be nice to click onto say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
micope21 thanks, it also works and it's shorter. Thanks
but it's without LARGE function, so not for me
Glenn Kennedy solution is universal and we only change nth value of LARGE formula. In your solution we have to change ranges, but it's nice alternative for LARGE
Last edited by mtcas; 03-15-2017 at 07:26 AM.
Hi Glenn,
After frantically searching for nearly 4 hours, I think this is exactly what I need. However I'm having trouble understanding the syntax. Could you please explain what theis doing in this example? Also, I replaced
Please Login or Register to view this content.withPlease Login or Register to view this content.to grab the top 10 for my situation.Please Login or Register to view this content.
Any help would be greatly appreciated!
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, or you want clarification, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. After all, the thread is three years old.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks