Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Average Cells Based on Multiple Criteria in Excel?
In Excel, we average the value based on no conditions. Our requirements are only a list of numbers, and we can calculate the average easily. But have you ever tried to calculate the average based on conditions? We can do this by using the formulas. This tutorial will help you understand how we can average cells based on multiple criteria in Excel. In this tutorial, I will demonstrate two methods for calculating the average in Excel: one using a single criterion and another based on multiple criteria.
Average Cells Based on Single Criteria
Here we will get the average by matching the cell value. Let us see a straightforward process to see how we can average cells based on single criteria, and we can complete this by using the formulas.
Let us consider an Excel sheet where the data is like the data shown in the below image.
Now, in the empty cell, enter the formula =AVERAGEIF(A2:A12,D2, B2:B12) and press enter to get the result shown in the image below. In the formula, A2:A12 is the range of products, B2:12 is the range of values, and D2 is the address of the cell where we have the criteria.
Average Cells Based on Multiple Criteria
In this section, we will calculate the average based on cell value. Let us see an effortless process to see how we can average cells based on multiple criteria, and we can complete this by using the formulas.
Let us consider the same data that we used in the above example. The other criteria we are using in the example is that the value should be greater than one hundred.
Now click on an empty cell and enter the formula as
=AVERAGEIFS(B2:B12,A2:A12,E1,B2:B12,">100") then click OK to get the result shown in the below image.
Conclusion
In this tutorial, we used a simple example to demonstrate how we can average cells based on multiple criteria in Excel.
376 Views
