Summing up spreadsheet data when a column contains “#N/A”

Posted by Doris on Super User See other posts from Super User or by Doris
Published on 2012-11-13T01:41:42Z Indexed on 2012/11/13 5:04 UTC
Read the original article Hit count: 199

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?

© Super User or respective owner

Related posts about data-validation

Related posts about google-spreadsheets