Weight Break calculations - A method question
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Weight Break calculations - A method question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Weight Break calculations - A method question

    Hey everyone,

    Although this account is new I have been around before and ive always gotten good answers and im hoping once again i may get some help.

    This question is about how to convert a single line of parameters into slightly different static parameters. I could quite easily create a nested IF formula to dynamically calculate the answers i require but this is intended to create a database to be imported into an antiquated Microsoft Dynamics CRM which requires a series of static integers to calculate upon.

    The math is simple.


    Minimum Weight MIN 0 45 100 250 300 500 1000
    6.266319 246 39.2575 21.115 15.785 0 14.35 13.8375 0


    The figures across the top are related as follows.

    Minimum Weight = Minimum possible weight for a job
    MIN = Minimum possible charge for a job
    0 = Rate for jobs between 0 and =<45
    45 = Rate for jobs between 45 and =<100
    etc

    The interesting bit is that for a job that is say, 24KG, the charge would be 942.18. When the weight increases to 25KG the same bracket would make the charge 981.4375.

    The charge for 45kg (The bottom of the next bracket) would be 950.175, and because this is less than the same charge for a weight higher than 24.2kg you would want to charge at the 45kg rate (you assume the shipment is then 45kg to reduce your costs).

    With this information, a new set of optimal weight brackets can be configured.

    In this case it would look like: between 0 and 24.2 at rate 39.2575 and the next bracket would be 24.3 to 45 at 21.115 (with a minimum weight to charge of 45).

    This reworked optimal bracketing is what the Database requires.

    It would look like this.

    Minimum Weight Maximum Weight Minimum Weight to Charge Rate
    0 24.2 0 39.2575
    24.21 44.99 45 21.115

    So for each line in the existing table there will be 6 new rows for that one configuration.

    What i was trying to achieve was a formula that would enable me to run it down a large sheet of 1000+ lines and output the new optimized data set in another sheet that i could then use for importing.

    I appreciate that i haven't given a huge amount of info here but its all ive got. I was trying to avoid using VBA to do this. Otherwise i might aswell write a script in C# to do it. However if someone can give direction with the VBA im happy to use that as a solution.

    If it would be useful i can also attach a snippet from the DB in question.

    Thanks in advance for anyone who has the time to assist me.

    Sincerely, Lewis.

  2. #2
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weight Break calculations - A method question

    More info.

    Having worked through the problem I now have all of the math required. But no idea how to automate the output.

    https://www.filehosting.org/file/det...xampleIOB.xlsx

    This can be used to download the small sample sheet i made.

    You will notice that on the Input sheet there are many rows. The calculations for the first 2 rows ive done in the Output, ive left the formulas in their.

    There are 8 output rows for each individual row in the input sheet.

    Hoping someone with some smarts can help me automate it to let it run through a whole list and output accordingly.

    Thanks again.

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Weight Break calculations - A method question

    Your file is NOT shared!
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weight Break calculations - A method question

    What do you mean its not shared? You just enter your email address and it sends you the download link.

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Weight Break calculations - A method question

    Shared means : no emails, no logging, no registration just download, that's all
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weight Break calculations - A method question

    Is there a service you would recommend for me to have it accessible in a format that would be shared?

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Weight Break calculations - A method question

    your service should have this option, if not try googledrive, onedrive, dropbox or any similar
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weight Break calculations - A method question

    This should be better https://we.tl/t-NcWMrAs3tI

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,678
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Weight Break calculations - A method question

    Quote Originally Posted by LMCT3 View Post
    This should be better https://we.tl/t-NcWMrAs3tI
    right
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weight Break calculations - A method question

    Thanks for the assistance, my apologies for poor format.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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