Balance lottery sales

[dtomaras4]

First time on this forum, looking for help with Excel. I own a small business and we sell lottery tickets. At the end of the day I use Excel to balance our lottery sales. The formula I have created in Excel works but is a bit cumbersome. I was looking for a better way to enter book numbers and have the worksheet calculate the $ that should be in the register. I currently have 3 lines of formulas that are all IF statements. The problem with this is that we constantly get new books in and if we get a new $10 book and I need to shift a few books around to get it in the cases near the other $10 books I have to completely redo the worksheet. I'm wondering if there is better way to do this in Excel. I tried to look at Excel tutorials and other help references but just can't seem to find a figure it out. I have been using this spreadsheet for so long I just can't think of another way to do it. I'm okay with Excel but I'm sure i only use a small percentage of it's features to begin with. Any help would be greatly appreciated.

I have attached a small section of the spreadsheet I am currently using. Red numbers are formulas and black numbers are the numbers we enter into the worksheet.

http://www.excelforum.com/attachment...1&d=1276098560

[6StringJazzer]

I looked at your workbook and think this can be simplified. But I need to fill in some holes in your description.

I notice that the numbers as they go to the right are the same or decreasing. Are these serial numbers printed on each individual ticket? Serial number of a book of tickets? Do you sell tickets in descending order of the serial numbers? It seems that you are using these serial numbers to determine how many tickets were sold on a given day.

The formulas in column C are all giving me a #VALUE! error. These formulas refer to value in column B, but column B is all text. Are you off by one column?

I don't understand how your formula works. It seems like it should be straightforward but let's take for example one part of the formula in D48, the part that deals with row 3:

IF(C3<D3,C3+30-D3,C3-D3)*30

For row 3, this says if the serial number went up from the previous day, then determine the difference in the two days and add 30. In other calculations you add 60, sometimes 120, I can't figure that out. Where does that logic come from? (You multiply it all by 30 to get the dollar amount; I get that part.) If the serial number goes down, you subtract to get the number of tickets sold, that seems to make sense.

Because Column A has the ticket value, it should be straightforward to simplify your formulas and make them immune to how you have ordered your books, once I understand the above.

[dtomaras4]

Yes, each ticket is numbered and we do sell in descending order. We have $30, $20, $10, $5, $3, $2 and $1 tickets. The number I tell it to add is the number of tickets in a book, which varies usually by the dollar amount of the tickets. $30 and $20 tickets contain 30 tickets in a book, $10 tickets contain 60, $5 120, $3 100, $2 150, and $1 300 tickets in a book. So lets say we are looking at a $30 ticket. A book of $30 tickets contains 30 tickets. If I sell out a book and replace it with a new book the number up (since we sell in reverse order), so I need to make sure the formula accounts for that. So, lets say I start the day with two $30 tickets (#1 and #0) in the bin, I sell them both and put a new book in the bin which starts with #29 (#29 to #0 for 30 tickets per book) but don't sell any more. My formula sees an increase in the number, adds 30 to the #1 (which ended the day before) then subtracts 29 which is my ending number and and it calculates that I sold 2 tickets. It then multiplies that by $30 and I should have collected $60 for that book and then it moves on to the next line and adds them all together for a total of how much we sold that day in instant tickets.

As for column C having a "value" error in them, I have to start somewhere but that column of numbers is just my starting point and calculations start on the next column to the right, D.

Hope I haven't confused you more.

[6StringJazzer]

Hope I haven't confused you more.

Actually that's very clear! I'm taking a look at this. I have one solution that requires an additional worksheet with "helper columns" in parallel to the existing one; I think there may be a solution with array formulas that does not require the additional sheet.

Meantime, what does -1 mean?

[shg]

Maybe as attached.

[dtomaras4]

the ticket numbers actually go to 0 so when we sell out a book we use "-1" to show that there are no tickets left. If we used 0 it would look like there was still one ticket left. If we have a book with 30 tickets in it they would be numbered 29 to 0.

[6StringJazzer]

I see that you responded to my question, but shg's solution seems quite elegant. If you find that useful then I don't need to carry on further, let us know how you're doing.

[dtomaras4]

I'll try that and let you know how it works. THANKS!!

[rizbiz]

Hey if your still having issues with your lottery balance sheet you can check mine out its not completely done the way you want it but you can add your cash drawer balance on it but the daily sales for the ticket work good.

I am having then same issue as you, how to go about add a new ticket with different amount

pls check it out

[dtomaras4]

The formula I got is working great with one exception... If we run out of a book of lottery tickets, let's say a $10 ticket, and I want to replace it with a book the is a different $ amount, lets say a $5 ticket book, how do I do that with out changing all my previous numbers. Right now I am just starting a new spreadsheet every time we need to change a book to a different $ amount. Is there a better way to do this in excel so it won't change previous sales figures? I know Rizbiz asked the same question in a different post but I didn't see an answer there.
Thanks.

[Shier]

Hello , I would appreciate if someone helps me create an excel balancing sheet for lotto and lottery ( instant) sales: the variables are; physical count of lottery tickets at the start and end of shift/day, winning report from cash register for cashed out tickets( Paid Out). Shift/daily sales from cash register for both, winnings per shift/day for instant and online sales from Terminal.
Thanks

[6StringJazzer]

Shier, welcome to the Forum but please take the time to review our rules. There aren't many, and they are all important.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[Shier]

My apologies first time user. I shall start a new thread.

[vishal50002004]

I can make you one excel sheet but first let me ask you few questions
do you really care about pack number and name or just amount of ticket and ticket number (there is difference between pack number and ticket number )
do you sell number 1 ticket first and 50 at last (straight way ) or
number 50 first and number 1 last (opposite way)

opposite way formula is easy to make and require only shift end numbers but straight is hard and require opening and ending both numbers and you can not add new pack in same slot if that slot had a ticket same day
so let me know

[vishal50002004]

here is what I made
please reply if it helps or find error and we can discuss

[6StringJazzer]

I can make you one excel sheet[...]

Welcome to the Forum!

You are answering a question that is 7 years old. Perhaps your energy would be better spent on recent threads.

[meadowspizza]

could you make me a excel sheet?

[protonLeah]

meadowspizza,
When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.