I am trying to determine the average, stdev, min, max of a column of data that contains #N/A (empty) cells. I'd just like to ignore all the #N/As in the column.
How best to do this?
Here's a sample
0:00:03
0:00:07
#N/A
#N/A
0:1:09
0:1:16
I tried =AVERAGE(IF(ISNUMBER(P2:P65), P2:P65)), but I think that is not calculating anything if a single #N/A is found.
Thanks!
The formula you posted will work if you confirm as an Array - that is to say you commit the formula using CTRL + SHIFT + ENTER rather than using Enter as would normally be the case.
Once the Array is set successfully then the formula will appear encased within { } [you can not type these in]
You could use:
Or if you're running XL2007 you can use AVERAGEIF
Please Login or Register to view this content.
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
wow. thanks much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks