PDA

View Full Version : Solved: Average if



neditheg
05-17-2012, 07:39 AM
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
05-17-2012, 07:44 AM
=AVERAGEIF(A2,C2,E2,G2,H2,I2,N2,O2,P2,"<>0")

Bob Phillips
05-17-2012, 07:48 AM
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
05-18-2012, 02:48 AM
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
05-18-2012, 07:43 AM
You want

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

neditheg
05-18-2012, 10:38 PM
I get the "#Name?" error :) what should I do?

neditheg
05-18-2012, 10:39 PM
done it! thanks!!

Bob Phillips
05-19-2012, 01:55 AM
Aaah, the forum injected a space in the second OFFSET :)

neditheg
05-21-2012, 01:07 AM
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
05-21-2012, 01:19 AM
hmm think I get it! !

Bob Phillips
05-21-2012, 02:12 AM
Did you just change both Evaluare!A15 to Evaluare!A18?

neditheg
05-21-2012, 02:13 AM
yap :) I had blank cells in the second sheet and I got #div0! error :)

thanks!