Table of Contents
You can use the following formula in Excel to perform some calculation only if specific cells are not blank:
=IF(COUNTBLANK(B2:D2),"",AVERAGE(B2:D2))
This particular formula calculates the average value of cells in the range B2:D2 only if none of the cells in the range are blank.
If even one cell is blank, then the formula will not calculate the average and will instead return a blank.
The following example shows how to use this formula in practice.
Example: How to Calculate Only if Cells are Not Blank in Excel
Suppose we have the following dataset in Excel that shows the number of points scored by various basketball players during three different games:
We can type the following formula into cell E2 to calculate the average points scored per game for the first player, only if all cells in the range B2:D2 are not blank:
=IF(COUNTBLANK(B2:D2),"",AVERAGE(B2:D2))
We’ll then click and drag this formula down to every remaining cell in column E:
Column E displays the average points per game for each player only if none of the games have a blank value.
For example:
- Andy scored an average of (22+44+30) / 3 = 32 points per game.
- Bob scored an average of (14+28+12) / 3 = 18 points per game.
- Chad had a blank value for game 1 so his average was not calculated.
And so on.
Note: In this particular example we calculated the average, but you can replace AVERAGE with whatever metric you’d like to calculate.
How This Formula Works
Recall the formula that we used to calculate the average value in the range B2:D2:
=IF(COUNTBLANK(B2:D2),"",AVERAGE(B2:D2))
Here is how this formula works:
First, we use the COUNTBLANK function to count the number of blank cells in the range B2:D2.
Then we use an IF function to return the following values:
- If the number of blank cells is greater than zero, return blank.
- Otherwise, return the average of cells in the range B2:D2.
This formula allows us to calculate the average points per game in each row only if there are no blank cells in a given row.