(Excel examples for Beginners)
In this end-to-end excel example, you will learn – Excel formula for Beginners – How to Find Minimum value if unique in Excel.
Excel formula for Beginners – How to Find Minimum value if unique in Excel
Generic formula
=MIN(UNIQUE(range,0,1))
Explanation
To find the minimum unique value in a set of data, you can use the UNIQUE function together with the MIN function. In the example below, the formula in E5 is:
=MIN(UNIQUE(data,0,1))
where “data” is the named range B5:B14.
In older versions of Excel, you can use an array formula based on the MIN, IF, and COUNTIF functions as explained below.
How this formula works
The goal in this example is to return the minimum value that is unique, i.e. the minimum value that occurs only once in the data.
The UNIQUE function, new in Excel 365, will return a unique list of values from a set of data. By default, this is a list of any value that occurs one or more times in the data.
UNIQUE has an optional third argument called “exactly_once” that will limit results to values that occur once only in the source data. To enable this feature, the argument needs to be set to TRUE or 1.
Working from the inside out, the UNIQUE function is configured like this:
UNIQUE(data,0,1)
For array, we provide the named range data. For the by_col argument, we use zero (0), since we want unique values by rows, not columns. Finally, for exactly_once, we provide 1, since we want only values that occur just once in the source data.
Configured this way, UNIQUE returns the 4 values that appear only once:
{700;600;500;300} // result from unique
This array is returned directly to the MIN function, which returns the minimum value, 300, as the final result:
=MIN({700;600;500;300}) // returns 300
Array formula with COUNTIF
If you are using a version of Excel without the UNIQUE function, you can find the minimum unique value with an array formula based on the COUNTIF, MIN, and IF functions.
{=MIN(IF(COUNTIF(data,data)=1,data))}
This is an array formula and must be entered with control + shift + enter, except in Excel 365.
Working from the inside out, the COUNTIF function is used to generate a count of each value in the data like this:
COUNTIF(data,data) // count all values
Because there are 10 values in the named range data, COUNTIF returns an array of 10 results:
{2;1;1;2;1;2;2;2;1;2}
This array holds the count of each value. Next we test the array for values equal to 1:
{2;1;1;2;1;2;2;2;1;2}=1
Again, we get an array with 10 results:
{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}
Each TRUE value corresponds to a value in the source data that occurs just once. This array is delivered directly to the IF function, which uses it like a filter. Only values in data associated with TRUE make it into the array returned by IF, all other values are FALSE.
{FALSE;700;600;FALSE;500;FALSE;FALSE;FALSE;300;FALSE}
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.