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.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, I'm Bill Jelen, from "MrExcel.com".
I've got a cool Excel tip for you, today.
Mike: Hey! this is my choker, an excellent spot on YouTube and I have a different way to do that.
Hey, all right! It's time for another dueling Excel podcast.
Today's question came in from YouTube someone said hey!
I have a range of numbers, there are some zeros in the range.
I want to average the non zero sales and the average function is going to throw those zeros, in of course.
So it's going to take the total of all this, divided by 20 to get that average.
So, I have a couple of methods.
I'm going to use them.
We'll throw it over to Mike and see what Mike has first thing.
I'm going to do is use, [ ctrl + H ] and say I want to find all of the zeros and replace with nothing.
Now, the important thing, match entire cell contents and say, replace all.
Click [ ok ] and now that gets rid of the nine, zeros and our average, now.
When I recalculate is up from 1822 to 3276.
A Cooler way to go.
I'm not sure if it's faster, anything like that is to use [ ctrl + F ], for find.
Find all zeros, match entire cell contents, [ find all ] and then here's the cool thing.
I press [ ctrl + A ] to select all of those cells.
We can close and now press the [ delete ] key, and that will delete all of those numbers.
And we now get the right average. Well, that's great!
If you're allowed to delete the zeros.
This person at YouTube did not want to delete the zero So, let's throw this over to Mike and see if Mike can come up, with a formula to figure out the average excluding the zeros.
Mike: Thanks! MrExcel.
Hey! So, we got the values here, and we want to get an average without a zero there.
I'm going to do two methods.
One, that'll be a non array formula.
One, that's an array formula.
Probably one of the easier ways is, just to add up and divide by the count, when it's greater than zero.
So, I will [ alt + = ], get that range there and that's adding it up, and then we'll just divide by COUNTIF and the range will be that same range A1 to A20, comma and the Criteria.
We can do it one of two ways.
I'm going to start off by saying not zero.
So, double quotes ("), less than, greater than Zero and double quote, close parenthesis.
Then not 0 would pick up anything, that's not 0.
So, it would actually include negatives.
Hit [ enter ] and there's our value.
We could also do this, if we did want to exclude any negatives and really only do greater than zero within we could just do greater than zero.
So, the SUM and COUNTIF, great way to go.
We could also do an array formula, AVERAGE function and then right inside the average function will do IF and we'll just ask is anything in that range right there.
If that range is greater than zero, then what do we want, that range whatever's in that range.
What that will do is give us, some trues and falses there.
This is the IF function and this argument is expecting a single logical test.
The fact that we're going to give it an array of logical tests.
We'll convert it to an array formula and those trues and falses, will relate to these ranges only when there's a true there.
Will it pick up the value there.
I can close parentheses.
I do not need the value of false, close parentheses and then this is an array formula.
So, I have to hold [ ctrl + shift + enter ].
All right! I'll throw back to MrExcel.
MrExcel: Mike, now that is really cool.
But, I couldn't believe this you said in this array formula, we don't have to put the value, if false.
What's going on there.
I can't understand, how that can work?
So, I came over here to the right hand side and I built 20 individual formulas, that do what your one array formula did and I see that I get numbers.
When the number is greater than 0 and I get false.
When the value is 0 and then the average.
The average is working but I can't figure out, how?
Because that false should be treated like a zero.
So, we go into equal average, open parentheses and I type the 'fx' here.
Which is a great way to get to help for a specific function.
And I start to read through and all the way down here, in remarks sure enough.
Logical values that you type directly into the list of arguments are counted.
But, if a range or cell reference argument contains text logical values or empty cells, those values are ignored.
Brilliant!
Basically, converting those zeros to falses and using this second bullet point, third bullet point here, in the remarks Excel help to your advantage.
Brilliant!
Now, hey! One last solution here and this is only if you are in Excel 2007 or newer.
Equal average (=AVERAGE) IF, brand new function, they added to Excel 2007.
We'll take a look at this range and then comma and use my greater than, less than, zero in quotes.
We'll into the AVERAGEIF much faster way to go, if you're in Excel 2007.
All right, hey! I want to thank you for stopping by, on behalf of Mike, and myself.
See you next time for another dueling Excel podcast.
I've got a cool Excel tip for you, today.
Mike: Hey! this is my choker, an excellent spot on YouTube and I have a different way to do that.
Hey, all right! It's time for another dueling Excel podcast.
Today's question came in from YouTube someone said hey!
I have a range of numbers, there are some zeros in the range.
I want to average the non zero sales and the average function is going to throw those zeros, in of course.
So it's going to take the total of all this, divided by 20 to get that average.
So, I have a couple of methods.
I'm going to use them.
We'll throw it over to Mike and see what Mike has first thing.
I'm going to do is use, [ ctrl + H ] and say I want to find all of the zeros and replace with nothing.
Now, the important thing, match entire cell contents and say, replace all.
Click [ ok ] and now that gets rid of the nine, zeros and our average, now.
When I recalculate is up from 1822 to 3276.
A Cooler way to go.
I'm not sure if it's faster, anything like that is to use [ ctrl + F ], for find.
Find all zeros, match entire cell contents, [ find all ] and then here's the cool thing.
I press [ ctrl + A ] to select all of those cells.
We can close and now press the [ delete ] key, and that will delete all of those numbers.
And we now get the right average. Well, that's great!
If you're allowed to delete the zeros.
This person at YouTube did not want to delete the zero So, let's throw this over to Mike and see if Mike can come up, with a formula to figure out the average excluding the zeros.
Mike: Thanks! MrExcel.
Hey! So, we got the values here, and we want to get an average without a zero there.
I'm going to do two methods.
One, that'll be a non array formula.
One, that's an array formula.
Probably one of the easier ways is, just to add up and divide by the count, when it's greater than zero.
So, I will [ alt + = ], get that range there and that's adding it up, and then we'll just divide by COUNTIF and the range will be that same range A1 to A20, comma and the Criteria.
We can do it one of two ways.
I'm going to start off by saying not zero.
So, double quotes ("), less than, greater than Zero and double quote, close parenthesis.
Then not 0 would pick up anything, that's not 0.
So, it would actually include negatives.
Hit [ enter ] and there's our value.
We could also do this, if we did want to exclude any negatives and really only do greater than zero within we could just do greater than zero.
So, the SUM and COUNTIF, great way to go.
We could also do an array formula, AVERAGE function and then right inside the average function will do IF and we'll just ask is anything in that range right there.
If that range is greater than zero, then what do we want, that range whatever's in that range.
What that will do is give us, some trues and falses there.
This is the IF function and this argument is expecting a single logical test.
The fact that we're going to give it an array of logical tests.
We'll convert it to an array formula and those trues and falses, will relate to these ranges only when there's a true there.
Will it pick up the value there.
I can close parentheses.
I do not need the value of false, close parentheses and then this is an array formula.
So, I have to hold [ ctrl + shift + enter ].
All right! I'll throw back to MrExcel.
MrExcel: Mike, now that is really cool.
But, I couldn't believe this you said in this array formula, we don't have to put the value, if false.
What's going on there.
I can't understand, how that can work?
So, I came over here to the right hand side and I built 20 individual formulas, that do what your one array formula did and I see that I get numbers.
When the number is greater than 0 and I get false.
When the value is 0 and then the average.
The average is working but I can't figure out, how?
Because that false should be treated like a zero.
So, we go into equal average, open parentheses and I type the 'fx' here.
Which is a great way to get to help for a specific function.
And I start to read through and all the way down here, in remarks sure enough.
Logical values that you type directly into the list of arguments are counted.
But, if a range or cell reference argument contains text logical values or empty cells, those values are ignored.
Brilliant!
Basically, converting those zeros to falses and using this second bullet point, third bullet point here, in the remarks Excel help to your advantage.
Brilliant!
Now, hey! One last solution here and this is only if you are in Excel 2007 or newer.
Equal average (=AVERAGE) IF, brand new function, they added to Excel 2007.
We'll take a look at this range and then comma and use my greater than, less than, zero in quotes.
We'll into the AVERAGEIF much faster way to go, if you're in Excel 2007.
All right, hey! I want to thank you for stopping by, on behalf of Mike, and myself.
See you next time for another dueling Excel podcast.
