Copyright infringement

How to average unique values in Excel the easy way

If you need to average a list of values ​​in Microsoft Excel that contains duplicates without including the duplicates, don’t worry about a complex expression when you can easily remove those duplicates.

Download an XLS file with a label on your laptop screen.  Download concept document.  Banner for business, marketing and advertising.

Photo: muchomor, Getty Images/iStockphoto

Returning the average value of a set of values ​​in Microsoft Excel is as easy as dropping into the AVERAGE() function – most of the time. As long as you want to consider every value in the data range, you are good to go. But what if you need to ignore certain values?

For example, in Microsoft Excel, the data set contains duplicate values, but you want to evaluate each value only once; In other words, you want to ignore duplicates. In this case, AVERAGE() will not get the job done. You can spend a lot of time devising an expression, but there is a much easier way. I’ll show you how to get the average of repeated discards without a complex expression.

More information: Office 365 Consumer pricing and features

I’m using Office 365 (for desktop) on Windows 10 64-bit, but you can use older versions. You can work with your own data or download a demo. xlxs and xls file. The Remove Duplicates tool is not available in xls format. The old one, but you can use an advanced filter instead. The online version supports this technology.

About the AVERAGE() function in Microsoft Excel

Figure A Displays the results of averaging a simple data set that includes duplicate values. There isn’t much to be said about this functionality – it performs exactly as you’d expect. The result is the same as adding values ​​and dividing by 8. AVERAGE() evaluates 0, but it does not evaluate spaces.

Figure A

excelaverageunique-a.jpg

The AVERAGE() function in Excel evaluates all reference values.

How to create a unique list in Microsoft Excel

Expression is the first path most of us would take to ignore iterators, but instead let’s revisit the problem. We don’t need a complex expression – we need a unique list based on the original data.

Fortunately, creating a list of unique values ​​is easy, and from there, using the standard AVERAGE() function is simple.

We will use the Eliminate Duplicates tool to create a list of unique values. We’ll start by copying the original data somewhere else – for example, column D. This feature will remove duplicates from the data set, so you don’t want to work with the original data.

We see: What to do if you’re still using Windows 7 (Free PDF) (TechRepublic)

After copying the list (Figure B), follow these steps on the values ​​copied into column D.

  1. Select the data set – in this case it is D3: D10.

  2. Click the Data tab.

  3. In the Data Tools group, click Remove Duplicates. In the resulting dialog box (Figure B), click OK. When applying this to your business, you may want to check out the My Data Has Headers option.

  4. Click OK.

  5. Click OK to confirm that two duplicate values ​​(53 and 12) have been removed.

Figure B

excelaverageunique-b.jpg

Use the duplicate removal tool.

The new mean only evaluates unique values ​​in the original data set because that’s all that can be evaluated (Figure C). To skip the copy job, you can use the advanced filter in Excel – click the Data tab and then click the advanced option in the Sort & Filter group.

Figure C

excelaverageunique-c.jpg

The list contains only unique values.

A simple solution is often the best

You may already be familiar with Excel’s duplicate removal tool, but if you’re like most of us, your initial idea is to use an expression. All it takes to simplify this problem is to look at it a little differently.

What unique averaging problems have you encountered? Please share your solutions in the comments section below.

Send me your questions about Microsoft Office

I answer readers’ questions when I can, but there is no guarantee. Do not send files unless asked to do so; Initial requests for help that arrive with attached files will be deleted as unread. You can submit screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, you likely won’t get the response “Please troubleshoot the workbook and fix the error”, but “Can you tell me why this formula isn’t returning the expected results?” Probably. Please mention the app and version you are using. I do not compensate TechRepublic for my time or expertise when helping readers, nor do I charge a fee from the readers I help. You can contact me at susansalesharkins@gmail.com.

see also