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.

• 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.

• 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.

• 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.

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.

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.

Related Articles

• How to Calculate Average of Multiple Columns in Excel
• How to Calculate Average of Multiple Ranges in Excel
• How to Average a Column in Excel
• How to Average Every Nth Row in Excel
• How to Find Average of Specific Cells in Excel
• How to Exclude a Cell in Excel AVERAGE Formula
• How to Fix Divide by Zero Error for Average Calculation in Excel
• How to Ignore #N/A Error When Getting Average in Excel
• [Fixed!] AVERAGE Formula Not Working in Excel

<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel