differencing sets of numbers, multiplying, and summing

Hi, i'm new to using MS Excel when it comes to anything beyond viewing a table of data.

I am seeking some guidance in creating the following:

A daily task i'd like automate consists of entering 56 numbers (up to 3 digits, 000-999) and finding the difference between today's numbers and yesterday's numbers. Then multiplying each difference by a set number (i.e. 1, 2, 3, 5, 10, 20, or 25). These numbers may, from time to time change (i.e. the 32nd entered number may be multiplied by 3 for 2 months, then we might swap out a product and then need to multiply by 5, etc). At the end, I need all 56 differences multiplied by their individual respective multipliers totaled. This needs to be done daily using previous days numbers and be able to change the multiplier without effective previous data.

I was trying to to this myself by Column 1 = multipliers, Column 2 = yesterdays' numbers and Column 3 = today's numbers. The calcualtions are done by excel and a total is summed. This I can do. However, each day i'd need to make a new file and enter yesterday's and today's numbers. This is time consuming. I'd like a system where I can just entere today's numbers and any changes to the multiplier and it will spit out the total.

If anyone can guide me or point me in any direction to accomplish this automation. I would greatly appreciate it. I'm pretty computer savvy, but just have not had the time to dwell into Excel.

For those interested in what this is for: I have a small business that sells instant lottery tickets. Each day we have to figure out the number of tickets sold the previous day. We have a notebook and manually write down numbers, do the substractions by a calculator and total. The table in the notebook is seperated by ticket values (i.e. $1 tickets, $5 tickets, etc). However, certain tickets run out or occasionally we need to move tickets to a different spot, thus the MULTIPLIER in the automated system would need to change.

Thanks!
-stocknewb.

Welcome to the forum.

Please read the Forum Rules about thread titles, and then edit yours to be descriptive of your problem.

shg, will do as soon as I figure out where the edit option is? I'm used to it being near the bottom right (i.e. near quote, etc), but I don't see it?? I'm logged in. Point me to it and i'll edit asap. Thanks.

EDIT: That's so strange, the first post has not 'edit' icon and this post does.... hmm.

You can only edit a thread for two hours after it's posted. Post with a new thread title and I or another mod will change it.

How about?

"automating task: differencing sets of numbers, multiplying, and summing; slight daily modifications therein"

Or if you have a better title, that's fine with me. Thanks.

One time saver, until a solution is found. Instead of

each day i'd need to make a new file and enter yesterday's and today's numbers.

you can d a simple copy and paste of the old data into the new book.

Are you looking to keep historical data or do you just need the daily total?

How about?

"automating task: differencing sets of numbers, multiplying, and summing; slight daily modifications therein"

Done. However, I would commend that you try that as a Google search and see how many useful hits it gets.

The rationale for relevant thread titles is that it makes the forum usefully searchable for other members. How do you think that stacks up?

Does the attachment do what you want.

When opened it creates a toolbar with a single button.

When clicked, the button will create a new sheet named with the current date. It then copies the previous day's data into the new sheet, copies the quantities from the Today column (which is actually yesterday) into the Yesterday column and then clears the Today column for the new day's entries.

mdbct: Many thanks! I think this will work great, the button for creating a new sheet is something I didn't know how to do. With that and copying the previous day's "today" numbers into "yesterday" works great. Also, it seems you can change the multiplier on any given day or modify the data and it doesn't effect previous data.

This is awesome! Again, many thanks!

mdbct: May I request one more thing, i've attached a file that has a slightly different layout from the one you sent me. Though, when I changed Tracker.xls to use this layout, it didn't copy the correct columns because I added a few columns and shifted those yesterday and today columns over.

Would you be able to make the same macro? I tried and didn't get it to work as nice as yours was. Same thing you had in 'Tracker.xls': create a new sheet with the date, copy the layout, and move "today" to "yeterday" -- for some reason my macro keeps failing to do it correctly.

Thanks again for your help! Have a good one.

Updated version is attached.

Thanks again man! This will work great. Saves us a little time each day. Time is money!

Have a good one.