Count since last out

    I am trying to work out the interval between apperences of a number in a lottery game [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 240"]

    [tr]


    [TD="class: xl65, width: 64"] N1[/TD]
    [TD="class: xl65, width: 64"]N2[/TD]
    [TD="class: xl65, width: 64"]N3[/TD]
    [TD="class: xl65, width: 64"]N4[/TD]
    [TD="class: xl65, width: 64"]N5[/TD]

    [/tr]


    [tr]


    [TD="class: xl66"]18[/TD]
    [TD="class: xl67"]19[/TD]
    [TD="class: xl68"]29[/TD]
    [TD="class: xl69"]33[/TD]
    [TD="class: xl70"]37[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]2[/TD]
    [TD="class: xl65"]4[/TD]
    [TD="class: xl65"]25[/TD]
    [TD="class: xl65"]32[/TD]
    [TD="class: xl65"]38[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]6[/TD]
    [TD="class: xl65"]9[/TD]
    [TD="class: xl65"]22[/TD]
    [TD="class: xl65"]26[/TD]
    [TD="class: xl65"]31[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]3[/TD]
    [TD="class: xl65"]13[/TD]
    [TD="class: xl65"]19[/TD]
    [TD="class: xl69"]33[/TD]
    [TD="class: xl70"]37[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]4[/TD]
    [TD="class: xl65"]5[/TD]
    [TD="class: xl65"]10[/TD]
    [TD="class: xl65"]26[/TD]
    [TD="class: xl65"]36[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]16[/TD]
    [TD="class: xl66"]18[/TD]
    [TD="class: xl65"]28[/TD]
    [TD="class: xl65"]34[/TD]
    [TD="class: xl65"]36[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]14[/TD]
    [TD="class: xl67"]19[/TD]
    [TD="class: xl65"]22[/TD]
    [TD="class: xl69"]33[/TD]
    [TD="class: xl70"]37[/TD]

    [/tr]


    [tr]


    [TD="class: xl65"]8[/TD]
    [TD="class: xl65"]12[/TD]
    [TD="class: xl65"]23[/TD]
    [TD="class: xl65"]25[/TD]
    [TD="class: xl68"]29[/TD]

    [/tr]


    [/TABLE]

    Using just the top row for this example
    No. 18 last appered 5 games ago
    No. 19 last appered 3 games ago and then 6 games ago
    No. 29 last appered 7 games ago
    No. 33 last appered 3 games ago and then 6 games ago
    No. 37 last appered 3 games ago and then 6 games ago


    I am hoping to show answers in a columns " 1st time out, 2nd time out" etc...


    Hope you can understand what i am trying to explain


    Thanks


    Nigel

    It woudlbe easier if you showed us your expected outcomes, and easier still if you attached a sample workbook with source data and expected outcomes added manually.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    In J2:


    =IFERROR(INDEX($A$2:$A$26,SMALL(IF($C$2:$G$26=$I2,ROW($C$2:$G$26)),COLUMNS($J2:J2))-1),"")


    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, drag copy across and down.

    Please let us know if this has worked for you.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    You're welcome, Nigel! :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login