How to Find Average with Blank Cells in Excel: 4 Easy Ways

Method 1 – Use AVERAGEIF Function to Find Average with Blank Cells in Excel

Steps:

Add a row named Average in row 11.
Type the following formula in cell E11.

=AVERAGEIF(D5:D9,"",E5:E9)

The AVERAGEIF function compares our selections to D5:D9.
Decide which cells within the given range shall be averaged through “”.
Calculate the average from the range E5:E9.
Press the Enter or Tab keys to find the output as $50,000.00.

Method 2 – Combine SUMPRODUCT and AVERAGE Functions to Find the Average with Zero Cells

Steps:

Write the formula down in cell E11.

=SUMPRODUCT(AVERAGE(0+E5:E9))

Enter or Tab buttons, get the output as $40,000.00.

Get the average of the check amounts including blank cells.

Method 3 – Combine Excel SUM and ROWS Functions to Calculate Average Counting Null Cells

Steps:

In cell E11, type the following formula.

=SUM(E5:E9)/ROWS(E5:E9)

Press the Enter or Tab keys to get an output of $40,000.00.

The average pop-up in the display also counts the empty cell.

Formula Breakdown

ROWS(E5:E9) returns the total row numbers as 5.
SUM(E5:E9) returns the summation of the range (E5:E9).
SUM(E5:E9)/ROWS(E5:E9) finally divides the products to finally get the average.

Method 4 – Run VBA Code to Count Average Including Empty Cells in Excel

Steps:

Go to the Developer tab and click the Visual Basic option.

The Visual Basic window appears in the display.
Tap the Insert tap and then Module to create a module box.

Type the following VBA code correctly.

Sub Avg_Inc_Blanks()
Dim SOFT_WS As Worksheet
Set SOFT_WS = Worksheets("Excel VBA")
SOFT_WS.Range("E11") = Application.WorksheetFunction.AverageIf(SOFT_WS.Range("D5:D9"), "", SOFT_WS.Range("E5:E9"))
End Sub

Close the window to open the active sheet.

Go to the Developer tab and tap Macros.

The Macro dialog box pops up.
Select the desired macro from the Macro name box and hit the Run button.

The average appears as $50,000.00 in cell E11.

Code Breakdown

As Worksheet is a representation of all the worksheets of the workbook but excludes the chart sheets.
Worksheets(“Excel VBA”) we input the active worksheet name here.
Range represents an Object that makes sure that all the given ranges get declared.

Download Practice Workbook

Download this practice workbook to exercise while reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the whole process.

Average with Blank Cells.xlsm