count how long it will takes for a number to repeat

[sierra spiegel]

I need help creating a equation to count how long it takes for a number to repeat. For example:

1 2 3 4 5 6 7 8 1

so it takes 7 calls for number 1 to repeat.

thanks

EggHeadCafe - Software Developer Portal of Choice
XML/XSL VB Tester
http://www.eggheadcafe.com/tutorial...-4e21-9b2c-13f77a74daff/xmlxsl-vb-tester.aspx

 

[T. Valko]

it takes 7 calls for number 1 to repeat.
Try this array formula** :

=IF(COUNTIF(A1:K1,1)>1,SMALL(IF(A1:K1=1,COLUMN(A1:K1)),2)-COLUMN(A1)+1-MATCH(1,A1:K1,0),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER