How do I write a formula in Google Spreadsheets to to compare two cells and write answer into another cell

March 24   google spreadsheets 0 1 Views
Advertisement

How do I write a formula in Google Spreadsheets to to compare two cells and write answer into another cell



How do I write a formula in Google Spreadsheets to to compare two cells and write answer into another cell

I am running into some obstacles while trying to design my ideal Google Spreadsheet. I am trying to do the following listed below and I am having a hard time. I was wondering if anyone can help me out.

In Sheet15 from A2 to A999 I will have a list of values. I want it to copy value from Sheet15 cell A2 to sheet Raw Data cell B1, wait for 40 seconds, check in Raw Data if B7 > 10% and B13 > 10%, if both conditions are met, in Sheet15 cell H2 write True, else False.

Second run would be copy value from Sheet15 cell A3 to Raw Data cell B1, wait for 40 seconds, check to see if Raw Data B7 > 10% and B13 > 10%, if both conditions are met, in Sheet15 H3 write True, else False.

Keep continuing this until A999 or so.

Advertisement

Replay

I believe this is what you want. If the comments in the code isn't enough, feel free to ask me for assistance.

function compareCells(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();                   //Get active Spreadsheet
  var listSheet = ss.getSheetByName('Sheet15');                     //Get sheet "Sheet15"
  var dataSheet = ss.getSheetByName('Raw Data');                    //Get sheet "Raw Data"
  var listSheetLastRow = listSheet.getLastRow();                    //Get number of last row with data in "Sheet15"

  var currRow = 2;                                                  //Start looping on row to, since row 1 is header

  for (i = 2; i <= listSheetLastRow; i++){                          //For-loop, looping through all rows with data in "Sheet15"
    var ticker = listSheet.getRange(currRow, 1).getValue();         //Getting value in column A in current row from "Sheet 15"
    dataSheet.getRange(1, 2).setValue(ticker);                      //Setting above value in cell A1 in "Raw Data"
    Utilities.sleep(40000);                                         //Pausing for 40 seconds (40 000 milliseconds)

    var value1 = dataSheet.getRange(7, 2).getValue();               //Getting value from B7 in "Raw Data"
    var value2 = dataSheet.getRange(13, 2).getValue();              //Getting value from B13 in "Raw Data"

    if (value1 > 0.1 && value2 > 0.1){                              //If both values gotten above is greater than 0.1 (10%)
      listSheet.getRange(currRow, 8).setValue('True');              //Set the value of the cell in current row, in the H column to "True"
    }else{                                                          //Else
      listSheet.getRange(currRow, 8).setValue('False');             //Set the value of the cell in current row, in the H column to "False"
    }
    currRow++;                                                      //Go to next row
  }
}

PC site About | Contact

©2016 avrocks.com all rights reserved

processed in 0.206 (s). 5 q(s)