Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using Goggle Spreadsheet to work up some historical stock data and I use a Google function (=googlefinance=…) to import the historical closing prices for a stock, then I work with that data further. But, in that list of data generated from the =googlefinance=… function, one of the amounts comes up as #N/A. I don’t know why, but it happens for various symbols that I have tried.

When I use a max function on the array, which includes the N/A line, the max function does not come up with anything but an N/A, so the N/A throws off any further functions. I thought I’d create a second column to the right of the imported data in which I can give it an IF function, something like, If ((A1 <0), "0", A1), with the expectation that it would return 0 if cell A1 is the N/A, and the cell value if it is not N/A. However, this still returns N/A. I also tried an IS BLANK function but that resulted in the same NA.

Does anyone have any suggestions for a workaround to eliminate the N/A from an array of numbers that I am trying to work with?

share|improve this question

migrated from superuser.com Nov 16 '12 at 8:33

This question came from our site for computer enthusiasts and power users.

Encountered this, then fiddling about I found that this works great:


This works even if there are invalid values inside the range

share|improve this answer
I fear this does not work in new Google Sheets (does in old) and does not work for MAX. – pnuts Jan 1 '15 at 6:50

You can use the isna(value) function to test if googlefinance returns N/A. You can use that in an if function to output whatever you want if N/A is returned.

For example:

=if(isna(googlefinance("DoesNotExist"; "price"),"NA returned",googlefinance("DoesNotExist"; "price")))

Or this example:


=googlefinance(A1; "price")
=if(isna(A2,"Not avail",A2))

Here's a screenshot of isna working in a Google Spreadsheet:

enter image description here

share|improve this answer

Lets say your values are in column A, and some contain N/A errors. Put this in column B:


If there is any error in column A it will use 0, otherwise the value in A - then you can do max etc on column B.

share|improve this answer
iserr didn't work with googlefinance for me, however isna did work. Did you try this yourself? – HeatfanJohn Nov 13 '12 at 19:10
iserr should work with any error, I wasn't able to test with googlefinance to make sure it matched - but isna is just as suitable. – Paul Nov 13 '12 at 23:53
The problem with iserr is that it does not consider the n/a as an error. See my answer that shows both. – HeatfanJohn Nov 14 '12 at 2:00
@HeatfanJohn Ah right. Thanks, fixed. – Paul Nov 14 '12 at 3:08

Please try:

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.