Getting the average of a column, eliminating zero values.

shmizzy

New Member
Joined
Oct 17, 2006
Messages
6
I want the average of column B. But there are many 0 values. I want just the average of the non zero vals. Sorting is not an option. Any ideas?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
shmizzy

Welcome to the Mr Excel board!

Try something like this:
=AVERAGE(IF(B1:B100<>0,B1:B100))
which must be confirmed with Ctrl+Shift+Enter not just Enter.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=AVERAGE(IF($B$2:$B$400 > 0, $B$2:$B$400))

which needs to be confirmed with control+shift+enter, not just with enter.

This type of formula do not allow whole column references.

Otherwise:

=SUMIF(B:B,">0")/COUNTIF(B:B,">0")
 

Forum statistics

Threads
1,136,652
Messages
5,677,007
Members
419,668
Latest member
DharmaK

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
Top