Hello,
I have a list of string values in a column and I need to write a command that returns the most frequently occurring value which is not "NA". For example:
Dog
Dog
Dog
Cat
Cat
NA
NA
NA
NA
I want the formula to return the value "Dog" despite the fact that NA is actually the most frequently occurring value. The internet helped me get this formula:
=INDEX(A1:A9,MODE(MATCH(A1:A9,A1:A9,0))). I just need an extra tweak to help it say "except NA!" Any help appreciated
=FREQUENCY( $A$1:$A$9, $B1:$B3)
Where B1 - B3 is your criteria ie: Dog, Cat n/a etc.
Last edited by BlindAlley; 04-27-2020 at 10:39 AM.
Uploading an attachment will assist Forum Members in finding the correct Solution for you. Create a dummy worksheet if necessary, add your start data and show where / what your expected results should be. TO ADD A FILE:
Go to 'Advanced. click on 'Manage Attachments. Select 'Browse & Choose your file, Select 'Upload and then close the Attachment window.
Hi
to be confirmed with control+shift+enter
=INDEX(A1:A9,MODE(IF(A1:A9<>"#NA",MATCH(A1:A9,A1:A9,0))))
Regads
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Ok, but what if the list is like this:
Dog
NA
Dog
NA
NA
Cat
Dog
Cat
NA
I have tried to put a list instead of the range you suggested, and it doesn't seem to work :/
Hi again,
a different approach
=INDEX(A1:A9,AGGREGATE(15,6,ROW(1:9)/(COUNTIFS(A1:A9,A1:A9,A1:A9,"<>#NA")=MAX(INDEX(COUNTIFS(A1:A9,A1:A9,A1:A9,"<>#NA"),))),1))
just enter
In the attachment formulas I'm excluding "NA" not "#NA"
Hope it could work.
Last edited by canapone; 04-27-2020 at 11:52 AM.
Used this one: =INDEX(A1:A9,MODE(IF(A1:A9<>"#NA",MATCH(A1:A9,A1:A9,0))))
and it worked perfectly! Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks