Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a column with "Y" and "N" values, and need a formula to count the last consecutive occurrences of "Y". So if I the column looks like this:

Y
Y
Y
N
Y
N
Y
Y

the desired result would be 2

Y
Y
Y
Y
N
N
Y

and here it would be 1

Y
Y
Y
Y
N

and here it would be 0. Is this possible with things like Excel, Numbers or Google Docs?

share|improve this question
    
Have you seen my solution to you problem? Could you give me some feedback? –  Jacob Jan Tuinstra Dec 20 '12 at 19:53
add comment

1 Answer

This little google apps script will make it happen:

function LASTCON(array) {
  // set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get data  
  var data = sh.getRange(array).getValues();

  // determine length of array
  var dLen = data.length; 

  // create yes counter
  var yesCount = 0;  

  // itterate through array (backwards)  
  for(var i=dLen-1; i>=0; i--) {
    if(data[i] == "Y") {      
      yesCount++;
    } else {
      break;
    }
  }

  // pass result to formula 
  return yesCount;
}

Add this script, via the script editor, to your spreadsheet and the function LASTCON is available throughout the worksheet, like so: =LASTCON("A1:A6")

See example file I've prepared: Last consecutive values in a column (editable)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.