Bill: Hey, I’m Bill Jelen from Mr. Excel.com, I got a cool Excel tip for you today.
Mike: Hey, this is Mike Gel Girvin at Excel is Fun in Youtube, I have a different way to do that.
Bill: Hey, alright 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’s some zeros in the range, I wanna averaged the none zero cells. And the average function is going to throw those zeros in, of course, so, it will take the total of all these, divided by 20, to get that average, so. I have a couple of methods I’m gonna use and then we’ll throw it over to Mike and see what Mike has. First thing I’m gonna do is use control H, and say I wanna find all of the zeros and replace with nothing, the important thing, match entire cell contents and say replace all, click OK. Now, that gives rid of the nine zeros and our average now, when I recalc, is up from 1820 to 3276. A cooler way to go, I’m not sure if it’s faster or anything like that, is to use control F, for find, find all zeros, match entire cell content, find all, and here’s the cool thing, I press control 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 those zeros, this person at Youtube did not want to delete the zeros. 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 Mr. Excel. Hey, so we got the values here, well, I got an average without the zero there, I’m gonna do two methods, one, that will be a non-array formula, one that’s an array formula. Oh, probably one of the easier way is to just to add up and divided by the count than it’s greater than zero, so I will alt-equals, get that range there, and that’s added up, then we’ll just divide by count-if, 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 gonna start off by saying not zero, so, double quote, less than, greater than zero, and double quote, close parenthesis. The not zero would pick up anything that is not zero, so, it actually include negatives, hit enter. And there’s our value, we could also do this, if we did wanna exclude any negative, and really only do greater than zero, then we can just do greater than zero. So the sum, and count-if, great way to go. We could also do an array formula, average function and then right inside the average function we’ll do if, and we’ll just ask, is anything in that range right there, if that range is greater than zero, then what will 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 task, the fact that we’re gonna give it an array of logical task, we’ll convert it to an array formula and those trues and falses will relate to this ranges, only when there’s a true there will it pick up the value there. I can close parenthesis, I do not need the value of false, close parenthesis, and then this is an array formula, so I have to hold control-shift and enter. Alright, I’ll throw it back to Mr. Excel.
Bill: 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 on over on 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, one of the numbers greater than zero, and I get false when the value is zero and then the average, 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 parenthesis, and I type the FX here, which is a great way to get to help for 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, third bullet point here in the remarks in Excel help to your advantage, brilliant. Now, hey, one last solution here, and this is only if you’re an Excel 2007 or newer, equal average, if, brand new function, they added to Excel 2007, we’ll take a look at this range and then comma, and use marks greater than, less than zero, in quotes, will do the average if. Much faster way to go, if, you’re an Excel 2007. Alright, hey, well thank you for stopping by, on behalf of Mike and myself, see you next time, for another Dueling Excel podcast.
Transcription by:
Scribe4you Transcription Services