Least Recurring Value in a Dataset?

New Contributor

Hi everyone, I'm working on analyzing a dataset and I'm looking for the least recurring value in a dataset w/270k entries. Basically, the opposite of the mode. I already attempted using this array formula(=INDEX(RANGE,MATCH(MIN(COUNTIF(RANGE,RANGE)),COUNTIF(RANGE,RANGE),0))) that I found on a website, but was unsure exactly what the 0 was for. 

If anyone has any useful tips or formulas, they would be greatly appreciated. Thanks!

3 Replies

@KyleRauba 

 

I just created a 100,000 item, 3 digit data set and ran my free 'List Unique Items' utility against it.  This is what is created (image below). 

Note that "600" is the single item that occurs the least.

The listing is added to the worksheet in the first group of blank columns to the right of the data set.

Nothing_Left_to_Lose_0-1640154633556.png

(truncated)

The free "Professional Compare" workbook includes the List Unique Items utility.
I will put the workbook up on OneDrive, for download, in a few minutes... (post edited to remove third-party link/advertising, not allowed in Tech Community)

 

If you have questions, I will help.

 

NLtL

@KyleRauba The zero inside a MATCH function means you are looking for the first exact match. This is what he MS support pages have to say about the match-type.

Screenshot 2021-12-22 at 08.47.43.png

@Riny_van_Eekelen Thanks for the response!