Excel: Average If in Non-Contiguous Cells

Hello,

I don't see the point in reviving a discussion from 2010 that has been marked as resolved regarding a solution proposed by Lermite, whom I salute, and that perfectly meets the request with AVERAGEA()
otherwise, without resorting to value groupings, there is

=SUM(D2;D5;D7;H2)/(COUNTIF(D2;">"0")+COUNTIF(D5;">"0")+COUNTIF(D7;">"0")+COUNTIF(H2;">"0))
Cheers,
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

Well, I think I've found a solution..
The AVERAGEA function averages non-contiguous ranges/cells but doesn't ignore empty cells unless told to.
I tested with 4 cells... =AVERAGEA(D2;D5;D7;H2)
I filled 3 cells with 5, 6, and 7, leaving the 4th empty.. = 6
But it doesn't work with a zero.
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)

The response is indeed a bit late, but it might help others: I found a way to work around the problem today:

If the issue is that the cells are not contiguous, you need to make them contiguous. On another sheet or far from the "clean" presentation or even in hidden cells, do =cellInQuestion. And use that for the average if needed.

For example, if we want the average of the positive values of cells A1, B3, C8, J9, C6, we do (the I marks a change of cell)

I =A1 I =B3 I =C8 I =J9 I =C6

Then we select the range for its average.if, and it works.

For example, I had to average the positive values of every other column, a few columns away I did
AA3=D3
AB3=E3
AC3=G3
AD3=I3 etc.

Then I selected the range AA3:AD3, which makes =Average.if(AA3:AD>0;AA3:AD3) and it's done.

Hoping this helps someone!

Hello,

It seems to me that with Excel 2007 and later, you need to use AVERAGEIFS()

Have a nice day.

Hello,
Same as the example you showed with Average.IF
A+
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-)(Confucius)

In F2, when I type =AVERAGEIF(D4;F4;H8;K8;"<>0") Excel responds that I have too many arguments. How can I do it?

If I type in F2: AVERAGEIF(D4:F4:H8:K8;"<>0") the formula is correct but it takes all the cells between D4 F4 F8 H8 K8 and I only want it to take the relevant cells D4 F4 H8 K8.

L'erreur dans votre formule Excel vient de la façon dont vous utilisez les critères dans la fonction MOYENNE.SI. La syntaxe correcte pour vérifier si une cellule n'est pas égale à zéro devrait être :

```excel
=MOYENNE.SI((G24;G71;G115;G159;G203;G247;G291;G335;G379;G423);"<>0")
```

Assurez-vous également de respecter le format des arguments. Vérifiez que vous utilisez les bons séparateurs (point-virgule ou virgule) en fonction de la configuration de votre version d'Excel.

Si toutes les cellules contiennent le chiffre 10, la formule devrait renvoyer 10 comme résultat, à condition que le format soit correct.

Hello,
I don't have XL2007 but I would tend to do this

with the ctrl key pressed, I select the cells and
I go to insert-name- and I name it "commetuveux"

moreover I would have written "<>"&0 instead of "<>0"

so, subject to

Michel

I tried Michel_m's suggestion and typed:
=AVERAGEIF(OLE_LINK1,"<>",0)
the answer gives me the following error: #REF!

Sumproduct-count I don't know.....

The goal is to calculate an average in Excel 2007 of a set of non-contiguous cells that should not take into account the empty cells or those equal to 0 in its calculation.

Good evening
thank you all for your solutions, I will try the AVERAGEA function and I will keep you updated
@+ and thanks