10.2 C
London
Sunday, May 18, 2025
HomeExcelDescriptive Statistics in ExcelExcel: How to Calculate Average If Cell Contains Number

Excel: How to Calculate Average If Cell Contains Number

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

You can use the following formula to calculate the average in Excel only for the cells that contain a number in a corresponding range:

=SUMPRODUCT(--ISNUMBER(A2:A11), B2:B11) / COUNT(A2:A11)

This particular formula will calculate the average of the values in the range B2:B11 only for the cells that contain a number in the range A2:A11.

The following example shows how to use this formula in practice.

Example: Calculate Average If Cell Contains Number

Suppose we have the following dataset in Excel that shows the number of sales made by employees at a company with certain ID’s:

We can use the following formula to calculate the average sales made by only the employees who have a numeric value for ID:

=SUMPRODUCT(--ISNUMBER(A2:A11), B2:B11) / COUNT(A2:A11)

The following screenshot shows how to use this formula in practice:

The average sales among employees with a numeric ID is 12.

We can manually verify that this is correct by identifying which employees have numeric ID’s:

The average sales made by these employees can be calculated as:

Average Sales = (19 + 4 + 8 +17) / 4 = 12.

This matches the value calculated using our formula.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Calculate Average If Between Two Values in Excel
How to Calculate a Cumulative Average in Excel
How to Find Weighted Moving Averages in Excel

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories