Look Who it Was Random video

Excel - How to Average a Range of Numbers Excluding Zeros (Dueling Excel) - Episode 1030

717 views · Published 5 June 2009 · 5:37 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  How to Average a Range of Numbers Excluding Zeros (Dueling Excel).
Welcome to another dueling Excel podcast! In this episode, we tackle a question from a viewer on YouTube who wants to know how to average a range of numbers while excluding any zeros. As always, Mike and I have different methods for solving this problem, so let's dive in and see what we come up with.

Mike's first solution involves using the Find and Replace function to get rid of all the zeros in the range. By selecting the entire range and replacing the zeros with nothing, the average function will automatically exclude them and give us the desired result. However, this method only works if you are allowed to delete the zeros from the range.

For those who cannot delete the zeros, Mike has another solution using the SUM and COUNTIF functions. By adding up the range and dividing it by the count of cells that are greater than zero, we can get the average without including any zeros. This method is quick and easy, but it does include any negative numbers in the range.

If you want to exclude negative numbers as well, Mike has an array formula solution using the IF function. By checking if each cell in the range is greater than zero, the formula will only include those cells in the average calculation. However, this is an array formula and needs to be entered with Ctrl+Shift+Enter.

But wait, there's more! I couldn't believe that Mike's array formula didn't need to include the value if false argument, so I did some investigating. As it turns out, Excel has a special rule for logical values in array formulas, which Mike cleverly used to his advantage. This just goes to show that there's always something new to learn in Excel.

And for those of you using Excel 2007 or newer, there's an even faster solution using the AVERAGEIF function. This function allows you to specify a criteria and only average the cells that meet that criteria. So, if you're using a newer version of Excel, this is definitely the way to go.

Thanks for tuning in to another dueling Excel podcast! Mike and I hope you found these solutions helpful and we'll see you next time for more Excel tips and tricks. Don't forget to subscribe to our channel and leave a comment with any questions or suggestions for future episodes. Happy Excel-ing!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Question from YouTube is how to average all the non-zero values in a range. In Episode 1030, Bill and Mike show several methods for solving the problem.

Table of Contents:
(00:00) Introduction by Bill Jelen
(00:21) Dueling Excel podcast question
(00:31) Problem: Averaging non-zero sales
(00:41) Solution 1: Using Find and Replace
(01:00) Solution 2: Using Find and Delete
(01:32) Solution 3: Using a formula to exclude zeros
(01:45) Introduction by Mike
(01:55) Solution 4: Using SUM and COUNTIF
(02:55) Solution 5: Using an array formula
(03:45) Explanation of array formula by Bill
(04:51) Additional solution using AVERAGEIF (Excel 2007 or newer)
(05:19) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Array formula in Excel
Average function in Excel
AVERAGEIF function in Excel
Ctrl + F in Excel
Ctrl + H in Excel
Deleting numbers in Excel
Excel 2007 features
Excel tips
IF function in Excel
Logical values in Excel
Removing zeros in Excel
Using SUM and COUNTIF in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152446/

More from this channel