Excel: Average If in Non-Contiguous Cells
Solved
yapluka
Posted messages
50
Status
Member
-
flavien -
flavien -
Hello,
After several fruitless searches on the internet, here is my question:
How can I calculate the average of non-contiguous cells while excluding empty cells or cells equal to 0?
When I type this formula: =AVERAGEIF(D4;F4;H8;K8;"<>0") Excel responds that I have too many arguments. How can I do this?
Thank you for your suggestions
Best regards
Jean Yves
After several fruitless searches on the internet, here is my question:
How can I calculate the average of non-contiguous cells while excluding empty cells or cells equal to 0?
When I type this formula: =AVERAGEIF(D4;F4;H8;K8;"<>0") Excel responds that I have too many arguments. How can I do this?
Thank you for your suggestions
Best regards
Jean Yves
10 answers
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.
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.
Bassy
Hello Mike-31 and thank you. Only you were able to EXACTLY solve this issue with AVERAGE.IF on non-contiguous cells.
flavien
Excellent solution Mike-31!
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 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!
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,
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)
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.
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.
```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.
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.
=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.
I should have marked "AT MY PLACE WITH 2003," the attempts did not work with discontinuous cells...
by the way, a thank you would still not have been too much to ask
G indicated the right function to you!!!
if you see g as an amateur (nice of you to think so)
maybe look
https://excel.developpez.com/faq/index.php?page=Formules#MoyenneSiens
these are MVPs (most valuable professionals) from Microsoft who wrote that, but it's up to you to continue with moyenne.si
just do it
by the way, a thank you would still not have been too much to ask
G indicated the right function to you!!!
if you see g as an amateur (nice of you to think so)
maybe look
https://excel.developpez.com/faq/index.php?page=Formules#MoyenneSiens
these are MVPs (most valuable professionals) from Microsoft who wrote that, but it's up to you to continue with moyenne.si
just do it
Hello Michel,
The IF.ENS formula won't work either; it allows adding up to 32 criteria but on a SINGLE RANGE.
I only see a VBA function to solve this problem.
If there were no conditions, he could use the AVERAGEA() formula.
Maybe a matrix formula that would combine this formula with IF, but I'll leave that to the formula professionals.
See you!
The IF.ENS formula won't work either; it allows adding up to 32 criteria but on a SINGLE RANGE.
I only see a VBA function to solve this problem.
If there were no conditions, he could use the AVERAGEA() formula.
Maybe a matrix formula that would combine this formula with IF, but I'll leave that to the formula professionals.
See you!
Hello hermit,
here is the copy-paste from the link I mentioned
This conditional function allows you to calculate the average of a range that meets several criteria.
You can specify up to 127 criteria in the formula.
Syntax
AVERAGEIFS(average_range, criteria_range1, criterion1, criteria_range2, criterion2...)
An example that displays the average of the range C1:C10 for the data in the range B1:B10 between 20 and 40
Formula
=AVERAGEIFS(C1:C10, B1:B10, ">20", B1:B10, "<40")
note that all that remains is to use average.if....
here is the copy-paste from the link I mentioned
This conditional function allows you to calculate the average of a range that meets several criteria.
You can specify up to 127 criteria in the formula.
Syntax
AVERAGEIFS(average_range, criteria_range1, criterion1, criteria_range2, criterion2...)
An example that displays the average of the range C1:C10 for the data in the range B1:B10 between 20 and 40
Formula
=AVERAGEIFS(C1:C10, B1:B10, ">20", B1:B10, "<40")
note that all that remains is to use average.if....