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 !
Best Answer
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:
If you won't have any negative numbers, you can use the simpler: