I have a large column of data that is randomly interspersed with a number of #N/A values. When I try to use descriptive statistics I get an error due to non-numeric data. Is there a way to get Excel to ignore the #N/A values in the column and just use the numeric values in the data column for basic statistics?
thanks, Roger
Wrap your formula in an error trapping function.
=if(iserror(your equation),Value if error exists, Your Equation again)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thanks Alan
I am still not quite following what to do. I want to take the average of a large column of data with lots of random #N/As that need to be ignored. Would it look like...
=if(iserror(average(datarange), average(datarange), average(datarange))?? I tried some versions of this and I got errors. I am sure your answer is correct but could I get a little more explanation of how it works with functions?
ideally, the descriptive statistics package under data analysis package could work; is there a way to get that routine to ignore #N/As?
thanks for your help.
Here is how I would do it. I hope this is clear. The formula in C2 is then copied down.
Data Range
This iserror returns a true of false. If it returns a true (an error), then the IF function says in my example show a zero, if it returns a false, then return the value of the vlookup.
Last edited by alansidman; 05-06-2014 at 11:10 AM.
Hi Roger,
There is a very nice function in Excel 2010 and up =AGGREGATE( and it is great when you deal with errors. For AVERAGE use =AGGREGATE(1,6,range)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thanks again, but I am sorry, I still don't see what this is doing and how it relates to my question (sorry for being such a novice)
If the data being analysis is in column A, there needs to be some #N/A values interspersed in the data set - if its all numerical values then there is no problem, its when there are #N/A values that I get errors. How do I get the average for example from a large column of data with some numeric values but lots of #N/A values? In your example, I still don't see how it relates, what are columns F and G for example? And how does this relate to having lots of #N/A values in a dataset?
I am sure you are showing me and I am just being too dumb to get it, so if you can break it down a little more, that would be great. thanks for your patience.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Ah-ha, that's it, works great, thanks.
You're welcome and thank you for the feedback!
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
I have one other question as an add-on to my previous question.
If my data column has both positive numbers, negative numbers and #N/A values. Is it possible to just do the aggregate command for mean on the positive numbers separate from the negative numbers? and of course, skip the #N/A values
thanks
I don't think AGGREGATE can do this but here is something that might work (by Aladin Akyurek)
=AVERAGE(IF(ISNUMBER(A2:A8),IF(A2:A8>0,A2:A8)))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Data
Last edited by AlKey; 05-07-2014 at 11:46 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks