Excel - count how long it will takes for a number to repeat

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

  T. Valko replied to sierra spiegel

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

Delete matching cells Excel Col A has 360, 000 cells. Col B has 240, 000 cells. A B 0000000021957 0000000341991 0000000481999 0000000401961 0000000482000 How can I delete the cells in Col A whose contents match cells in Col B? Excel Programming Discussions WorksheetFunction (1) Excel 2007 (1) Excel 2010 (1) Error (1) Excel (1) Distributed (1) Worksheet (1) TRANSPOSE (1) One method. Insert a column left of Column A Insert a row at top Add titles in A1:C1. . . . . . . . . . will need for filtering

Search for matches in two columns Excel First two columns of data which will vary depending on other functions in worksheet 8 0 2 5 1 5 0 4 0 0 I want to in a third column to list any occurences of values in column 2 that appear in column 1, including multiples, there will always be 16 rows of this data. I already have be helpful to blank any 0 value. Would be nice if empty values in third column are blank, but can deal with anything. Thank You in advance Gary Excel Worksheet Discussions Microsoft Excel (1) Excel 2003 (1) EstusedSHIFTs (1) SUMPRODUCT (1) Worksheet (1) ISNUMBER (1) COUNTIF (1) INDEX

SUM with two conditions Excel I have been struggling with this for awhile, so I decided to post. I am sheet are assigned to a given course. In the second (textbook) sheet, I have a column that I put the courses to which are assigned each textbook. COLUMN A Includes "YES" if the textbook is still actively used or "NO" if it is discontinued. COLUMN B Title of textbook COLUMN T Courses (e.g., "PSY 250", "PSY 260, PSY 250", "") There are three possibilities: (1) No textbooks are assigned to that course. (No course IDs in Column T) (2) A textbook is assigned to just the one course. (Only one course ID in Column T) (3) A textbook is assigned to more than one course, including the course in question. (More than one course ID in Column T) So, I came up with this formula, which I typed in cell A22 of

to CONCATENATE from the smallest date with at least 2 criteria Excel Dear All, Here is the sample database. Date Salesman Region 16-Aug-08 A N the date in that arrays). thank you for your kind attention and help. respectfully, andri Excel Worksheet Discussions Microsoft Excel (1) Excel (1) CONCATENATE (1) SUBSTITUTE (1) SUMPRODUCT (1) Workbook (1) COUNTIF (1) LOOKUP (1) Andri, As long as those are properly formatted dates try this ARRAY by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You cannot type these yourself. If you edit intReturnColumn, strFormat As String, strDelimiter) rngRange is the data range intLookupColumn1 is the First lookup column strLookupValue1 is the value to be looked up in the first lookup column intLookupColumn2 is

Counting by Two Columns of Variables Excel I have three worksheets: call data, staff, and VIPs. The staff and VIP worksheets each contain only a single column of names. The call data sheet contains a list of staff names and caller names one of those on the VIP sheet. I have tried playing around with COUNT and MATCH to no avail. How would I perform such a count? Excel Miscellaneous Discussions Microsoft Excel (1) SUMPRODUCT (1) ISNUMBER (1) MATCH (1) COUNT (1) ISNA (1) VIPlistrange (1) MvsnH3AQ51 (1) add a column to the call data and use this formula in c2 = if(iserror(match(A2, Stafflistrange