Software

Office Challenge: How can you get Excel to return an average that doesn't consider 0 values?

Here's the answer to last week's challenge on exporting the text from a PowerPoint presentation, along with a new question to test your Excel skills.
Excel's AVG() function evaluates all the values, including 0s, in a specified range. For example, AVB() evaluates the values 3, 0, and 3 as follows: (3+0+3) / 3 6 / 3 2 Sometimes, you'll want to exclude 0 values from the average. In the case of our example expression, removing the 0 makes a big difference: (3+3) / 2 6 / 2 3 Unfortunately, there's no way to tell AVG() to ignore 0 values. How do you exclude 0 values from an averaging expression? Last week we asked… "How do you export text from a PowerPoint presentation?" If the text you want to export is in text placeholders, your export task is simple:

  1. From the File menu, choose Send To and select Microsoft Office Word. In PowerPoint 2007, click the Office button, select Publish, and then choose the appropriate option -- most likely, Create Handouts In Microsoft Office Word.
  2. In the resulting dialog box, click the Outline Only option and click OK.
  3. When Word opens with the presentation's text, reformat and save as a Word document.

Unfortunately, this easy method won't transfer text that's in a text box or an AutoShape object. PowerPoint treats the text in these objects as a graphic, not text. You can write code to do the job (which is rather complex). A less than perfect, but codeless method is to print the presentation to a PDF file and then save that file as text (Save As Text from the File menu). You'll have a bit of cleanup, but you'll have all of the text, so the solution meets the goal. My favorite PDF utility is CutePDF, but there are several alternatives, so shop around before making a decision.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

28 comments
esikorski
esikorski

I use this all the time in my grade spreadsheets. I want to know the exam average without any "0"s (say if someone missed the exam for example). I type in "AVERAGEIF(A2;A25,"0").

kburrows
kburrows

Try a simple formula like this where you sum all the cell values and then divide by the values greater than zero: SUM(A1:A10)/COUNTIF(A1:IA10,">0") You don't care if the numerator adds the zero since it adds nothing to the average, you just want to exclude counting zeros in the denominator to get an average for only cells greater than zero.

heyyoucraig
heyyoucraig

An Average is defined as "a quantity intermediate to a set of quantities" Therefore the Zeros are part of this Set and should be included... Cheers

mw00110011
mw00110011

I guess, by default, all current Office discussions invoke 2007. I saw no reference to version in the question. AVERAGEIF is not supported in earlier versions - but obviously isn't the only solution. The 'Array Formula' approach overcomes that "shortfall" in Office 2003. New functions in Excel '07, not supported in earlier versions: include: AVERAGEIF, AVERAGEIFS, CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT, CUBEVALUE, COUNTIFS, IFERROR, and SUMIFS. Wow, 18 character function names. It's good to see all the variations and alternatives, along with friendly discussion. Exploring these suggestions always leads to more understanding of Excel's capabilities and throws in some 'Best Practices' for good measure.

tgullo
tgullo

One of the simplest ways to include or exclude zeroes is to decide as suggested by previous comments what value a zero represents in that portion of the worksheet. Some like to use a blank others a "-" lastly a 0. To achieve any of these options all you have to do is format the cell(s) that will be used in these forms of calculations. Using Custom Format in the Format cells dialog, enter your preferred +ve;-ve;zero;text formats For the Challenge here you would enter #.#;-#.#;""; - this would replace a entered zero with a blank. or #.#;-#.#;"-"; - this would replace a entered zero with a dash. A normal =Average(Range) formula then will exclude the zero or blank entry tgullo@teccon.optusnet.au Melbourne, Australia

rellis1949
rellis1949

There were several good suggestions as to how zeros could be eliminated from the average operation. However, a more important concept should be to reinforce the spreadsheet design such that zeros and blanks are differentiated and the zero/blank designation should be mandated in the spreadsheet. Data quantification, especially in areas like sales dollars and sales units, mandates the difference between zero sales and no possible (blank) sales. As a resident of the MS Coast and an educator, I constantly reinforce the difference between the two values through examples of various service revenues. Problems, such as considering the mean (average) revenue for a service operation, take on considerably different results if the service revenues are considered zero when the dollars never had an opportunity to exist. Hurricane Katrina devastated the Coast. The three months following Katrina also prompted closure of the gaming institutions. State revenue tables suggest the revenues were zero instead of blanks. Thus, the mean earnings were inappropriately reflected. Unlike a store that is open but had no sales (zero dollars), gaming institutions could not have revenues for three months. Blanks in a spreadsheet would have resulted in a more meaningful mean statistic. More importantly, the reflection of no earnings would have been more representative than that of zero earnings. Respectively, building a spreadsheet with the appropriate use of zeros and blanks should be established so the zero exclusion from the average calculation would be a moot situation.

faisal_kaleem
faisal_kaleem

use Sumif ">0" and countif ">0", like sumif(a1:a3,">0",a1:a3)/countif(a1:a3,">0")

lou.bergner
lou.bergner

If we take the values proposed in the statement of the problem (3, 0, 3) and place them in cells C2, C3 and C4, you can get the average of the non-zero values by using this formula in cell C5: =SUM(C2:C4)/(COUNTIF(C2:C4,"0")) Lou Bergner

aaron.baker2
aaron.baker2

two methods: 1) instead of average use sumif/countif where the if statement says only value numbers greater than 0 2) instead of populating with 0, use "" and then the average function will work properly.

amasa
amasa

You could use the COUNTIF function to exclude zero values. If the information you want to average is in column A rows 1 through 5 you could use this formula in A6 to average them without counting zeros: =(SUM(A1:A5))/(COUNTIF(A1:A5,"0"))

lkeppel
lkeppel

Use the averageif function and exclude any cells equal to zero. Example: =AVERAGEIF(A1:D1,"0")

dave
dave

What I do is probably not as elegant as most but it always works. Underneath each value have an expression =isblank(a1) then under that =if(a1=true,1) then simply at the end of each row do a sum(a1:a10) , then simple division works. Then hide the rows where the calcs are and no-one will be any the wiser!

alan
alan

Use the =AVERAGEIF(Range,Criteria) Therefore if the cells were a1:a5 Use =AVERAGEIF(A1:A5,">0")

dogknees
dogknees

Assuming the data is in A1:A5, =SUM(A1:A5)/COUNTIF(A1:A5,"0") Excel 2007 has an averageif function that can be used, but the formula above will work in any version. Regards

massonjj
massonjj

Lets say the {3,0,3} data you describe is in A1:A3. For your average use the following: =AVERAGE(IF(A1:A30,A1:A3,"")) Then, and this is the important part, hit CTRL+SHIFT+ENTER so that excel recognizes it as an array function. It will appear in the function bar with {} around it and should give the right result.

CharlieSpencer
CharlieSpencer

Mathematically, you're correct. However, if the boss wants the answer to the original question, debating what to call the result and giving him what you define as the answer isn't going to look good on your next review (assuming you're still employed).

dogknees
dogknees

I agree with what you're saying about the design issues. However, one needs to be careful. In your example of gaming income, if I was the proprietor and wanted to know my average income per month for the year, the zeroes need to be included. Otherwise, it gives a false impression. If you see an average of $1,000 per month for the year, you know with certainty that you earned $12,000 that year. Any other interpretation of the average is false. The results have to be consistent to be useful in making judgements.

rupinder
rupinder

Use the following. It takes into account zeros, blanks and negative number. =SUM(A1:A4)/(COUNT(A1:A4)-COUNTIF(A1:A4,"0"))

dogknees
dogknees

Your formula would exclude negative numbers as well as zeros.

Gordon Or-8
Gordon Or-8

You have to go with the array formula option suggested. Using the Countif() option will not take account of blank cells in the range being averaged whereas the array treats the blanks as zeroes (Also treats text as zeroes)

Lori.shore
Lori.shore

I have a large amount of data with many columns that have cells with 0 values. I have no issue writing this for the data. However, I need to be able to do this type of average in a pivot table. I know you can't do it in Excel 2007, can you in Excel 2010.

williams
williams

Use find & replace to change the zeros to blanks

rellis1949
rellis1949

You are absolutely right. The problem definition is the basis for considering the use of a zero or a blank in the representation of data values. If the organization, in particular strategic management, is trying to reflect the true position of the aggregate income (and the statistics associated with the aggregate) then the use of a zero value would probably be considered. Whereas, if the organization (more specifically operations managment) was attempting to reflect the potential of the revenue generation process then a blank might be more appropriate for representing those occasions where revenues could not be generated. As a side note, in the case of Hurricane Katrina and the gaming revenues, use of an average would be misleading for any report generation. The transition from patterned and trend revenues to no revenues and, respectively, a regeneration of revenues (basically a return to a new PLC structure) makes the presenation of most central tendency statistics virtually useless. Thank you for your observation and response.

dogknees
dogknees

The point was that you didn't answer the question as it was asked. In our business it pays to be precise.

alan
alan

Just change the criteria....

dogknees
dogknees

The question explicitly says to ignore zeroes, not blanks. A solution that excludes blank cells would be incorrect in this case.

chee.seng.kee
chee.seng.kee

countif() ignores any content that does not meet the criteria. In gbentley's e.g.(criteria = "0"), it will ignore blanks, text and zeroes.

Editor's Picks