10

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?

Matthias Vanb
  • 923
  • 5
  • 14
  • 32

4 Answers4

20

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.

Wally
  • 407
  • 2
  • 6
  • Is there also a way to use the function for certain cells and not just for a range between 2 cells? Thanks. – Matthias Vanb Apr 01 '14 at 13:48
  • @MatthiasVanb, I'm not sure. I'm still testing things out, but for the most part in the past I would just make a new range that references all the non-contiguous cells. Then with this new, compact range I would then perform operations like averaging. – Wally Apr 01 '14 at 13:53
  • what about negative values? – Dmitry Pavliv Apr 01 '14 at 14:10
  • To enable this formula to work with negative values and still exclude zero, just use the criteria "<>0". – Wally Apr 01 '14 at 14:11
  • @Wally Thanks. Ps, is it normal that it doesnt work with =AVERAGEIF(C1:C5,">0") but it does with =AVERAGEIF(C1:C5;">0")? – Matthias Vanb Apr 01 '14 at 14:12
  • @MatthiasVanb, the two formulas you just asked about look like the only difference is the second formula uses a ; semicolon to delimit the values passed to Averageif. The semicolon won't work for me, period (Excel 2010). If you meant to ask, "Is it normal that it doesn't work with ">0" but does work with "<>0", I don't know. It is doing what you tell it to do. If you need to include negative numbers but still not zero, use "<>0" – Wally Apr 01 '14 at 14:16
  • @Wally I meant the semicolon ;) Thanks for the answer! – Matthias Vanb Apr 01 '14 at 14:21
  • @MatthiasVanb, ah, now I see your original question had semicolons! Yes, semicolons in all my Excel experience aren't used as delimiters. I had glossed over those punctuations when reading your question, lol. – Wally Apr 01 '14 at 14:27
  • delimiter type is determined by regional settings: UK and US use commas, much of Europe, e.g. Germany use semi-colon separators – barry houdini Apr 01 '14 at 14:46
  • @barryhoudini, thanks for the explanation! What we can conclude from this discussion is that the same delimiter cannot be used within the range of cells and between the range and the criteria. And about the OP's question about separate cells being used for the average-function, I don't think it's possible. They have to be continguous. – Wally Apr 01 '14 at 14:52
  • @MatthiasVanb Success! With Barry's help, the formula has been updated and it is indeed possible. – Wally Apr 01 '14 at 15:00
  • @Wally - I don't believe that works, if you use `=AVERAGEIF(B1:B1:B3:B3:B5:B5:B7:B7,">0")` that will include the whole range `B1:B7` - to only average specific cells see my answer – barry houdini Apr 01 '14 at 15:05
  • @barryhoudini, thanks for the fact-checking! You're right. When I added some random numbers between my data points for the fruit in my example, it changed the average. – Wally Apr 01 '14 at 15:14
  • 1
    @MatthiasVanb check out the new answer for the question-- collab effort between Harry and me. – Wally Apr 01 '14 at 15:18
6

For an average of non-contiguous values, excluding any zeroes, try this formula

=IFERROR(SUM(A10;B13;C5;D6;D8)/((A10<>0)+(B13<>0)+(C5<>0)+(D6<>0)+(D8<>0));0)

or assuming no negative values you can use this

=IFERROR(SUM(A10;B13;C5;D6;D8)/INDEX(FREQUENCY((A10;B13;C5;D6;D8);0);2);0)

I used semi-colon delimiters as per question, change to commas if your regional settings demands

barry houdini
  • 45,615
  • 8
  • 63
  • 81
2

I found myself working on something that I wanted to compute a formula only with the cells that are not empty. To accomplish this, I used the following:

=IF(COUNT(DATA),COMPUTE(DATA),"")

(where COMPUTE is the formula or computation you want to make, and DATA is the selection of data you have--in my case a column of data).

To make sure the cells are empty/zero/something when not a "normal value" you can use something like: =IF(CONDITION,NORMAL VALUE,"")

Example:

=IF(SUM(DATA)=0,"",SUM(DATA))

This will only sum the data if it is non-zero, and other cells will see this cell as blank when the data is zero.

Raj
  • 138
  • 1
  • 11
0

Averageif using Indirect

After failing about three times with my other answer for this question, this answer should be ready for inspection:

=AVERAGEIF(INDIRECT({"b1","b3","b5","b7"}),">0")

Averageif and Indirect

Note: the localization of my Excel is United States Excel 2010. My delimiter is a comma, so adjust per your locale. If you would like to also average negative numbers, use "<>0" as the last value in the Averageif function.

How it works

The Indirect function makes a custom range of just the cells specified by (in this case) an array of strings. Averageif only adds items to its count if the criteria is met.

Community
  • 1
  • 1
Wally
  • 407
  • 2
  • 6