Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .