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.


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.

Editor's Picks