Many of us use Excel a lot, but we work with only one or two workbooks. After launching Excel, we open the same workbook we use everyday and go to work. Can you omit the repetitious open task and force Excel to open a specific workbook when it launches?
Last week we asked…
“How can you get Excel to return an average that doesn’t consider 0 values?” The responses were all sound and valuable. You guys really know Excel! Most of you shared the most common solution, a combination of the SUM() and COUNTIF() functions, in the following form:
=SUM(range)/COUNTIF(range,”<>0″)
Joshua.Masson@… Offered an interesting array solution:
=AVERAGE(IF(range<>0,range,”"))
Gordon.cooper@… pointed out that the array accounts for blank cells, whereas the more traditional SUM() expression doesn’t. He’s right, but I agree with gbentley@… in this particular case. Neither expression is superior, but it is important that you know how both expressions respond to blanks and text (any value that doesn’t specifically match the COUNTIF() function’s criteria).
A few of you mentioned Excel 2007’s new AVERAGEIF() function. Interestingly, if you exclude 0 values, this function also ignores blank cells — curious.
Thanks for playing! It’s interesting to see so many different solutions to the same question.
