Ignore Zeros in Aggregate Average Function

[WilliamPHII]

I get a feeling this might not be possible, but I have a table that uses the Aggregate function to average a column. It needs to be Aggregate, because that allows me to use slicers to see averages for different items in my table. The problem is, the Aggregate average is averaging the zeros and throwing it off. Is there anyway to prevent this?

If I were averaging the whole range, I'm aware I could be using AverageIf. The problem here is that I want a function that ignores hidden rows so it works with slicers.

Any brilliant ideas out there?

Thanks.

William

 

[vaskov17]

How are the zeroes entered in the table? Also do other formulas exist that need the zeroes to be there?

 

[WilliamPHII]

I think you're right we could do something about how the zeros get there... I simply wanted to pose the question that IF the zeros ARE there, is there any way to avoid them in a slice-able way?

 

[Aladin Akyurek]

Not sure I understand your request correctly. Here two possible interpretations:

1. The data is autofiltered:

Book1
	A	B	C
1	x	y	z
2	zad	a	2
3	zad	a	3
4	zad	b	5
9	zad	a	0
11
12	a
13	2.5
Sheet1

In A13, control+shift+enter, not just enter:

=AVERAGE(IF($B$2:$B$10=A12,IF(SUBTOTAL(2,OFFSET($C$2,ROW($C$2:$C$10)-ROW($C$2),0)),IF(ISNUMBER($C$2:$C$10),IF($C$2:$C$10>0,$C$2:$C$10)))))

This computes a conditional average while data area in autofiltered on column A.

2. A conditional average without autofilter:

Control+shift+enter, not just enter...

=AVERAGE(IF(ConditionRange=Condition,IF(ISNUMBER(AverageRange),IF(AverageRange>0,AverageRange))))

 

[WilliamPHII]

Wow! I must admit I thought I wouldn't get an answer, but yours seems to work! Amazing. I'm going to need some time to work through the formula so I understand it, but very impressive. Thanks so much!

 

[Aladin Akyurek]

Wow! I must admit I thought I wouldn't get an answer, but yours seems to work! Amazing. I'm going to need some time to work through the formula so I understand it, but very impressive. Thanks so much!

You are very welcome. Thanks for providing feedback.