Microsoft Office – Page 5 – STL

What are the MAXIFS and MINIFS functions and why are they useful

The MAXIFS and MINIFS functions allow you to find the highest or lowest value based on one or more criteria. For example, an HR officer may need to find the highest or lowest salary from a specific department, e.g. Sales, from the dataset below.

The standard method for getting this result would be to filter the list on the specific criteria i.e. Sales and then select all the cells in the Salaries column and checking the status bar at the bottom of the screen for the highest or lowest salary. This does the job but is extremely manual and unproductive as similar methods would need to be employed for other departments. By using the MAXIFS and MINIFS functions instead, the results are all there in one place to do further analysis, e.g. using other formulas to create % bonuses for highest earners.

How to apply the MAXIFS and MINIFS functions

To calculate the Highest Salary per Department see below:

2. Copy down the MAXIFS formula for the other cells to create highest salaries for all other departments

3. Repeat this formula for the Lowest Salary per Department but replace MAXIFS with MINIFS:

Type =MINIFS($E$2:$E$29, $D$2:$D$29, H2)

In this HR example you may need to apply more criteria to limit the range even further to find the maximum value, e.g. the highest salary per department for a specific status such as status 2:

This blog is part 3 of a series that looks at the amazing functionality of some of the most popular Office 365 Excel functions. In parts 1 and 2 the focus was on the FILTER and SORT functions. Building on this, we will explore how useful the UNIQUE function is in creating unique lists more efficiently.

What is the UNIQUE function and why is it useful

The UNIQUE function lets you extract all the multiple entries in a list and populates them as single entries in a unique list. For example, clients that do business with a company may place many different orders so their name will get repeated many times. Having a unique list of clients can help to show summaries of total orders or sales.

How is it different from other functions?

The UNIQUE function is one of a number of Office 365 functions that behaves differently to all other Excel functions. With the UNIQUE function, the results automatically ‘spill’ into all available cells below. In contrast, all other functions require the result to be copied down manually.

How to apply the function

Let’s take a list of names where some of them appear more than once – see below:

Unique

Type =UNIQUE(A1:A14) in an empty cell to produce the following unique list:

2. To return a range from a horizontal list do the following:

A) Select the range of cells in the list e.g. E1:R1Type a comma and then TRUE – B) this returns all unique items across all the columns.

Another really useful feature of UNIQUE is being able to produce a list of all items that appear only once. From the original list, we can see all the items that have no duplicates i.e.. those that display no colour (see below):

3. To return a distinct list type =UNIQUE(A1:A14,,TRUE) where:
A) the 2 commas mean “bypass the 2^nd part” because the list is vertical not horizontal
B) TRUE returns only items that appear once in the original list

The result is the following list:

One thing to look out for is if any data is ‘blocking’ this space then you will get a #SPILL error. Consequently, the resulting data will not populate the cells. (See below)

To remove the spill error, simply delete the data that is blocking the spill. Other Office 365 functions such as SORT and FILTER also have this ‘spill’ feature.

Conclusion

The Excel UNIQUE function is an amazing tool that allows you to extract specific data quickly and efficiently from a list of multiple entries.

Blog Categories