Let’s take a look. In this worksheet we have a list of 16 properties, each with a price and other information. Let’s calculate an average price. First, I’ll create a named range for the prices. This makes the formulas easier to read and copy. Excel has a function called AVERAGE, but let’s do the calculation manually to start off with. We know we have 16 properties, so I’ll enter “16” for the count. I’ll use the SUM function to calculate a total. Now that we have both a count and the total, I can just divide the total by the count to calculate an average. You can see that the average is about $344,000. Now let’s calculate an average price using the AVERAGE function. AVERAGE takes one or more arguments, which can be numbers or ranges of numbers. In this case, I can just use the named range “prices.” You can see that the AVERAGE function gives the same number: $344,681. What if I delete one of the prices? In that case, we get a new average, but notice that the manually calculated average is now different from the average we calculated with the AVERAGE function. Why is that? Well, this is because the AVERAGE function automatically excludes blank cells, and non-numeric values, but our manual formula does not. It’s still dividing the total by “16”. We can fix this pretty easily by replacing the hard-coded value of 16, in cell M7, with the COUNT function which counts only numeric values. When I give COUNT our list of prices, it returns “15”. Now our manually calculated average and the AVERAGE function return the same number. This holds true if I enter text in place of one of the prices. Both formulas now exclude non-numeric values from the count, so they both return the same average. What if a price is zero? In that case, each formula calculates an average that includes the zero price, since zero is numeric. If you want to exclude zero values from an average, you can use the AVERAGEIF function, which we’ll look at in another video.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
