| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence (Part II)
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence (Part II)
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence (Part II)
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| list missing values in a sequence | Excel Discussion (Misc queries) | |||
| Inserting and Tracking Missing Sequence | Excel Discussion (Misc queries) | |||
| I need help with an "If" Statement | Excel Worksheet Functions | |||
| Part Number Lookup | Excel Worksheet Functions | |||
| Automatic Number Sequence | Excel Discussion (Misc queries) | |||
Linear Mode
