How to AVERAGEIF not using a range of cells?

I've got a formula that will pull in a number. Sometimes that number will be 0. I want to get the average of the items not counting any zeroes. I want this =AVERAGEIF({formula1, formula2, etc},"<>0"). I haven't been able to figure out a way to feed AVERAGEIF individual values to average. I would think that =AVERAGEIF({3,7,0,4},"<>0") should be possible but again I haven't figured out how to make it work. BTW, the answer to the example should be 4.666 (i.e. 14/3). TIA

=AVERAGEIF({3,7,0,4},"<>0") is not possible, because AVERAGEIF needs a range and {3,7,0,4} is not a range but an array.

If you want to code the AVARAGE with an (hardcoded) array you can try: =LET(t,{3,7,0,4},AVERAGE(FILTER(t,t<>0)))

Perhaps it is just me -- who would simply put the individual formulas into cells in a helper range and then use =AVERAGEIF(helper_range,"<>0"). It seems to me we are unnecessarily trying to tie ourselves into knots to avoid the helper range.

If I were absolutely forbidden from using the helper range, I think I would use something like =AVERAGE(IF(formula1<>0,formula1,"text"),IF(formula2<>0,formula2,"text"),...). The IF() functions inside of the AVERAGE() perform each individual calculation and, when that calculation results in 0, replaces the number with a text string, which the AVERAGE() function will ignore when it computes the average.

Would something like that work for you?

If I understand correctly perhaps this

Formula:

=AVERAGE(IF({3,7,0,4}<>0,{3,7,0,4}))

It returns 4.666 at my end.

Originally Posted by FlameRetired

If I understand correctly perhaps this

Formula:

=AVERAGE(IF({3,7,0,4}<>0,{3,7,0,4}))

It returns 4.666 at my end.

Thanks but when I replace the numbers with my formula that returns a number, it doesn't work. Same problem that I report in post #4.

Actually that can be shortened to

Formula:

=AVERAGE(IF({3,7,0,4},{3,7,0,4}))

Time to have a look at the data rather than guessing. What is the formula that generated the numbers? Is it in fact generating text?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Average doesn't seem to ignore 'text'. The formula gives a #VALUE! error when one of the IF functions returns "text".

In the spirit of learning new things every day, I must have never encountered this behavior before. I am surprised.

Here's what I tried instead.

Average is sum/count, so I tried something based on that basic definition.

1) Zero values have no impact on the sum, so a simple SUM(formula1,formula2,formula3,formula4) is enough to get the sum.
2) The count of non-zero values is trickier. First I reduce each formula to a value of 1 or 0. I can use the SIGN() function for this (or ABS(SIGN()) if the formulas can return negative values). SIGN(formula1),SIGN(formula2),...
3) A simple SUM() function can now add up the results of the SIGN() functions to get the count SUM(SIGN(formula1),SIGN(formula2),...)
4) The average is the ratio of step 1 and step 3 =SUM(formula1,formula2,...)/SUM(SIGN(formula1),SIGN(formula2),...)

That should work. The only problem I can foresee with this approach is if your formulas are subject to floating point errors so that what should be exactly 0 is not exactly 0. In that case, some adjustment will need to be made to force the "should be exactly 0" values to be "exactly 0."

Originally Posted by MrShorty

In the spirit of learning new things every day, I must have never encountered this behavior before. I am surprised.

Here's what I tried instead.

Average is sum/count, so I tried something based on that basic definition.

1) Zero values have no impact on the sum, so a simple SUM(formula1,formula2,formula3,formula4) is enough to get the sum.
2) The count of non-zero values is trickier. First I reduce each formula to a value of 1 or 0. I can use the SIGN() function for this (or ABS(SIGN()) if the formulas can return negative values). SIGN(formula1),SIGN(formula2),...
3) A simple SUM() function can now add up the results of the SIGN() functions to get the count SUM(SIGN(formula1),SIGN(formula2),...)
4) The average is the ratio of step 1 and step 3 =SUM(formula1,formula2,...)/SUM(SIGN(formula1),SIGN(formula2),...)

That should work. The only problem I can foresee with this approach is if your formulas are subject to floating point errors so that what should be exactly 0 is not exactly 0. In that case, some adjustment will need to be made to force the "should be exactly 0" values to be "exactly 0."

This formula works. Anybody know why the other formulas don't?

The AVERAGE function ignores TEXT-VALUES, but does not ignore zero-values.

I'll wait for an example-sheet of the OP before I post a solution.

@HansDouwe: You are correct when the average values are in cells and you are using a reference or references to those cells inside of the AVERAGE() function. I have always done my averages this way and never had trouble ignoring text values.

However, remember that the OP, for some reason known only to the OP, wants to avoid at all costs storing the values to be averaged in cells. They want to nest the formulas that calculate the values to be averaged inside of the AVERAGE() function -- while also ignoring those cases where the formula returns 0. When you nest something like IF(formula=0,"text",formula) inside of the AVERAGE() function, an error is triggered.