AVERAGEIF to remove zeros

[ColleenMarie47]

Good Morning,

I am trying to use the following formula and I keep getting a #VALUE ! error:

AVERAGEIF(Firm1:Firm70!E46, "<>0")

I am trying to get an overall average for a particular survey question, but I want to exclude zeros. I have each firm's submission in a separate tab. Any clue what I am doing wrong?

 

[Domenic]

First, list your sheet names in a range of cells, let's say G1:G70. Then try the following array formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(N(INDIRECT("'"&$G$1:$G$70&"'!E46"))<>0,N(INDIRECT("'"&$G$1:$G$70&"'!E46"))))

Hope this helps!

 

[Aladin Akyurek]

First, list your sheet names in a range of cells, let's say G1:G70. Then try the following array formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(N(INDIRECT("'"&$G$1:$G$70&"'!E46"))<>0,N(INDIRECT("'"&$G$1:$G$70&"'!E46"))))

Hope this helps!

Hi Domenic,

The following appears to work with the 3D specifications too...

=SUM(Firm1:Firm70!E46)/INDEX(FREQUENCY(Firm1:Firm70!E46,0),2)

which requires just enter.

Aladin

 

[Domenic]

Hi Domenic,

The following appears to work with the 3D specifications too...

=SUM(Firm1:Firm70!E46)/INDEX(FREQUENCY(Firm1:Firm70!E46,0),2)

which requires just enter.

Aladin

Hi Aladin,

Oh yes, much better..

 

[Sean15]

@Aladin - brilliant as usual. But could you give some insight on what your formula is doing? Thanks.


Regards,

Sean

 

[TimNevins]

Does correcting the criteria in the original formula work?

Original formula - AVERAGEIF(Firm1:Firm70!E46, "<>0")

The criteria should be "<>"&0

 

[Aladin Akyurek]

FREQUENCY sorts values into bins: FREQUENCY(values,bins).

In

=SUM(Firm1:Firm70!E46)/INDEX(FREQUENCY(Firm1:Firm70!E46,0),2)

the values consists of numbers which are located in E46 of each relevant sheet.

FREQUENCY, which is given just a 0 to be used for sorting, takes each E46 and if the value <= 0, the counter for the 0 bin is augmented with 1, otherwise the counter for the rest bin (FREQUENCY has always a rest basket) is augmented with 1.

So a -0.6 bumps the counter for 0 1 up; a 0 too bumps it up. But a 5 on some sheet is greater that the 0 bin, so the counter for the basket is bumped up with 1.

We can something like:

{1;2}

which means we have 1 number that is <= 0 and 2 numbers > 0.

With INDEX we can at the basket counter:

INDEX(FREQUENCY(...,0),2)

The basket counter is exactly the valid number of observations that the average needs to know:

=SUM(...)/INDEX(FREQUENCY(...,0),2)

Note. We don't expect negative numbers in the present case.

Hope this helps.

 

[Aladin Akyurek]

Does correcting the criteria in the original formula work?

Original formula - AVERAGEIF(Firm1:Firm70!E46, "<>0")

The criteria should be "<>"&0

AVERAGEIF and kindred functions like SUMIF(S) do not admit 3d references.

 

[TimNevins]

Thanks, I seldom use 3D references, but I could tell the criteria was malformed.

 

[Aladin Akyurek]

Thanks, I seldom use 3D references, but I could tell the criteria was malformed.

But the criteria is not malformed at all:

"<>0"

and

"<>"&0

are equivalent.