[theseus75]

I have a spreadsheet with an average line that can sometimes have no values. Thus, the average line reads #DIV/0. When charted, however, this shows up the same as a zero as opposed to there being no value.

Thus, I'd like to add something to the average formula to tell it: If #DIV/0, then blank. I would normally handle this with a macro replacing all #DIV/0s with blanks, but - as you may know - that doesn't work with a formula; it would have to be the literal value of the cell.

I'm using Excel 2003 on Windows XP w/ SP3, but the way.

Here's an example:

Part 1 Part 2 Part 3
100%
100%
100%
100%
100% 0%
100%
100% 50% #DIV/0! Average Line

And of course the formula now just reads: =AVERAGE(A1:A7), etc.

Thanks!

[slurpee55]

Well, the following:
=IF(ISERROR(AVERAGE(A1:A7)),"",AVERAGE(A1:A7))
will show a blank rather than an error (or you could put text in instead, such as
=IF(ISERROR(AVERAGE(A1:A7)),"You can't divide by zero",AVERAGE(A1:A7))
but I am not sure that is what you want.

[Aj_old]

Or you could use this formula:

=IF(count(A1:A7)>0),AVERAGE(A1:A7),"")

[theseus75]

Thanks slurpee:

Well, I put that in, and it indeed leaves the cell empty but for the formula. Alas, when charted, it still sees it (and displays it) the same as a zero. This is problematic b/c if there was an actual 0 for the measure, that's far different than having no cases for the measure (where the #DIV/0 would come in).

I mean, I could add to the macro - factoring the average, copying the results, pasting as values only, and THEN doing the find/replace of #DIV/0 for a blank cell, but I'd be surprised if there weren't another way.

Thanks.

[theseus75]

Aj_Old:

I'm getting an error on that, albeit it could be operator error.

[Aj_old]

You are right, it should be:

=IF(count(A1:A7)>0,AVERAGE(A1:A7),"")

[slurpee55]

I made a chart as well, and got no listing for the division error - except as part of the name for the series.
Maybe you could upload a sample file (fake data, please) so we can see what is happening?

[theseus75]

Okay, here's a sample where the first few columns are just the average formula, the fifth is blank (nothing in the cell), and 6th and 7th have Slurpee's and Aj_Old's formulas respectively.

So when there's nothing to average, I'd want it to look like column 5: nothing there at all.

Thanks for the help; let me know if you need more details.

[Zack Barresse]

You could force an N/A error, and then use the skip N/A's tool by Tushar Mehta...

http://www.tushar-mehta.com/excel/so...ity/index.html

This works in 2003, but does NOT work in 2007 (yet).

What I mean by force an N/A is to use the formula in your IF() condition...

NA()

HTH

[theseus75]

Zack: I installed the Add-in, and now #N/As indeed show up as nothing at all on charts. However, I don't know how to put this into a formula; don't mean to be an Excel newb, but could you flesh out the code a little more?

Cause using something like:

=IF(COUNT(U73:U84)>0,AVERAGE(U73:U84),"#N/A")

Puts in #N/A literally, but Excel still charts it as a 0. Whereas a #N/A caused by, say, a VLOOOKUP doesn't chart at all (which is what I want).

Thanks.

[Zack Barresse]

You could use the NA() formula is what I meant, sorry for not clarifying...

=IF(COUNT(U73:U84)>0,AVERAGE(U73:U84),NA())

[slurpee55]

This would also work - in row 12, enter this formula and drag it across, and base your graph on these values, not on row 10:
=IF(B10="",#N/A, B10)
Any column with a "0" actually in it will still show as a zero, but since text is not a value, the #N/A will not register.
You could hide row 12 if you preferred.

[Zack Barresse]

Forcing an error with an error. ROFLMAO!!! Too creative.

[slurpee55]

What the heck, it works! - Oh, note, that is based on using the first formula I gave, or AJ's in row 10.
And you knew I think in, um, strange ways....

[Zack Barresse]

Well, I think it's the most ingenious/hilarious/by-product solution I've ever seen in a formula.