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
data-validation
|google-spreadsheets
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