AVERAGE ignore 0 (zero)
The array formula in cell D3 calculates an average and ignores 0 (zeros).
The formula above is an array formula. To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
The IF function checks if values in B3:B8 are not equal to 0, it returns the value if TRUE and returns a blank if it is FALSE.
becomes
and returns
The AVERAGE function then calculates the average of the numbers in the array, it ignores the blank values.
returns 4. 3 + 5 +4 +4 = 16. 16/4 = 4.
Download Excel *.xlsx file
Calculate running average of last 10 data with random blank cells
Question: List of data and blank cells in a column which will be added from day to day. There are [โฆ]
Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes [โฆ]
#N/A error is sometimes used to show gaps in charts, however, the AVERAGE function can't process errors, shown in C11 [โฆ]
How to use the AVERAGE function
Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and [โฆ]
The AVERAGE function is designed to ignore blank cells but there are instances where it fails. The picture above seems [โฆ]
The array formula in cell D14 calculates an average based on multiple criteria in cell range B14:B15. If value in [โฆ]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
){kind=link}