Formula To Ignore Zeros In Averages?

Brian Andrews

I have columns running left to right with the 12 months listed as the main header of each. I want to have a column on the far right that shows our average month's sales based on each months totals we go. RIght now it is May, but the simple AVG formula also counts June-December sicne they have zeros in them. So, instead of it dividing the total by 5, it is dividing by 12, thus giving a false average. I already understand that I can simply delete the zeros. However, those zeros are there based on the formula with that cell which will total that column's sales totals based on the cells below (Children).

 

In short, I just need a way to tell the formula to ignore anything showing a zero and then divided by the cells containg actual sales numbers. 

 

So, what would you add to this is formula below to accomplish this?

 

=AVG(Jan8:Dec8)

 

Thanks!

Travis

Hi Brian! This is possible by creating a formula that will sum the range of numbers and divide that number by a count of the cells subtracted by a count of the cells that contain 0. 

 

Here is what the formula would look like if your column names and range are January - December (and in row 3):

 

=(SUM(January3:December3)) / ((COUNT(January3:December3)) - (COUNTIF(January3:December3, 0))) 

 

This formula will divide the total sum by the number of cells that do not contain a 0. 

 

 

Brian Andrews

Yes, it worked! Thanks so much Travis.

Brian Andrews

By the way, some lines will have zeros in almost all months until later in the year. It looks like this particular formula requires at least one cell to contain a number greater than zero. Otherwise, it shows #DIVIDE BY ZERO. Any addtions I could add to the formula to force it to show a zero in that cell in place of that message?

Richard Rymill SBP

To avoid seeing the error codes because there is no data to crunch, eg Divide by Zero you can insert and IF statement. Detailed explanations in the "formula errors section of the help pages with workarounds for most of these.  

Travis

Brian try this formula which will first check if the count is the same as the count if 0. If these are the same, the formula will produce a "0". If they are not the same, it will move on the formula I provided above. 

 

=IF((COUNT(January3:December3)) = (COUNTIF(January3:December3, 0)), "0", SUM(January3:December3) / ((COUNT(January3:December3)) - (COUNTIF(January3:December3, 0))))

Brian Andrews

Thanks again!

 

By the way, how would I make it display the zero like this: $0.00 rather than this: 0.

 

When I try to add "Currency Format" using the control panel to the left, it won't change. I figured something in this formula must be overrriding it.

Brian Andrews

Travis, any ideas on my question above?

Travis

Brian, thanks for following up! The 0 is formatted as text (and cannot be currency) because I added quotes around the 0 (not sure why I did that! ). If you remove the quotes, it will be formatted as a number and then will be displayed as $0.00. Here is the new formula with the change:

 

 

=IF((COUNT(January3:December3)) = (COUNTIF(January3:December3, 0)), 0, SUM(January3:December3) / ((COUNT(January3:December3)) - (COUNTIF(January3:December3, 0))))

Brian Andrews

Of course, in the end it's always something simple and easy in these instances. All fixed now. Many thanks!