K
Ken
Hi,
I am trying to evaluate the average of a range of cells if their values are
not 0 or "NA" (If they are 0 or "NA" then I want to ignore the cells as they
are not relevant to the average yet).
If i use the following formula it seems to work for one condition:
=AVERAGEIFS(B31:M31,B31:M31,"<>NA")
but I want to use this:
=AVERAGEIFS(B31:M31,B31:M31,"<>NA"AND<>0)
I've also tried this:
=AVERAGEIFS(B31:M31,B31:M31,"<>NA",B31:M31,<>0)
both say "The formula you have entered has an error" but it does not tell me
what the error is or how to correct it. Any ideas?
from the help file:
Syntax
AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Average_range is one or more cells to average, including numbers or names,
arrays, or references that contain numbers.
Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to
evaluate the associated criteria.
Criteria1, criteria2, … are 1 to 127 criteria in the form of a number,
expression, cell reference, or text that define which cells will be averaged.
For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.
I am trying to evaluate the average of a range of cells if their values are
not 0 or "NA" (If they are 0 or "NA" then I want to ignore the cells as they
are not relevant to the average yet).
If i use the following formula it seems to work for one condition:
=AVERAGEIFS(B31:M31,B31:M31,"<>NA")
but I want to use this:
=AVERAGEIFS(B31:M31,B31:M31,"<>NA"AND<>0)
I've also tried this:
=AVERAGEIFS(B31:M31,B31:M31,"<>NA",B31:M31,<>0)
both say "The formula you have entered has an error" but it does not tell me
what the error is or how to correct it. Any ideas?
from the help file:
Syntax
AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Average_range is one or more cells to average, including numbers or names,
arrays, or references that contain numbers.
Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to
evaluate the associated criteria.
Criteria1, criteria2, … are 1 to 127 criteria in the form of a number,
expression, cell reference, or text that define which cells will be averaged.
For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.