I have some data that I would like to take the simple

average of "=Average(C2:C30)". The problem is there are

some #N/A's in the list. I am pretty sure there is a

formula that excludes #N/A's I just can't remember it.

I do realize that I can just replace the #N/A's with

zeros, but for other reasons I need the #N/A's to stay

there.

Any ideas?

Thank you!

Kevin,

=IF(ISNA(etc ...

or more generically

=IF(ISERROR(etc...

-------

Bob Phillips

... looking out across Poole Harbour to the Purbecks

=IF(ISNA(etc ...

or more generically

=IF(ISERROR(etc...

-------

Bob Phillips

... looking out across Poole Harbour to the Purbecks

=AVERAGE(IF(ISNUMBER(A1:A29),A1:A29))

This formula must be array entered - You must press ctrl+shift+enter (as

opposed to just hitting enter) after inserting the formula and subsequently

every time you edit the formula for it to work. If you do this correctly,

Excel will display the formula enclosed in curly braces {}.

--

HTH,

Laura Cook

Appleton, WI

This formula must be array entered - You must press ctrl+shift+enter (as

opposed to just hitting enter) after inserting the formula and subsequently

every time you edit the formula for it to work. If you do this correctly,

Excel will display the formula enclosed in curly braces {}.

--

HTH,

Laura Cook

Appleton, WI

Also...

=SUMIF(C2:C30,"#N/A")/COUNT(C2:C30)

or

=SUMIF(C2:C30,"#N/A")/MAX(1,COUNT(C2:C30))

=SUMIF(C2:C30,"#N/A")/COUNT(C2:C30)

or

=SUMIF(C2:C30,"#N/A")/MAX(1,COUNT(C2:C30))

2. Is is possible to count the Y/N's in a record?

4. deleting empty lines from a variable containing \n's

5. How to change the "N's" in proc report across time

6. Weird Problem -- Repeating C's and N's

7. index n's maximun values in an array

8. Multiline tooltip problem... shows the \n's

11. 2 fields have embedded \n's, how to get rid of these?

13. Mofify data values of n's variables

14. Sandwich existing formulas with "If iserror(formula),0,formula

15. Sandwich existing formulas with "If iserror(formula),0,formula)"

4 post • Page:**1** of **1**