Average / Standard Deviation on range containing #N/A errors

[Thebatfink]

Hi all,

I'm currently trying to perform some statistical functions on a range of data but with little success.

I want to "AVERAGE" the range and also do a "STDEV" on the range G8:G107 but some of the cells contain #N/A formula errors. I must have the formula returning #N/A to the cells else my chart on the same range plots the empty cell as 0.

What formula can I use that will let me do the standard average and stdev functions on that range, but ignoring the #N/A errors as if they were simply empty cells??

Is this possible? Thanks!

 

[slnovak]

Try:

=AVERAGE(IF(NOT(ISNA(Q45:Q51)),Q45:Q51,""))

Where Q45:Q51 is the range you want to sum.

Important! : Make sure that you press ctrl-shift-enter when entering the formula, otherwise you will get an NA error.[/b]

Note: It should work with STDEV as well.

 

[Thebatfink]

Exellent! Thank you so much. That appears to work great!

Can I assume this will work for pretty much any of the maths functions like sum, count, stdev etc etc?

 

[Aladin Akyurek]

Exellent! Thank you so much. That appears to work great!

Can I assume this will work for pretty much any of the maths functions like sum, count, stdev etc etc?

Yes, but...

Control+shift+enter:

=AVERAGE(IF(ISNUMBER(G8:G107),G8:G107))

=STDEV(IF(ISNUMBER(G8:G107),G8:G107))

 

[Thebatfink]

Thanks, appreciate it!

By the way, what exactly does control shift enter do that enter doesnt do on its own??

Cheers

 

[Aladin Akyurek]

Thanks, appreciate it!

By the way, what exactly does control shift enter do that enter doesnt do on its own??

Cheers

It signals Excel that the formula in question needs process array objects. See Excel's Help on "array formulas".

 

[hmmm...]

I ran into a problem taking the STDEV with an expression like the one above:

=AVERAGE(IF(NOT(ISNA(Q45:Q51)),Q45:Q51,""))

If there are any blanks in your range they will be evaluated as zeros and you'll get the wrong result, whereas Aladin's

=AVERAGE(IF(ISNUMBER(G8:G107),G8:G107))

behaves like AVERAGE without the IF--the blanks are ignored and you get the correct result.

This type of "error" could be difficult to spot, so I am an ISNUMBER fan now.

 

[jkeohane]

The only way I have found to do this, in a way that anyone else would understand, is to make sure there are blanks instead of NA().
The easiest way to do this is cell by cell, with an IF function, like:

=IF(ISNUMBER(G8),G8,"")

And then fill it down.

Then average and take the standard deviation of that new column.

But that is kind of a pain.