+ Reply to Thread
Results 1 to 13 of 13

differencing sets of numbers, multiplying, and summing

  1. #1
    Registered User
    Join Date
    09-01-2008
    Location
    Philly
    Posts
    7

    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.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Welcome to the forum.

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

  3. #3
    Registered User
    Join Date
    09-01-2008
    Location
    Philly
    Posts
    7
    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.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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.

  5. #5
    Registered User
    Join Date
    09-01-2008
    Location
    Philly
    Posts
    7
    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.

  6. #6
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848
    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?

  7. #7
    Registered User
    Join Date
    09-01-2008
    Location
    Philly
    Posts
    7
    Quote Originally Posted by mdbct View Post
    One time saver, until a solution is found. Instead of 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?
    I'd like to somehow save historical data. Also, my elderly parents are the owners and run the business. I'm trying to automate this for them and they're not computer savvy enough, such that making new files, copying data into the new sheet or file, etc... would be just as time consuming for them as doing it manually. So, I guess i'm looking for an all-in-one quicker solution. Thanks for any input/guidance. Have a good one.

    We just installed a computer at the business for the first time ever... haha... For now, i'll have them continue to do it manually as, all-in-all, it doesn't take hours or anything, merely 20 minutes max. I figured, with the computer there, there might be a way to do it even faster and without using a calculator to double and triple check at times...ya know.
    Last edited by stocknewb; 09-01-2008 at 11:01 PM.

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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?

  9. #9
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848
    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.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-01-2008
    Location
    Philly
    Posts
    7
    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!

  11. #11
    Registered User
    Join Date
    09-01-2008
    Location
    Philly
    Posts
    7
    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.
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848
    Updated version is attached.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    09-01-2008
    Location
    Philly
    Posts
    7
    Thanks again man! This will work great. Saves us a little time each day. Time is money!

    Have a good one.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multiplying and summing attributes that appear in a list
    By Johan_E in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2008, 01:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1