Need help with formula for counting packets of scratch tickets

[gepullen]

Hello, I am new here and hope that someone out there can assist me with my formula. I currently use an excel formula that will count a packet of scratch tickets. Packets vary depending on dollar amount of the ticket. So the formula below that I can currently using is for a 2 dollar ticket which contains 100 tickets per packet. The problem is that let's say I sell all of that packet and start a new packet the same day, it is not properly counting the new tickets sold on the same day.

So let's say on this day, I sell the last 5 tickets of the current pack and open a new pack and sell 3 more tickets. My result at the end of the day when I type in the ticket number of 003 for my total sold should read 16 dollars. 5 from previous pack and 3 from the new pack. Tickets start with 000 and go to 099 on this pack.

D3 will the the last number recorded from the day before so in this case that number would be 95. D5 will be the current day end in which I sold 3 so the number would be 003.

=IF(AND(ISBLANK(D5)=TRUE,D5=""),"",IF(AND(ISBLANK(D5)=FALSE,D5>D3),(D5-D3)*2,IF(AND(ISBLANK(D5)=FALSE,D5=D3),0,IF(AND(ISBLANK(D5)=TRUE,D5>=0),(100-D3)*2,IF(AND(ISBLANK(D5)=FALSE,D5<>0),(100-D3+D5)*2,IF(AND(ISBLANK(D5)=FALSE,D5>=0),(100-D3)*2,"-"))))))

If anyone can help with this, it would be fantastic. What I get sometimes is a total count of 205. So it is counting one whole additional pack plus the 5 that were sold the day before.

Thanks in advance

[bhenlee]

Please attach a sample file so we could take a look. it's easier to do it in excel of course

[mahju]

Hi
Tickets starts from 000 and go to 099

So if you start new packet and sold 30 tickets (say)

Then D3=000
D5=29

and the amount will be (d5-d3+1)*2

on this ground you may try this

Use "isblank" function etc as needed)

Please Login or Register  to view this content.

[jason.b75]

Tickets start with 000 and go to 099 on this pack.

D3 will the the last number recorded from the day before so in this case that number would be 95. D5 will be the current day end in which I sold 3 so the number would be 003.

Your numbers don't add up the same as your description. If you sold 3, then surely the number would be 002, not 003. (000,001 and 002 = 3 tickets).

See if this works.

=IF(D3>D5,100-D3+D5,D5-D3)*2

[gepullen]

Your numbers don't add up the same as your description. If you sold 3, then surely the number would be 002, not 003. (000,001 and 002 = 3 tickets).

See if this works.

=IF(D3>D5,100-D3+D5,D5-D3)*2

It works great. I guess with all the string functions I was making the formula more complicated than it needed to be.