Microsoft Excel help

[Tar Devil]

How can I average an array which contains "#N/A" values?

Later,

Phil

[ljb5]

I'm not sure of the best way, but I have a way that will work.

Make a column next to the one you want to average.

Use =if(iserror(A1),"",A1)

This means, "If that cell is an error, make this cell blank -- otherwise, make this cell the same as that."

Then you can take the average of the new column.

[Tar Devil]

Popeye, that works ok with sum formulas, but I need to average.

lijb5, there are several workarounds I use, such as converting all #N/A returns to values and converting to text, but I have 38,000 lines of data. I was hoping for a way to do this en mass.

I do appreciate the suggestions, guys! Thanks.

Phil

[Katherine]

Why not just do Popeye's sum equation and divide it by the total number of data points?

[John Bell]

I create a column that uses the following test. Let's assume the data you want to average is in column A:

=IF(ISNA(a1)," ",a1)

This simple if-then tests to see if the value in A1 is #NA. If yes, then it returns a cell with just a space character in it that does not count as far the =AVERAGE function is concerned. If no, it returns the value of A1 for further computation.

[Katherine]

I see where mine would screw up and count the N/A cells in the average. John's looks like a better solution.

[Donn]

An easier way:

Sort the datarange on that column, delete all the #N/A's, then highlight the entire column, and select average in the status bar.

[Donn]

Correction...after sorting, copy 0 to all the #N/A cells.

[Bruce Hooke]

Another option is to put the NA test in the equation that populates the cells you are trying to average...

=IF(ISNA(Your equation),"",Your equation)

Another approach would be to write a VB routine that loops through all the cells and adds up the cells that are not n/a and then divides by the number of such cells it finds, but that seems like a lot more work than is necessary. Since it is so easy in Excel to populate an entire column with an equation it really doesn't matter how many rows you are dealing with...

[Bruce Hooke]

Originally posted by Donn:
Correction...after sorting, copy 0 to all the #N/A cells.NO!!!! Don't replace NA cells with 0. That will affect your average. n.b. I take NA to mean null, which is NOT the same as zero. If NA does in fact mean 0 then of course you should replace the NA's with zeros.

[Bruce Hooke]

Originally posted by Katherine:
I see where mine would screw up and count the N/A cells in the average. John's looks like a better solution.Actually, if you wanted to go the SUMIF route you could use the COUNTIF function to get the number of cells you are averaging across.

[ljb5]

Originally posted by Bruce Hooke:
Actually, if you wanted to go the SUMIF route you could use the COUNTIF function to get the number of cells you are averaging across.This worked for me:

=SUMIF(A:A,">0")/COUNTIF(A:A,">0")

Note: this only works if your data is all greater than zero.

I couldn't get it to work with "<>#NA" or "ISNA=FALSE"...

Okay... this works if your error type is #NAME?

<code>=SUMIF(A:A,"<>#NAME?")/COUNTIF(A:A,"<>#NAME?")</code>

If your error type is all the same, you can substitute #DIV/0!, #NUM! or whatever your error is.

[ 01-06-2006, 05:06 PM: Message edited by: ljb5 ]

[Donn]

If the records with #N/A in that field are to be factored in the average, then they must be replaced with 0. If not, my first solution works.

[Bruce Hooke]

Well...sort of...N/A could mean 100 about as easily as it could mean 0...it all depends on the circumstances. Barring other information I think it is most likely to mean null or no information, but you are right that if you do nothing with the N/A cells most of the proposed solutions will simply ignore these cells...

[Donn]

You can do it all from the keyboard, without entering a formula or using the mouse. If it's a one time operation, it's far faster than keying in a formula or function, and inserting columns. If you need an automated function to do it again and again, I'd write a VB routine.

[Tar Devil]

I want the N/A's to remain null values... don't want those cells averaged.

In the end, I completed all my vlookups, converted the cells to values, changed the N/A's to "No" and got the results I needed. Doing it all in one fell swoop cut down on time, plus the spreadsheet is faster now that it isn't recalculating constantly.

Thanks a million for all the suggestions! There are a number here that I'll catalog for future use!

Later,

Phil