Ranked Choice Voting is getting popular in the US and fortunately, you don’t need a super sophisticated program to determine the results of an election conducted using Ranked Choice Voting. This blog post explains a super simple, yet a little sophisticated, semi-automated method of determining the winner in a Ranked Choice Voting electoral system.Read More »
If you have new data flowing into your Google Sheets regularly, this blog post is for you! Many a time, you will have additional columns in your data sheet, running calculations based on the raw data being imported in your Google Sheet.Read More »
Data transformation is fun! More so when you’re doing it using tools like Microsoft Excel and Tableau. While programming languages like Python and R facilitate this very easily, it’s not everybody’s cup of tea. Excel and Tableau are relatively easier to understand.Read More »
Problem and Dataset
I recently had a client who wanted a quick way to count the number of unique values in a list. In the following case, the number of unique names in the data in column A.
The general format of the formula is:
How it Works
The trick is in knowing how the formula actually works.
If you evaluate the formula, this is what you get:
- The formula counts the number of instances of each name in the list (7; 7; 7; ….), and stores the count in an array.
- 1/Array creates fractions for each value in the list.
- SUMPRODUCT then sums up the fractions for each name (7*1/7, 2*1/2, etc.) and gives you the number of 1s in the calculation.
Genius, I say!