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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.