10

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?

  • The title is about "summing up", but then you say "When I use a max function on the array …". Only one of them can be right :) – tanius Oct 16 '18 at 17:13
11

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

=sumif(A1:A20,"<>0")

This works even if there are invalid values inside the range

  • 10
    Should be =sumif(A1:A20,"<>#N/A") these days, see my answer for details. – tanius Oct 16 '18 at 17:30
5

To sum up values which might contain #N/A, use this formula:

=sumif(A1:A20,"<>#N/A")

Details: It seems that in earlier versions of Google Sheets, =sumif(A1:A20,"<>0") could be used instead as in the answer by Lockzmith. Now however, Google Sheets considers #N/A<>0, so it won't work anymore, returning #N/A if any part of the sum is #N/A. Anyway, here is the new way that works nowadays.

3

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:

ColA

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

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

enter image description here

2

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

=if(isna(A1),0,A1)

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.

  • 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
0

Please try:

=ArrayFormula(max(if(not(iserror(A:A)),A:A)))
0

Ran into this issue yesterday. Eventually realized that my data range that I was referencing in the original spreadsheet contained some error codes, not just numbers.

If any of the cells in the spreadsheet range you're referencing (e.g. the A1:A100 part in IMPORTRANGE("SpreadsheetURL","SheetName!A1:A100") ) contain errors such as

#NULL!
#DIV/0!
#VALUE!
#REF!
#NAME?
#NUM!
#N/A

then your formula must be modified to include SUMIF() as follows

=SUMIF(IMPORTRANGE("SpreadsheetURL","SheetName!A1:A100"), ">0")

This ensures that it's only adding actual numbers and not error codes (which will just throw another error).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy