Solved: Average if

[neditheg]

hello,

I have this issue.

I need to obtain the average value of 10 cells excluding the blank cell or those cells which are equal to zero.

for example , i need to get the average value of
a2,c2,e2,g2,h2,i2,n2,o2,p2 and a2=4,c2=3 and e2=7, other cells are 0 for the moment.

what formula I need to use to get the right average value?

thanks!

[kpark]

=AVERAGEIF(A2,C2,E2,G2,H2,I2,N2,O2,P2,"<>0")

[Bob Phillips]

This formula should do it

=AVERAGE(IF(N(OFFSET(A2,0,{0,2,4,7,8,13,14,15},1,1))<>0,N(OFFSET(A2,0,{0,2, 4,7,8,13,14,15},1,1))))

[neditheg]

Hello again,

I've attached an example ..because i don't understand xld formula.

In the first sheet in the blue cell I should get 3.67 instead of 0.92.

If you have time , maybe you will apply the formula on my example.

thanks!

[Bob Phillips]

You want

=AVERAGE(IF(N(OFFSET(Evaluare!A15,0,{4,7,10,13,16,19,22,25},1,1))<>0,N(OFFS ET(Evaluare!A15,0,{4,7,10,13,16,19,22,25},1,1))))

[neditheg]

I get the "#Name?" error what should I do?

[neditheg]

done it! thanks!!

[Bob Phillips]

Aaah, the forum injected a space in the second OFFSET

[neditheg]

Ok I marked it as solved.. but I need the same thing for the next cells

I'm trying by myself but I'm not very sure that I'll get the right result!

thanks!

[neditheg]

hmm think I get it! !

[Bob Phillips]

Did you just change both Evaluare!A15 to Evaluare!A18?

[neditheg]

yap I had blank cells in the second sheet and I got #div0! error

thanks!