Nneed to calculate average of NON sequential multiple cells & I want to exclude values = 0 or Empty Cell with AVERAGEIF

Question

Nneed to calculate average of NON sequential multiple cells and I want to exclude values = 0 or Empty Cell with AVERAGEIFS.

There is a mistake in my formula but I can't find a way to have it since value are NOT in sequencial order:

=AVERAGEIFS($C$9,$C$19,$C$29,$C$39,$C$49,$C$59,$C$69,$C$79,$C$89,$C$99,$C$109,$C$119,$C$129,$C$139,$C$149,$C$159,$C$169,$C$179,$C$190,$C$200,$C$210,$C$220,$C$231,$C$241,$C$251,$C$261,$C$271,$C$281,$C$291,$C$301,$C$311,$C$321,$C$331,$C$341,$C$351,$C$361,$C$371,$C$381,$C$391,$C$401,$C$411,$C$421,$C$431,$C$441,$C$451,$C$461,$C$471,$C$481,$C$491,$C$501,$C$512,$C$522,$C$532,$C$542,$C$552,$C$562;"<>0")

Any help ?

Regards !

Solution

AVERAGEIF/AVERAGEIFS cannot be used with non-contiguous ranges.

Instead, you can calculate average by taking the SUM and count of non-zero cells. You may have to adjust the -0.01 if you have small negatives:

=SUM(theRange)/(SUM(INDEX(FREQUENCY((theRange),0),2),INDEX(FREQUENCY((theRange),-0.01),1)))

If you won't have any negative numbers, you can use the simpler:

=SUM(theRange)/INDEX(FREQUENCY((theRange),0),2)

Comments

[Khado Mikhal]

@mcalex I found something here using Averageif for non-continuous ranges ---> tinyurl.com/lm2jpub but I'm not able to apply it ??? could you help me cause I'm not very good with excel ? Thx

[Khado Mikhal]

I found something here using Averageif for non-continuous ranges ---> tinyurl.com/lm2jpub but I'm not able to apply it ??? could you help me cause I'm not very good with excel ? Thx