Count Skips Between Last Occurrence

  • So basically ... you do need a single check ... but throughout your FIVE columns C - G ...


    See attached Version 3


    Hope it is in line with your expectations ...

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Almost. Very close.


    Your formula result (C25), "7" for #24 is correct. My manual count, "6", was incorrect.


    I tried to enter a few numbers for the next drawing to see how the formulas on Sheet SBLO update and they don't exactly.


    Enter 39, 40, 41, 42, 43 as the next winning numbers on the Data Sheet and see what results you get for SBLO.


    Also, try entering the last winning numbers 2, 6, 11, 13, 31 as the winning numbers for the next drawing to see what you get. The formula update should be "0" for each number. I'm getting "-1" as a result.


    Lastly, whenever I enter the winning numbers for the next drawing, all of the results should update with an additional skip.


    In other words, if #7 didn't hit on the next drawing and the formula results show it had "1" skip, when I enter the next winning numbers, although "7" wasn't one of the winning numbers, the formula should update to show it's now "2" skips IF it were to hit in the next drawing.

  • If I am not mistaken ... it is only my guess ... you have left cell A878 empty ... and Column A is THE reference column to count the total number of records ...


    Am I wrong ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    I moved the formula you created for me yesterday to another workbook. When I tried to reopen the workbook this morning, I get a message to update links.


    Why can't I use that formula in another workbook without having to link it to the workbook that it was created in?


    I made a worksheet reference change in the formula from Data! to DB! which is an exact copy of the Data worksheet in Test SBLO V3.xlsm


    Test SBLO V3 - {=COUNTA(Data!$A:$A)-MAX(IF(ListN1=A6,ROW(ListN1)-MIN(ROW(ListN1))+1))-1}


    New Workbook - {=COUNTA(DB!$A:$A)-MAX(IF(ListN1=A6,ROW(ListN1)-MIN(ROW(ListN1))+1))-1}


    Does it have anything to do with "ListN1"? I really don't understand that part of the formula. What is ListN1? I'll research ROW function more.


    I'd really need to be able to use the formula without having to link it to another workbook.


    When I try to break the links, it warns me to save a copy. I'm not sure what is going on. I'll try to research this more myself because I need to understand why I can't use the formula as written.


    I wonder if a subroutine is the way to go.

  • Hello,


    In any workbook where you would like to use the formula ... the only thing to do is to create a Dynamic Named Range ...


    Control F3 ... > New > the Name you want > Refers to > =OFFSET(Data!$C$2,0,0,COUNTA(Data!$C:$C)-1,5) > OK


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Perfect! Does help.


    Once again, thanks for sharing your Excel brilliance with mere mortals like myself.^^

    You are welcome :)


    Never forget our common destiny ... IS ... to BE MERE MORTALS .... And PAY TAXES ... as Woody Allen used to say ...!!! ;(:D;(

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)