AverageIF on separate values without 0

[NicBKK]

Hello Gurus,

I am trying to average My ADR BAR columns without the 0 values.
My problem is that the data is not continuous and my formula returns #VALUE! (excel Attached)

I'm not sure what I'm doing wrong

Thanks a lot

[etaf]

try
=(E6+I6+M6)/((E6<>0)+(I6<>0)+(M6<>0))
http://cdn.na.sage.com/SageMail/MAS%...us%20range.pdf

I dont know what Without 0, without 1 or without 2 means

[davsth]

inelegantly
=(E6+I6+M6)/(1*(E6>0)+1*(I6>0)+1*(M6>0))

[NicBKK]

Thanks a lot guys, that worked perfectly 😊

[Bo_Ry]

Plaese try at Q6
=AVERAGEIFS(E6:M6,E6:M6,">0",E$5:M$5,E$5)

[Limor_OP]

Hi,
Try this in Q6 and down:

=SUM(($C$5:$P$5=$E$5)*(TEXT($C6:$P6,"0;;0;\0")))/COUNTIFS($C$5:$P$5,$I$5,$C6:$P6,">0")

[Limor_OP]

Thanks a lot guys, that worked perfectly 😊

Please keep in mind that in solutions # 2&3 you will have to select manually the cells to calculate the average on.
If you have a large range - it might be less practical.

[wk9128]

Q6 cell , array formula