Average in OOo calc with empty cells
kris22170
Posted messages
184
Status
Member
-
kris22170 Posted messages 184 Status Member -
kris22170 Posted messages 184 Status Member -
Hello,
I'm making a table where there are empty cells in one column, and I want to calculate the average of this column.
I've used the function =AVERAGE(I5:I40), but in reality, about twenty cells are filled.
Is there a solution because the result is incorrect with this function?
Thank you in advance.
P.S: I'm not very good at math.
I'm making a table where there are empty cells in one column, and I want to calculate the average of this column.
I've used the function =AVERAGE(I5:I40), but in reality, about twenty cells are filled.
Is there a solution because the result is incorrect with this function?
Thank you in advance.
P.S: I'm not very good at math.
7 answers
Re
I don't know why my previous message disappeared and I can't delete the empty message?
So I was saying:
Calc does not take empty cells into account in the average, which is normal
However, it will consider 0 values entered in cells deemed empty.
If you want an average that includes all cells, including the empty ones from I5 to I40, use:
=SUM(I5:I40)/36
Regards
--
To err is human, to persist is diabolical.
I don't know why my previous message disappeared and I can't delete the empty message?
So I was saying:
Calc does not take empty cells into account in the average, which is normal
However, it will consider 0 values entered in cells deemed empty.
If you want an average that includes all cells, including the empty ones from I5 to I40, use:
=SUM(I5:I40)/36
Regards
--
To err is human, to persist is diabolical.
[IMG]http://imageshack.us/a/img441/382/moyenne.jpg[/IMG]
An image of what I want.
I do not want to refresh the overall average row (in red).
Thank you and I'm sorry if I'm not clear enough.
An image of what I want.
I do not want to refresh the overall average row (in red).
Thank you and I'm sorry if I'm not clear enough.
It will sort itself out, no problem,
but I still don't understand everything you want. Actually, it's a simple division if I believe the formula displayed??
So, assuming we start in line 5, why do = SUM(F5/D5) when it’s simply = F5/D5?
Of course, this formula returns an error when D equals 0.
So write in all of column I the formula as below:
=IF(D5=0,"",F5/D5)
And why not simply take the overall average with the same formula between F42 and D32???
And to avoid negatives in column D when there is no end date, write:
=IF(C5=0,0,C5-B5)
Get back to me if that doesn't work
Regards.
but I still don't understand everything you want. Actually, it's a simple division if I believe the formula displayed??
So, assuming we start in line 5, why do = SUM(F5/D5) when it’s simply = F5/D5?
Of course, this formula returns an error when D equals 0.
So write in all of column I the formula as below:
=IF(D5=0,"",F5/D5)
And why not simply take the overall average with the same formula between F42 and D32???
And to avoid negatives in column D when there is no end date, write:
=IF(C5=0,0,C5-B5)
Get back to me if that doesn't work
Regards.
Hello Vaucluse
Here is what it looks like now, with the explanation of the formulas for those who might find it useful.
http://img23.imageshack.us/img23/6861/consomgo1.jpg.
And on top of that, it gives an idea of the increase in fuel since November 1999 – 300%!!!!
Thank you very much.
Here is what it looks like now, with the explanation of the formulas for those who might find it useful.
http://img23.imageshack.us/img23/6861/consomgo1.jpg.
And on top of that, it gives an idea of the increase in fuel since November 1999 – 300%!!!!
Thank you very much.
1) As you can see, an image from a screenshot is not very interesting; it is much better to send the Excel file itself.
2) Line 31 causes a general error, with negative values in D31, E31, I31. The formula in D6 (to be copied down) should be written as =IF(C6="","",C6-B6); same formula structure in E, F, G, H, I.
3) The formula in I40 is unnecessarily complicated; it is enough to put
=AVERAGE(I6:I40)
--
Retirement is great! Especially in the Caribbean ... :-)
☻ Raymond ♂
2) Line 31 causes a general error, with negative values in D31, E31, I31. The formula in D6 (to be copied down) should be written as =IF(C6="","",C6-B6); same formula structure in E, F, G, H, I.
3) The formula in I40 is unnecessarily complicated; it is enough to put
=AVERAGE(I6:I40)
--
Retirement is great! Especially in the Caribbean ... :-)
☻ Raymond ♂
Thank you for all the information.
I have noted the recommendations, but I did not find how to send the Calc file.
Other than that, everything is working well.
Best regards.
I have noted the recommendations, but I did not find how to send the Calc file.
Other than that, everything is working well.
Best regards.