Calculate average excluding zeros in a non continguous range

[PostMaloney]

Hi guys,

I have a question about a data set of a hotel. We are calculating the average ADR per year per segment. Normally I would use the function Average and select from every year the first number. Divide it by 7 and there is my answer. Double click on the number and I have all the averages for that segment.

However the problem now is; some rows have an ADR of 0 in it. This will effect the total average. I know that the formula AverageIF can be used. But I do not know how to fill in the range, criteria and (average range) so it does not include the zero's.
The difficulty here is that I am working with a Non-Continguous range.

Can you help me?

[Glenn Kennedy]

So what exactly are you trying to average? What is a segment?

[PostMaloney]

The average of the ADR for every day. Attached you can find an example of 1 segment: corporate individual.

[mehmetcik]

=sum($e$5:$e$200)/(rows($e$5:$e$200)-countif($e$5:$e$200,0)+countif($e$5:$e$200,""))

[Bo_Ry]

Is this right?

G5
=SUMPRODUCT((MOD(ROW(),17)=MOD(ROW($C$4:$C$999),17))*$E$4:$E$999)/SUMPRODUCT(--((MOD(ROW(),17)=MOD(ROW($C$4:$C$999),17))*$E$4:$E$999>0))

[PostMaloney]

Hi thank you for your reaction! Much appreciated.

The formula helped but I cannot apply it to other data, because I do not fully understand the formula. So for example, what does the 17 stand for? How can we apply this formula to the other data without getting an error.

Looking forward to hearing from you

[Bo_Ry]

17 is from row(C4) 2012 and row(C21) 2013 21-4 = 17. Your data is repeat every 17 rows.

Just change this Number accordingly.

[PostMaloney]

Thank you so much! It worked out. And could you help us with the MEDIAN formula as well? it is the exact same data but then the Median.

[Bo_Ry]

Please try at J5 and Press Ctrl+Shift+Enter

=MEDIAN(AGGREGATE(14,6,INDEX(E5:E120,N(IF(1,ROW(INDIRECT("1:"&COUNT(C:C)))*17-16))),ROW(INDIRECT("1:"&SUM(COUNTIF(OFFSET(E5,ROW(INDIRECT("1:"&COUNT(C:C)))*17-17,),">0"))))))