J.E. McGimpsey said:
The formula I gave you works for your situation.
First, it's an array formula, so when you enter the formula, you
need to hold down the CTRL and SHIFT keys while pressing ENTER
The first part checks to make sure that there is at least one
non-zero value. Since your values will all be zero or positive, you
can delete the ABS() function:
=IF(SUM(AF22:AI22),...
will return a value from 0 to 480. In boolean functions like IF(),
XL interprets 0 as FALSE and non-zero as TRUE, so if all values are
zero the IF statement will branch to the FALSE branch, or 0.
If there is at least one non-zero value, then the TRUE branch
AVERAGE(IF(AF22:AI22,AF22:AI22,""))
is taken. IF(AF22:AI22 will return an array of four values which
will be interpreted as TRUE or FALSE, as above. If the values
evaluate to TRUE, then the corresponding value from the array
AF22:AI22 will be returned (i.e, the same value), if it's zero, a
null string will be returned). Average ignores text, so will not
include the cell in the average.
For example: Assume
AF22: 10
AG22: 0
AH22: 70
AI22: 100
Stepping through the function:
SUM(AF22:AI22) = 180
which is evaluated as TRUE, so the true branch of the outer IF() is
taken.
Within the Average(IF(, AF22:AI22 returns the array
{10, 0, 70, 100}
which is also what is returned by the true branch of the IF()
function. Since IF evaluates arguments as boolean, this is the same
as
{TRUE,FALSE,TRUE,TRUE}
The first term is true so the IF will return the first value from
its true branch. Likewise, the second term is FALSE, so IF will
return the value from the false branch, or "". Evaluating all four
terms, the IF() returns:
{10,"",70,100}
to the Average function. Since Average ignores text, it returns
180/3 or 60.
)