View original (French)

AVERAGEIF issue in Excel 2003

Solved
yoyo65000 Posted messages 4 Status Member -  
yoyo65000 Posted messages 4 Status Member -
Hello,

I would like to calculate an average of a column based on the week number.
Let me explain

Column A Days
Column B week number
Column J analysis result

I would like to calculate the average of column J based on the week number in column B.

Thank you in advance for your solutions

Configuration: Windows / Chrome 71.0.3578.98

2 answers

  1.
    DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
     
    Hello,

    {=average(if(b2:b10=5;j2:j10))}

    array formula, so it must be validated by
    Shift+Ctrl+Enter (all 3 keys at the same time) to display the curly braces

    Regards
    0
    1. yoyo65000 Posted messages 4 Status Member
       
      Thank you, it works.
      The problem is that it considers blank cells in the calculation, which gives me an incorrect result.
      0
    2. DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
       
      {=average(if(b2:b10=5)*(j2:j10>0),j2:j10)}

      Best regards
      0
    3. yoyo65000 Posted messages 4 Status Member
       
      Hello,

      The formula is not working; it gives me an error at the =5 level.
      0
    4. DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
       
      {=AVERAGE(IF((B2:B10=5)*(J2:J10>0),J2:J10))}

      Did you know that there must be the same number of ( as there are )
      There was one missing
      Best regards
      0
    5. tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
       
      Hello:
      Just a small clarification: we assume that 0 is not a possible value.
      0
  2. yoyo65000 Posted messages 4 Status Member
     
    Hello,

    Thank you all, it works.

    For the ( yes I knew it but I didn't pay attention that one was missing.
    0