I own a small business and I'm having trouble with employees adding incorrectly and I'm wanting to create a spreadsheet that will do the math for them. As a part of my business I sell lottery scratch off tickets. Each ticket is numbered and we count them down at the end of the day to determine how much money should be accounted for. I have a spreadsheet set up with the starting number in one column, ending number in another column, and a total dollar amount column. So far its pretty simple but the problem is when we run out of a ticket and replace it with a new pack of tickets. So I need it to calculate what was sold on the previous pack and also add what was sold on the new pack. Each dollar amount has a set number of tickets per pack. For example a $5.00 lottery ticket pack will have a total of 60 tickets per pack. Can you assist me with a formula to calculate this?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Formaula Assistance
- Thread starter jlyons88
- Start date
Oaktree
MrExcel MVP
- Joined
- Jun 20, 2002
- Messages
- 8,121
- Office Version
- 365
Welcome to the board.
Not sure if you want to include both the start & end point as tickets sold... if you want to include only the starting number, just remove the +1 below.
If the numbers always increased, you could use something like
To account for 61 resetting to 1, you could use the small modification of
But, the thing this can't account for... suppose you start the day with the last one in the pack (# 60) and have an unusually popular day where you sell 62 of them. You'd start with # 60 , end with # 1. The formula would imply that you only sold 2. Not sure how/if you'd want to account for selling a full ream.
Not sure if you want to include both the start & end point as tickets sold... if you want to include only the starting number, just remove the +1 below.
If the numbers always increased, you could use something like
Code:
=$5*(B2-A2+1)To account for 61 resetting to 1, you could use the small modification of
Code:
=5*(B2-A2+1+60*(B2 < A2))But, the thing this can't account for... suppose you start the day with the last one in the pack (# 60) and have an unusually popular day where you sell 62 of them. You'd start with # 60 , end with # 1. The formula would imply that you only sold 2. Not sure how/if you'd want to account for selling a full ream.
Last edited:
Upvote
0
