+ Reply to Thread
Results 1 to 22 of 22

I need a mround type formula that only rounds up to a max number.

  1. #1
    Registered User
    Join Date
    07-26-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    17

    I need a mround type formula that only rounds up to a max number.

    I need a formula for to help determine what I can ship out. It needs to give me a number that is only skid quantites based on what needs to ship but I need it to not exceed what is on hand.
    Attached is an example workbook.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-18-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: I need a mround type formula that only rounds up to a max number.

    Try this:

    =ROUND((H8/F8),0)*F8

  3. #3
    Registered User
    Join Date
    07-26-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need a mround type formula that only rounds up to a max number.

    Thank you M1234.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: I need a mround type formula that only rounds up to a max number.

    Hi,

    Just to be clear then: you'll always err on the side of over-shipping if you've got enough in stock?

    So if your Need to ship was 97, for example, with a Skid Amount of 48, and assuming you had enough in stock, you'd have to ship out 3 x 48 = 144 (and couldn't get away with being short by 1 with a delivery of 96)?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    07-18-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: I need a mround type formula that only rounds up to a max number.

    Quote Originally Posted by Rlong1818 View Post
    Thank you M1234.
    No problem.

    Do you have an example of any more than just the 48 and 96 records so we can check if it works for more?

  6. #6
    Registered User
    Join Date
    07-26-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need a mround type formula that only rounds up to a max number.

    Good point. Is there a suggested formula to prevent shipping 47 extra rolls?

  7. #7
    Registered User
    Join Date
    07-26-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need a mround type formula that only rounds up to a max number.

    Th frmual does not work for all scenarios.....for examle if i have 47 in stock it tells me I can ship 48 (skid quantity). If it's not in stock, I can't ship it.
    Attached Files Attached Files

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: I need a mround type formula that only rounds up to a max number.

    "Good point. Is there a suggested formula to prevent shipping 47 extra rolls?"

    But that's my question - what's your company policy on this issue? You say they have to go out in Skids of a certain quantity (e.g. 48), but there may arise cases where you have a choice between a delivery with an extra 47 or 1 less.

    I presume you have to go with the extra 47, since the alternative will not leave the customer happy. Or perhaps you have a restriction on the actual order quantity, i.e. orders cannot be places which are not a multiple (whole, half, quarter, etc.) of the given skid for that product?

    Either way, for this to be written into the formula, you need to decide what should happen in these cases.

    Regards

  9. #9
    Registered User
    Join Date
    07-26-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need a mround type formula that only rounds up to a max number.

    We do have to go with the extra 47 to keep the customer happy. I look forward to seeing your suggested formula.

  10. #10
    Registered User
    Join Date
    07-18-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: I need a mround type formula that only rounds up to a max number.

    Agreed. I don't fully understand what your policy is

  11. #11
    Registered User
    Join Date
    07-26-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need a mround type formula that only rounds up to a max number.

    we must ship the maximum amount of inventory we can in skid quantities. However we can not exceed what is on hand.

  12. #12
    Registered User
    Join Date
    07-26-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need a mround type formula that only rounds up to a max number.

    I think this may be the way to go, unless you think of something better.....

    =ROUNDDOWN((H8/F8),0)*F8

  13. #13
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: I need a mround type formula that only rounds up to a max number.

    And what's your policy if you don't have enough in stock? Do you just ship out what you've got (even if it's way less than what the customer ordered), or not ship anything?

    Regards

  14. #14
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: I need a mround type formula that only rounds up to a max number.

    "I think this may be the way to go, unless you think of something better.....

    =ROUNDDOWN((H8/F8),0)*F8
    "

    That doesn't work. Check it with a Skid of 48, Need to ship of 90 and In Stock of 95.

    Regards

  15. #15
    Registered User
    Join Date
    07-18-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: I need a mround type formula that only rounds up to a max number.

    Is the skid amount a fixed quantity? Will it always remain at 48?

  16. #16
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: I need a mround type formula that only rounds up to a max number.

    Presuming that, in cases where you don't have enough stock to meet the order, you ship the highest multiple of the Skid value possible which is less than or equal to that which you have in stock:

    =MIN(ROUNDUP(G8/F8,0)*F8,ROUNDDOWN(H8/F8,0)*F8)

    Regards

  17. #17
    Registered User
    Join Date
    07-26-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need a mround type formula that only rounds up to a max number.

    ***And what's your policy if you don't have enough in stock? Do you just ship out what you've got (even if it's way less than what the customer ordered), or not ship anything?***

    We ship what we have, only if it is a full skid quantity. There are some exceptions but that does not need to be into the formula. Skid wquantities will fluctuate, they will not always be 48

  18. #18
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: I need a mround type formula that only rounds up to a max number.

    Did you try my last formula?

  19. #19
    Registered User
    Join Date
    07-26-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need a mround type formula that only rounds up to a max number.

    skid qty can fluctuate.

  20. #20
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: I need a mround type formula that only rounds up to a max number.

    "skid qty can fluctuate."

    My formula doesn't use a fixed Skid Quantity. It references the cell in which the Skid Quantity is held, so it adjusts to whatever value you put into that cell.

    Have you tried the formula I posted? What results did you get that didn't meet your expectations?

    Regards

  21. #21
    Registered User
    Join Date
    07-26-2012
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: I need a mround type formula that only rounds up to a max number.

    Your last formula works. THANK YOU VERY MUCH!!!!

  22. #22
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: I need a mround type formula that only rounds up to a max number.

    Ah, it seems our last posts crossed!

    Anyway, you're welcome!

+ 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. [SOLVED] I need a formula that rounds up the date.
    By VivatMartin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 08:44 AM
  2. MROUND formula
    By SaschaB in forum Excel General
    Replies: 2
    Last Post: 10-20-2011, 10:43 AM
  3. Number in cell rounds up
    By jswarb001 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2007, 03:07 PM
  4. MROUND on a negative number.
    By Big Rick in forum Excel General
    Replies: 6
    Last Post: 12-29-2005, 11:50 AM
  5. I need a %age increase formula that rounds up to the nearest $10
    By Chopper_Haynes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-04-2005, 02:05 PM

Tags for this Thread

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