Aveageif containing blank value => return #VALUE!

[garga]

Hi guys,

After spending couple of hours trying to find a solution to my problem, I ask for the first time to professionals

My data

Team 9/05/2016 9/05/2016 10/05/2016 13/05/2016 14/05/2016 16/05/2016
Ben
Ben 3.00
Ben 1.12
Ben 1.08
Michel 1.08
Ben 0.49

My formula : =AVERAGEIFS(B2:G7,A2:A7,"Ben")

I presume it is linked to the fact I have empty cells but I can't fix it and it's driving me crazy !!

Please help !

Thanks and respect in advance to my hero !
Cyrille

 

[garga]

Thanks for your answer Brian,

Nevertheless, even if I changed it by =AVERAGEIFS(A2:G7,A2:G7,"Ben") I get a #DIV/0!

What shall I do then ?

Thanks in advance
Cyrille

 

[garga]

What are you trying to average, or what criteria must be met before averaging?

If A2:G7 is your header row, there is nothing to average.

If A2:A10 contains Ben and B2:B10 values, AVERAGEIF would be sufficient

=AVERAGEIF($A$2:$A$10,"BEN",$B$2:$B$10)

=

I want just an average when the Name "Ben" is met. The problem is that I have empty cells in my range, therefore it doesn't work !

Any thoughts ?

Thanks in advance mate

 

[Brian from Maui]

I want just an average when the Name "Ben" is met. The problem is that I have empty cells in my range, therefore it doesn't work !

Any thoughts ?

Thanks in advance mate

Why doesn't it work?

Average disregards empty cells in the range.

What have you tried?

 

[Marcelo Branco]

Maybe this...

	A​	B​	C​	D​	E​	F​	G​
1​	Team​	09/05/2016​	09/05/2016​	10/05/2016​	13/05/2016​	14/05/2016​	16/05/2016​
2​	Ben​
3​	Ben​	3,00​
4​	Ben​			1,12​
5​	Ben​		1,08​
6​	Michel​						1,08​
7​	Ben​	0,49​
8​
9​	Criteria​	Result​
10​	Ben​	1,4225​

<tbody>
</tbody>

put the criteria in A10

Array formula in B10
=AVERAGE(IF(A2:A7=A10,IF(ISNUMBER(B2:G7),B2:G7)))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[garga]

I have tried everything... !!

Here are my data:
Team 9/05/2016 9/05/2016 10/05/2016 13/05/2016 14/05/2016 16/05/2016
Ben
Ben 3
Ben 1.12
Ben 1.08
Michel 1.08
Ben 0.49

Try to make it work, personally I don't understand what is wrong.. what will you do ?

Thanks again !

 

[garga]

I have tried in another computer and it works... I really don't get it !!!

 

[Marcelo Branco]

If all the values are in the same day, 09/05/2016 for example, the AVERAGEIF formula suggested in post #4 should work.
Otherwise you should use an array formula as i suggested in post #7.

Please clarify if all the values are in the same day (column) or not.

M.

 

[garga]

Hi Marcelo,

I have tried your way and I get #DIV/0!....

I confirm the value are not the same date.. ! sometimes it will be filled, sometimes not !

 

[Marcelo Branco]

It worked perfectly for me. Have you confirmed the formula with Ctrl+Shift+Enter?

If you are not familiar with array formulas, take a look at
Array Formulas

M.