Large(if(and with 3 arguments on an array
Hello everyone,
I am trying to get the top 10 largest values in column E based on 3 arguments in column A, B and C. I tried the following for my top 1 for example:
=LARGE(IF(AND(A:A="All",B:B<>"Other",C:C="All"),E:E),1)
If I just press Enter, I get the #1 value of the entire column E (66.216), without qualifying for the 3 arguments. If I press Ctrl+Shift+Enter, I get 0.
Can I not use IF AND on an array ? If not, what would be the way to do it ?
Thanks in advance !
Susanne
Hi and welcome to the forum
Hers an option that uses a helper column instead...
in E2, copierd down, use this, and then base the =large() on the results...
=IF(AND(A2="ALL",B2="Other",C2="ALL"),D2,"")
Instead of hard-codingthe text, you could put it in a cell and reference it, and you can hide the helper if you want
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Donot use AND in this case.
Try this:
Confirmed with Ctrl-Shift-Enter
Please Login or Register to view this content.
Quang PT
Re: Large(if(and with 3 arguments on an array
Because I can't add a column I used bebo021999's solution. It works perfectly. Thank you both for the quick responses.
Susanne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks