average exclude zero

[billandrew]

Trying to average range excluding zero values and max value and min value using the following formula. seems it works then it doesn't.

Formula = =AVERAGEIFS(K3:K39,K3:K39,"<>0",K3:K39,"<>"&MAX(K3:K39),K3:K39,"<>"&MIN(K3:K39)) Control Shift Enter

Data - Return = 0.838098


If I manually remove all zero values and max value and min value the average should be 0.858264 not the above 0.838098

0
0
0
0
0
0
0
0
0
0
0
0
0.953407
1.006452
1.124045
1.199524
1.516364
1.211111
0.741905
1.022222
0.692941
0.847368
0.91122
0.898734
0.656842
0.621053
0.495263
0.526977
0.609583
0.637368
0.929744

<colgroup><col width="80" style="width:60pt"> </colgroup><tbody>
</tbody>

 

[jproffer]

If you leave the zeros in, the MIN is zero instead of 0.495263.

 

[mopp1]

Replace MIN(K3:K39) in your formula with 1/AGGREGATE(14,6,1/K3:K39,1) to get the minimum non-zero number from a non-negative data set (a set that only contains positive numbers or 0). If there could be negative numbers in your data this approach will not work. Does not require CSE.

 

[billandrew]

I am using the same formula on multiple ranges which may contain negative numbers

 

[mopp1]

I am using the same formula on multiple ranges which may contain negative numbers

try AGGREGATE(15,6,1/(1/K3:K39),1)

 

[billandrew]

This does look like it is averaging to my need. Below is the range. If I do not include all the zero values and do not include the lowest value (- 1.2625) and do not include the highest value (2.225833) the average should be 1.220556.

When I use the following formula the result is not the above. Is there something I am not doing correctly.

=AVERAGEIFS(AB14:AB40,AB14:AB40,"<>0",AB14:AB40,"<>"&MAX(AB14:AB40),AB14:AB40,"<>"&1/AGGREGATE(14,6,1/(AB14:AB40),1))

Is there something I am not doing correctly.

Appreciate your time

0
0
0
0
0
0
0
1.485833
1.616667
2.064167
1.994167
1.311667
1.486667
0.705833
1.519167
1.129167
-1.01
1.220833
1.151667
-1.01
1.220833
1.151667
1.180833
1.45251.220833

<tbody>
</tbody>

 

[mopp1]

use AGGREGATE(15,6,1/(1/K3:K39),1) in place of 1/AGGREGATE(14,6,1/(AB14:AB40),1))

i.e., =AVERAGEIFS(AB14:AB40,AB14:AB40,"<>0",AB14:AB40,"<>"&MAX(AB14:AB40),AB14:AB40,"<>"&AGGREGATE(15,6,1/(1/K3:K39),1)

 

[billandrew]

Awesome

Could you explain, how is the aggregate actually removing the smallest value?

Thank You

 

[mopp1]

Aggregate function 15 is the small function. Option 6 is ignore errors. Dividing your range into 1 creates #DIV/0! errors for the zero values. Dividing that back again into 1 restores the original values but leaves the div0 errors which small will ignore (due to option 6 selected). The final parameter (1) says return the 1st smallest value.

 

[billandrew]

Another AWESOME. Thanks