Returning the average value for a set of values in Microsoft Excel is as easy as dropping in an AVERAGE() function—most of the time. As long as you want to consider every value in the data range, you’re 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 the duplicates. In that case, AVERAGE() won’t get the job done. You could spend a lot of time coming up with an expression, but there’s a much easier way. I’ll show you how to get that duplicate-ignoring average without a complex expression.
LEARN MORE: Office 365 Consumer pricing and features
I’m using (desktop) Office 365 on a Windows 10 64-bit system, but you can use older versions. You can work with your own data or download the demonstration .xlxs and .xls file. The Remove Duplicates tool isn’t available in the older .xls format, but you can use an Advanced filter instead. The online version supports this technique.
About the AVERAGE() function in Microsoft Excel
Figure A shows the results of averaging a simple data set that includes duplicate values. There’s not a lot to say about this function—it performs exactly as you probably expect. The result is the same as summing the values and dividing by 8. AVERAGE() evaluates 0, but it doesn’t evaluate blanks.
How to create a unique list in Microsoft Excel
An expression is the first route most of us would take to ignore duplicates, but instead, let’s reconsider 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, it’s a simple matter of using the standard AVERAGE() function.
We’re going to use the Remove Duplicates tool to create a list of unique values. We’ll start by copying the original data to another spot—say, column D. This feature will remove duplicates from the data set, so you don’t want to work with the original data.
SEE: What to do if you’re still running Windows 7 (free PDF) (TechRepublic)
After copying the list (Figure B), follow these steps on the copied values in column D.
Select the data set–in this case that’s D3:D10.
Click the Data tab.
In the Data Tools group, click Remove Duplicates. In the resulting dialog (Figure B), click OK. When applying this to your work, you might need to check the My Data Has Headers option.
Click OK to confirm the removal of two duplicate values (53 and 12).
The new average evaluates only the unique values in the original data set because that’s all there is to evaluate (Figure C). To skip the copy task, you can use Excel’s Advanced filter–click the Data tab and then click the Advanced option in the Sort & Filter group.
A simple solution is often best
You might already know about Excel’s Remove Duplicates tool, but if you’re like most of us, your initial thought is to use an expression. All it takes to simplify this problem is to look at it a bit differently.
What unique averaging problems have you had? Please share your solutions in the comments section below.
Send me your Microsoft Office questions
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at email@example.com.