Indicate missing number in a sequence (Part II)

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
> > > >

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
> > > > >
>

Thank you TOppers.....it worked great.
One more question...what it the purpose of the space after the < 500. Does
it matter if there is a space there???

Thank you again for your help!
m.


"Toppers" wrote:

> 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
> > > > > >
> >

Not needed ...<500 will work.

"mmock" wrote:

> Thank you TOppers.....it worked great.
> One more question...what it the purpose of the space after the < 500. Does
> it matter if there is a space there???
>
> Thank you again for your help!
> m.
>
>
> "Toppers" wrote:
>
> > 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
> > > > > > >
> > >