average exclude zero

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
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>


 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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>
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top