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/tutorials...vb-tester.aspx

it takes 7 calls for number 1 to repeat.
it takes 8 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

--
Biff
Microsoft Excel MVP


<sierra spiegel wrote in message
...
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/tutorials...vb-tester.aspx