Share via

Average, excluding 0, with non-sequential cells

Anonymous
2023-11-25T20:14:16+00:00

I want to average P3, AF3, P17, AF17, P31 and AF31, but I want to ignore 0 values. The mentioned cells are populated with COUNTA formula and some will have 0 items to count.

I've tried using this formula: =AVERAGEIF(P3,AF3,P17,P31,AF31, "<>0") but I get TOO MANY ARGUMENTS error.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 102K Reputation points Volunteer Moderator
2023-11-25T22:59:56+00:00

Hi,

In cell X17, enter this formula

=LET(rng,INDIRECT(X4:X9),SUM(SUMIF(rng,"<>0",rng))/SUM(COUNTIF(rng,"<>0")))

Hope this helps.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2023-11-25T20:49:32+00:00

AVERAGEIF only works with a contiguous range.

So one option would be to use formulas to return the values of P3, AF3 etc. in a contiguous range of cells, and use that range in your AVERAGEIF formula.

Another one would be to use an awkward formula like this:

=AVERAGE(IF(P3<>0,P3),IF(AF3<>0,AF3),IF(P17<>0,P17),IF(P31<>0,P31),IF(AF31<>0,AF31))

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more