OK, this should be simple but its Monday, I have a deadline and its bugging me.
Im trying the get an AVERAGE of a column that contains a number between 0 and 5 OR the text "NA" (not an error of #NA), Im only interested in the numeric values, so I've tried:
and
=AVERAGEIF(A:A,">=0",A:A)
but both return #DIV/0!=AVERAGEIF(A:A,"<>'NA'",A:A)
Last edited by pjwhitfield; 08-10-2015 at 03:24 AM.
If someone has helped you then please add to their Reputation
Not sure what you mean by getting the average of the text "NA"? Could you explain a bit further on that and/or upload a workbook example.
For getting values between 0 and 5, try:
=AVERAGEIFS(A:A,A:A,">=0",A:A,"<=5")
Apologies, I may have badly worded it. Im looking to do as you think, ie ignore the NA results and only get the average of those with 0,1,2,3,4 or 5 in.
I've tried your solution and still get #DIV/0!
Im checking my data column now to see if theres an entry out of the ordinary as both yours and my 1st ">=0" option should have returned an answer.
Both your formula work OK for me as does AVERAGE suggested in following post.
It would help if you posted a file.
Why not =AVERAGE(A:A)?
I think it automatically ignores texts.
EDIT: Ignore the above, I didn't fully read the query. My apologies.
Having said that... are you sure the numbers you are seeing are in fact numeric and not stored as text?
quekbc nailed it, the data was imported from Excel and therefore came over as text, Ive added a helper column in to convert and all is good. Thanks all.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks