Google-sheets – Summing up spreadsheet data when a column contains “#N/A”

google sheets

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?

Best Answer

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

Related Solutions

GOOGLEFINANCE – Return Only Historical Price in Google Sheets

No, it is not possible. You can however just reference the lower right cell in another cell.

so if you got back from Google:

A1: "Date"      B1: "Close"
A2: "3/3/2000"  B2: "55.22"
A3: "Date"      B3: "Close"
A4: "3/4/2000"  B4: "58.44"

Set cell C1 =B2 and cell C2 =B4

and the results will be:

C1: 55.22
C2: 58.44

you could also put the query in one google doc and then reference it from another google doc if you really wanted to separete the data.

Google-sheets – How to change cell value, looping through a provided list and record result in Google Sheets

You need an Apps Script for this (under Tools > Script Editor). Here is how it could work (explanation below).

function loop() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');   // name of your sheet
  var data = ss.getSheetByName('data').getRange('A1:A20').getValues();
  var output = [];
  for (var i = 0; i < data.length; i++) {
    sheet.getRange('B1').setValue(data[i][0]);
    SpreadsheetApp.flush();
    Utilities.sleep(10000);
    if (sheet.getRange('B18').getValue() == 'Yes') {
      output.push([data[i][0]]);
    }
  }
  sheet.getRange(2, 12, output.length, 1).setValues(output);
}

The lines until for are just setting up pointers to resources and grabbing data. In the loop, the value in B1 is set; the spreadsheet is flushed to make sure it really applies; and then the script waits 10 seconds (10000 milliseconds) for the financial data to arrive. You may decide to change this pause depending on your sheet's performance. If the value in B18 is Yes, the stock symbol is added to array "output". After the whole thing has ran, the output is placed in column L (column number 12).

To be able to run this function from the spreadsheet itself, add the function below to the script, which will create a menu item for it.

function onOpen() {
   var menu = [{name: "Run a loop", functionName: "loop"}];
   SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);  
}

(Another option is to use a clickable drawing).

Related Topic