AVERAGEIF sans les div#0
auralexander
-
Raymond PENTIER Posted messages 58207 Registration date Status Contributor Last intervention -
Raymond PENTIER Posted messages 58207 Registration date Status Contributor Last intervention -
Hello,
I have a problem, I am a teacher and I calculate my averages using OpenCalc...
In my class, I have some students who do not have grades. Their averages therefore display DIV#0. However, I still want to know the class averages, which is impossible for me.
Of course, I can calculate an average by excluding the rows with DIV#0, but this technique forces me to reconfigure everything year after year.
I also saw that it was possible to make the DIV#0 not display. However, this technique does not suit me because it would take too long for me (I have 3 trimesters and 10 subjects to grade and several different average calculations)
What I really want is a formula that calculates the average WITHOUT these DIV#0 like =AVERAGE(A1:A10; EXCEPT (DIV#0)), I know it's not like that.
Does this exist?
Thank you in advance for your response
Configuration: Windows 7 / Internet Explorer 9.0
I have a problem, I am a teacher and I calculate my averages using OpenCalc...
In my class, I have some students who do not have grades. Their averages therefore display DIV#0. However, I still want to know the class averages, which is impossible for me.
Of course, I can calculate an average by excluding the rows with DIV#0, but this technique forces me to reconfigure everything year after year.
I also saw that it was possible to make the DIV#0 not display. However, this technique does not suit me because it would take too long for me (I have 3 trimesters and 10 subjects to grade and several different average calculations)
What I really want is a formula that calculates the average WITHOUT these DIV#0 like =AVERAGE(A1:A10; EXCEPT (DIV#0)), I know it's not like that.
Does this exist?
Thank you in advance for your response
Configuration: Windows 7 / Internet Explorer 9.0
5 answers
Yes
Thank you, but that doesn't work. Isn't there a method to calculate the average only of numbers while excluding the rest?
Furthermore, when using the average with matrices, it is incorrect (it gives me 13.5 when it should give me 14)
Thank you in advance.
Thank you, but that doesn't work. Isn't there a method to calculate the average only of numbers while excluding the rest?
Furthermore, when using the average with matrices, it is incorrect (it gives me 13.5 when it should give me 14)
Thank you in advance.
Hello
try with:
=AVERAGEIF(Score range,">=0",score field)
to avoid getting a #DIV/0 error in your calculations
=IF(B1=0,"",A1/B1)
then, the average code works.
best regards
Let's ask ourselves if we are not alone in understanding what is being explained?
try with:
=AVERAGEIF(Score range,">=0",score field)
to avoid getting a #DIV/0 error in your calculations
=IF(B1=0,"",A1/B1)
then, the average code works.
best regards
Let's ask ourselves if we are not alone in understanding what is being explained?
... I just realized that you were using Calc, and that AVERAGE.IF doesn't work!
So try this instead:
=SUMIF(notes_range,"<>")/COUNTIF(notes_range,"<>")
Apparently, it works.
Best regards
--
Let's ask ourselves if we are the only ones who understand what we are explaining?
So try this instead:
=SUMIF(notes_range,"<>")/COUNTIF(notes_range,"<>")
Apparently, it works.
Best regards
--
Let's ask ourselves if we are the only ones who understand what we are explaining?
Hello auralexander.
I just entered grades from A1 to A10 leaving some cells blank.
In A11 I enter the formula =AVERAGE(A1:A10) and it displays the correct average.
Even when writing "absent" in one of the cells, the formula remains accurate. What have you invented to create this error?
It might be useful for you to send us an excerpt of your file...
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
I just entered grades from A1 to A10 leaving some cells blank.
In A11 I enter the formula =AVERAGE(A1:A10) and it displays the correct average.
Even when writing "absent" in one of the cells, the formula remains accurate. What have you invented to create this error?
It might be useful for you to send us an excerpt of your file...
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
Hello forum
When you use the AVERAGE function, the number zero will be included in the calculation.
-To exclude zero, you need to use an array formula.
-Important: type the formula without the braces.
-After entering the formula, press CTRL+SHIFT+ENTER.
Result:
Or
Mytå
What an audacity to claim that computing is recent
Adam and Eve already had an Apple! [MsProject 2003(FR), Excel 2003-2007(FR)]
When you use the AVERAGE function, the number zero will be included in the calculation.
-To exclude zero, you need to use an array formula.
-Important: type the formula without the braces.
-After entering the formula, press CTRL+SHIFT+ENTER.
Result:
{=AVERAGE(IF(A1:A10,A1:A10))} Or
=SUM(A1:A10)/COUNTIF(A1:A10,"<>0")
Mytå
What an audacity to claim that computing is recent
Adam and Eve already had an Apple! [MsProject 2003(FR), Excel 2003-2007(FR)]