Results 1 to 2 of 2

Thread: Problem for Puzzle Solvers

  1. #1
    Neophyte kkline116's Avatar
    Join Date
    Jan 2017
    Posts
    1
    Articles
    0
    Excel Version
    2016

    How to reformat table to reduce rework



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all...

    Posting here because it's getting the formulas to work that I'm struggling with. This has been bothering me for over a day because I know the answer is RIGHT THERE, but I can't seem to get my mind around what to do. I'll do my best to explain, but it's kinda complicated...

    Please see attached excel file.

    What it currently does:


    You input data into the yellow fields, and at the very bottom out pops the cost specific to that quantity. If you change the quantity, the price reduces because we divide the setup hours by the number of pieces ordered (please see formulas to see relationships).

    We then take that total cost, put that into our quote, then change the quantity, put that into the quote, change the quantity, put that into the quote...

    I learned a long time ago that if you're repeating doing anything in excel, then you're not using excel correctly. This introduces a LOT of potential for human error in hand typing the numbers into the quote. Then, if something changes, you have to redo everything, and it's just as big hassle.

    What I want it to do:

    I want to have the quantities in a single column on the left, so we can just "list" the quantities the customer wants quoted. Then fill in the Cycle/Min, fill in the Setup Hours, and to the right of this will come the cost for that quantity. That part is easy (I think), but the hard part comes when doing the same thing for the next quantity set... because the new quantity will use the same numbers in Cycle and Setup, but the time for Setup is divided by this new quantity... and for that to have a total cost on the new quantity line for that quantity is what's tripping me up.

    I know that sounds confusing, and it probably is... ha ha... but if you take a look at what the current table does that should help.

    The end goal is to have all the quantities listed (say 10, 20, 30, 40, 50) with the cost per that quantity on the same row, without having to retype the quantity over and over to get the cost to change (as is now).

    Final thought... I'm fine if this has to be split into 2 tables, or any other type of format / rearranging that needs to happen to make it work. The only "input fields" (again, in yellow) are Quantity, Cycle/Min, and Setup Hours, everything else should be automatically filled in.

    Thanks for your time.
    Attached Files Attached Files
    Last edited by kkline116; 2017-01-18 at 01:37 PM. Reason: Title change

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    You have explained what you need quite well, but your example worksheet doesn't include much detail. As a result, I don't feel able to to make suggestions in any depth, but I will outline a method that can develop, or perhaps discount
    and explain why.
    See attachment.
    Attached Files Attached Files

Posting Permissions

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