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

Answer 1 / 2
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
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
DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
 
{=average(if(b2:b10=5)*(j2:j10>0),j2:j10)}

Best regards
0
yoyo65000 Posted messages 4 Status Member
 
Hello,

The formula is not working; it gives me an error at the =5 level.
0
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
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
Answer 2 / 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