There are several distinct Microsoft Excel functions for determining the average of integer values. Additionally, there is a quick non-formula way. This article shows 4 easy ways to find average with blank cells in Excel. We will discuss a brief summary of each technique in this tutorial, along with usage examples and best practices. Every function covered in this article is compatible with Excel 2007 through the latest Excel 365 version.
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.
4 Easy Ways to Find Average with Blank Cells in Excel
An average is a number that represents the typical value within a dataset of data in everyday life. The average, which is determined by dividing the total of all the values by their number, is the mean or middle value in a group of numbers in mathematics. Although if we wish to take the empty cells into an account, we can use three functional methods and one VBA approach. To demonstrate, let’s take a dataset that represents the budget book of a sales company. See the picture and you will notice a blank cell in column D. In this tutorial, we will learn how to find the average of column E including the corresponding empty cells in column D.
1. Use AVERAGEIF Function to Find Average with Blank Cells in Excel
In the first step, we will use the AVERAGEIF function to average numbers with blank cells. The AVERAGEIF function determines an arithmetic mean from an array under a specified condition and returns it in a numerical value. Based on the corresponding column D, let’s find out the average of the column E values.
Steps:
- Firstly, add a row named Average in row 11.
- Now, in cell E11, type the following formula:
=AVERAGEIF(D5:D9,"",E5:E9)
- Here, the AVERAGEIF function compares our selections to D5:D9.
- Furthermore, it decides which cells within the given range shall be averaged through “”.
- Finally, it calculates the average from the range E5:E9.
- Afterward, press the Enter or Tab keys to find the output as $50,000.00.
- Thus, we obtain the average of the check amounts.
Read More: How to Find Blank Cells in Excel (8 Easy Ways)
2. Combine SUMPRODUCT and AVERAGE Functions to Find Average with Zero Cells
The second method aims to adjoin the SUMPRODUCT and AVERAGE functions to count the average including the empty cells. The AVERAGE function under the Statistical functions category returns the average of a given range. Furthermore, the SUMPRODUCT function takes one or more cell ranges as an argument, multiplies the corresponding values of all the ranges, and then returns the summation of the products. Let’s follow the below instructions to combine the functions and get better efficiency.
Steps:
- To begin with, write the formula down in cell E11,
=SUMPRODUCT(AVERAGE(0+E5:E9))
- Later, by pressing the Enter or Tab buttons, we get the output as $40,000.00.
- Hence, we get the average of the check amounts including blank cells.
Read More: How to Find Blank Cells Using VBA in Excel (6 Methods)
Similar Readings
- Fill Blank Cells with Text in Excel (3 Effective Ways)
- How to Fill Blank Cells with Formula in Excel (2 Easy Methods)
- Fill Blank Cells with Dash in Excel (3 Easy Ways)
- How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)
- Fill Blank Cells with Color in Excel (5 Methods)
3. Combine Excel SUM and ROWS Functions to Calculate Average Counting Null Cells
The objective of the third method is to combine the SUM and ROWS functions thoroughly to find the average taking the empty cells into account. The SUM function determines the summation of an array and returns an integer as an output. Alternatively, the ROWS function is a built-in Excel function under the LOOKUP and REFERENCE functions category that returns the number of rows existing within a given array. Let’s learn some steps to see how they work.
Steps:
- First, in cell E11, type the following formula:
=SUM(E5:E9)/ROWS(E5:E9)
- Further, press the Enter or Tab keys to get an output as $40,000.00.
- As a result, the average pop-up in the display also counts the empty cell.
- 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.
Read More: Find If Cell is Blank in Excel (7 Methods
4. Run VBA Code to Count Average Including Empty Cells in Excel
In our last method, we will run a VBA code to calculate the average with the blank cells. The VBA approach needs to use VBA Objects and specified parameters. Furthermore, we will also mention the specific worksheet names in the code. Let’s jump into the code to understand properly how the method works.
Steps:
- To begin with, go to the Developer tab and click the Visual Basic option.
- Subsequently, the Visual Basic window appears in the display.
- Further, tap the Insert tap and then Module to create a module box.
- In the 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
- After that, close the window to open the active sheet.
- Meanwhile, go to the Developer tab and tap Macros.
- Consequently, the Macro dialog box pops up.
- There, click the option in the Macro name box and hit the Run button.
- Lastly, the average appears as $50,000.00 in cell E11.
- 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.
Read More: Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
Conclusion
In conclusion, we have discussed some easy ways to find the average with blank cells in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.
Related Articles
- How to Apply Conditional Formatting in Excel If Another Cell Is Blank
- Remove Blanks from List Using Formula in Excel (4 Methods)
- How to Set Cell to Blank in Formula in Excel (6 Ways)
- Remove Blank Lines in Excel (8 Easy Ways)
- Return Non Blank Cells from a Range in Excel (3 Suitable Ways)
- Excel If Two Cells Are Blank Then Return Value
- VBA to Count Blank Cells in Range in Excel (3 Methods)
