finding next instance of text in a cell

**pmetzak** · 02-28-2008, 08:02 PM

Hello,

I am trying to use excel to calculate the number of button presses in a trial. I have managed to extract the column and row information for the beginning of each trial and I am hoping that someone will be able to help me write a formula that will extract the location (column and row) of the end of each trial so I can use countif to calculate the number of button presses in each trial.

The end of each trial is marked by the entry of cell marked 'ITI'. Since I already know the location of the beginning of each trial, I just to know how to find the next appearance of ITI in the subsequent rows. For instance the trial begins in cell C42 and it ends somewhere in column C below the 42nd row.

Thank you very much, and if any more information is necessary, please let me know.

Paul

**Claymation** · 02-28-2008, 09:03 PM

You may have to adjust the ranges a bit, but it sounds like what you need.

=IF(ROWS($G$4:G4)<=$G$3,
CELL("Address",INDEX($A$2:$D$13,
SMALL(IF($A$2:$D$13=$G$2,ROW($A$2:$D$13)-ROW($A$2)+1),
ROWS($G$4:G4)),
SMALL(IF($A$2:$D$13=$G$2,COLUMN($A$2:$D$13)-COLUMN($A$2)+1),
ROWS($G$4:G4)))),
"")

Hope that helps.