Average from 5 cells if not blank or zero

excel average zero
Matthias Vanb picture Matthias Vanb · Apr 1, 2014 · Viewed 50.5k times · Source

I need to calculate an average of 5 cells, but if a cell is blank or zero, it should neglect this value.

I am not able to get it to work with 

=AVERAGEIFS(A10;B13;C5;D6;D8;"<>0")

Does anyone know the correct way to calculate this?

Answer

Wally picture Wally · Apr 1, 2014

You are looking for "Averageif": Excel showing averageif

Specifically, you want to use the range that includes possible blanks and then for the criteria use ">0"

=AVERAGEIF(C1:C5,">0")

Update: Non-contiguous ranges (not all working)

In the comments for this answer is a discussion about localization. My locale is United States (Excel 2010), so my delimiter between values passed to a function is the comma ,

Performing an averageif function on non-contiguous ranges is possible:

=AVERAGEIF(B1:B1:B3:B3:B5:B5:B7:B7,">0")

Excel 2010 comma-delimited averageif

For your locale, you might need to adjust delimiters, but the key thing is for the selection of individual cells, use the format "C1:C1:D4:D4" for the individual cells C1 and D4. The engine must be parsing the references as pairs.

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I'm trying to get data from an Excel file on a button click event. My connection string is: string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data.xls;Extended Properties=Excel 8.0;"; When I click on the button, …

Read More