Unique function example

Excel Unique Function

June 16, 2020


Have you wanted to count unique values in Excel? Early this year Microsoft released to all users of Excel new array functions that make using an array A LOT easier in order to do so.  These mainly take the place of Control-Shift-Enter arrays (CSE). If you are a user of LibreOffice, I am afraid these are not yet available to that office suite. I am sure they are on their way though. The new array functions will be able to save you a lot of thinking and math.

Under the old CSE style of arrays you could come up with long and complex formulas.  I believe the longest formula I wrote in a spreadsheet was about 600 characters. Needless to say debugging or adding new considerations in the formula was time consuming and took some time. Depending on what you are trying to calculate, that same type of formula could possibly now be written in about 50 characters.

I am going to keep this simple by discussing only the new UNIQUE function here. First lets take a look at the old way. The way I would evaluate a list uses IFERROR to avoid displaying N/A errors. LOOKUP to do what it suggests, lookup values. Finally COUNTIF to evaluate the lookup data to find the unique values. I made a simple spreadsheet with a fictional employee list as seen below:

CSE style array formula to find unique values.

You can see the formula I used up in the formula bar. Using a binary (1 = yes) type of sort I labelled all the active employees. So now I want to find all the unique names. This example is rather impractical in real life, but that is why it is an example. In a nutshell that ugly formula basically says Lookup and write only once unique names from column B and list them in column D only if the value in Column A is 1.  It lists from bottom up and in column D it fills in the names by comparing the names to the name listed above in column D. I then copied that formula into every cell from D2 through D15, Rather complicated.  It works. Yes there are other ways to do this, however the formula will look just as complicated.

So what happened in 2020?  SOMETHING AMAZING! In fact it will blow you away. It will give you free time. It might win over new fans to Excel and other spreadsheet software programs. Really to be honest, this is likely very familiar to you if you use Google Sheets. Are you ready to see how to rewrite the above formula the new way?

The Unique function

UNIQUE formula with spill results.

There you have it, the new way to count unique Excel entries. To get the same list of unique names from the previous formula, just type =UNIQUE(B2:B15).  The only issue you may have with this is if the column the data spills into finds a cell with data in it.  In which case you will get a spill error. Another benefit is that since you only entered the formula one time and the resulting data “spills” down, you end up with a smaller file size that loads faster.

There you have it, one of the new functions Microsoft added to Excel.  Such a time and frustration saver. 

Photography Basics ISO

So received your first Single-lens reflect camera or (SLR). Since it is 2020, it is likely a Digital variety (DSLR). Whether you purchased it yourself or received as a hand me down or a gift this is an exciting moment.  You take it outside or to a party and take that first picture and then you are disappointed.  “I thought these were supposed to be top tier cameras” you think.  They are, and just like everything in life it is knowing how to use it. There are the automatic modes, but if you really want to unlock the full power of your camera then you will want to use manual mode. Don’t worry phone users, this writing is for you too if your camera has a manual mode option. Lets dive into part one of my mini series: Photography basics ISO.

What is ISO?  Well the acronym is rather boring and refers to the name of the standards organization, The International Organization of Standards. As to its historical purpose it is the speed of the film. This effected the light sensitivity of the camera to the film. So in the world of DLSR’s it is the measure of the light sensitivity of the sensor. So when you change the ISO higher you are making it more sensitive to light.  A lower ISO is less sensitive. Many modern high end DSLR cameras with interchangeable lenses can reach ISO’s in near or beyond 100,000. The main problem with a higher ISO value is noise or grainy pictures. 

An example of grain and noise.

If you are wanting to take everyday photos you will likely use ISO 100.  It is the standard and default. This value collects the most detail information for editing (post processing).  It is because of this detail I suggest setting the camera to save in RAW.  Once you get the image to a the larger screen you will see all the flaws you are unable to see on the small camera. (Unless of course your camera is connected to a desktop or laptop for for previewing and shuttering.) Be aware when you venture into higher ISO’s. Older DSLR models (and crop sensors) often produce grainy or noisy pictures at the higher ISO values while newer models and full frame sensors are less likely to have noise in the pictures. My current Canon 40D is a rather old model now and it also is a crop sensor camera meaning it is highly susceptible to noisy pictures. I have to do a lot of post processing (editing) work to make a halfway decent picture.  Therefore I am also looking to purchase a newer full frame DSLR.

How do I select an ISO with low Noise?  Simple remember lower ISO is better.  A lot of the high ISO use is reserved for very dark settings such as taking astrophotography pictures as is my hobby. Generally you want to use a high ISO with a low f value ( for a different blog explanation) for a depth of field. A lot of this is easily found through trial an error or through math calculations. I plan to develop a spreadsheet for those who love the images but hate the math.

So when you pick up your camera or switch to manual mode on your cell phone, you now have a better idea what the ISO value is for, being how sensitive to light the image sensor is.

Canon 40D | ISO 800 | 18mm | f/3.5 | 20 sec | Maglite and paper