RE: Indicate missing number in a sequence (Part II)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Try:

=IF(A2< 500,IF(A2=B1+1,"", B1+1 & "-" & A2-1),"")

Using this test data:
A B C
1 10
11 20
21 490
493 500 491-492
501 510

Is this what you want?


"mmock" wrote:

Thank you again for helping me with the formula below. I hoping you can help
me with one more twist. I only need the formula to count to 500. There are
only 500 tickets in the booklet. If the count goes higher than 500 I do not
want it to count an out of sequence or just leave the cell blank. Does this
make sence?

Thank you for the assistance!
M.




Assuming you are happy with Gary's solution, try this modified version which
gives range of missing tickets i.e. 15-16 in your example in row 3:

=IF(A2=B1+1,"", B1+1 & "-" & A2-1)

"mmock" wrote:

You are correct......thank you again, I would have another ticket missing!!

"Toppers" wrote:

Shouldn't that be 11 tickets sold (numbers to 1 to 11 inclusive)?

"mmock" wrote:

Maybe to clarify more. On same days I will use or sell more than one ticket.
Line B1 - A1 = C1 (or 10 tickets sold). Is the +1 used only for when it is
1 above the previous number?
Thanks,
Mark


"mmock" wrote:

Prior to sending this post, I have read many posts and have not quite found
the answer I am looking for. I hope someone can help me with a formula.

I need to track tickets used. More importantly, I need to know if a ticket
in a sequence is missing. I will be tracking start & stop numbers and
tickets used. I'm looking for an indicator of some sort to tell me when a
ticket is missing or out of sequence.
Example

A1=1 B1=11 C1=10
A2=12 B2=14 C2=2
A3=17 B3=20 C3=3 (FLAG the 2 missing tickets 15 & 16)

Any assistance would be apprciated.
thanks,
Mark


.

Relevant Pages

  • RE: Indicate missing number in a sequence (Part II)
    ... "Toppers" wrote: ... "mmock" wrote: ... only 500 tickets in the booklet. ... in a sequence is missing. ...
    (microsoft.public.excel.misc)
  • RE: Indicate missing number in a sequence (Part II)
    ... "mmock" wrote: ... "Toppers" wrote: ... only 500 tickets in the booklet. ... in a sequence is missing. ...
    (microsoft.public.excel.misc)
  • Re: Query to find a missing number
    ... Let's assume we have a table of people who bought tickets that are ... PRIMARY KEY (buyer, ticket_nbr)); ... FROM Tickets AS T1, Sequence AS S1 ... AND seq NOT IN (SELECT ticket_nbr -- get missing numbers ...
    (comp.databases.ms-sqlserver)
  • Re: Query to find amissing number
    ... Let's assume we have a table of people who bought tickets that are ... PRIMARY KEY (buyer, ticket_nbr)); ... FROM Tickets AS T1, Sequence AS S1 ... AND seq NOT IN (SELECT ticket_nbr -- get missing numbers ...
    (comp.databases.sybase)