View original (French)

Average in OOo calc with empty cells

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.

7 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    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.
    0
  2. kris22170 Posted messages 184 Status Member 24
     
    Thank you for this response, but I just tested it and it doesn't work.
    Why divide by 36?
    I'm sorry.
    0
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Because we do not understand what you want, given what I specified in my message.
      Let us know if you want to include or exclude the empty cells in the column in the average or not.

      If you do not count them, the AVERAGE function works perfectly.
      Looking forward to hearing from you.
      0
  3. kris22170 Posted messages 184 Status Member 24
     
    [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.
    0
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      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.
      0
  4. kris22170 Posted messages 184 Status Member 24
     
    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.
    0
    1. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
       
      Attention, kris !

      When you paste a hyperlink, make sure not to add a period at the end!

      http://img23.imageshack.us/img23/6861/consomgo1.jpg does not work

      http://img23.imageshack.us/img23/6861/consomgo1.jpg works perfectly ...
      0
  6. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    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 ♂
    0
  7. kris22170 Posted messages 184 Status Member 24
     
    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.
    0
    1. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
       
      https://www.cjoint.com/
      0
  8. kris22170 Posted messages 184 Status Member 24
     
    Thank you, I had never heard of this site.
    0
    1. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
       
      It's simply because since your membership at CCM on March 3, 2010, this is your very first foray into the Office forum! This site is widely used there and provides us with unparalleled services ...
      See you soon :-)
      0
    2. kris22170 Posted messages 184 Status Member 24
       
      I come here from time to time to solve problems, but I don't necessarily go for office work. Sincerely
      0