Excel tip: How to compute average using complex conditions - exclude zeros and blanks

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version

When working with a spreadsheet like MS Excel, there are certain times that you want to calculate data based on certain conditions. For instance, you might need to calculate the average of a certain column based on the values on another column, or based on the values to be averaged themselves.

Using AVERAGEIFS to compute conditional average

It is good to know that MS Excel has a lot of built-in functions to do most of the calculation jobs. And the most useful functions are the conditional functions like SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, and many others.

In this article, I will demonstrate the use of AVERAGE function to calculate conditional average in an array format. By definition, AVERAGE function returns the average (arithmetic mean) of the arguments. It has the following syntax:

AVERAGE(number1, [number2], ...)

SYNTAX NOTES:

  • Number1    Required. The first number, cell reference, or range for which you want the average.
  • Number2, ...    Optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.

For the purpose of this demonstration, consider the following Excel table:

Sample usage of AVERAGEIFS function in Excel

In the worksheet shown, I used conditional formatting to highlight the weekend dates in red. Now, suppose you need to calculate the average of the sales data. Yes, you can do it manually by taking the SUM of the weekly totals and then divide it by the total number of weeks. This formula can manually calculate the average sales:

 =SUM(C4,C11,C18,C25)/16

However, to dynamically calculate the average sales, here is the formula:

{=AVERAGE(IF(C2:C25<>0,IF(B2:B25<>"",C2:C25)))}

Note that you cannot enter the curly brackets in the formula manually. You need to press Ctrl+Shift+Enter to make the formula an array and the curly braces will be placed automatically.

What the array formula above does is it calculates the average of the sales figures in cells C2:C25 excluding:

  1. Non-numerical data like OFF
  2. The weekly totals

The non-numerics are excluded by the expression IF(C2:C25<>0, part. The weekly totals are excluded from the average using the expression IF(B2:B25<>"",. So overall, the formula will only take the figure for average calculation when the data is a number and when the corresponding date in column B is NOT BLANK.

You can download the sample Excel file below.

 

 

MS Office Software: 
AttachmentSize
File conditional-average-sample.xlsx10.46 KB