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

1. ## 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.

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

Try this:

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

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

Thank you M1234.

4. ## 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

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

Originally Posted by Rlong1818
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. ## 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. ## 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.

8. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: I need a mround type formula that only rounds up to a max number.

Did you try my last formula?

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

skid qty can fluctuate.

20. ## 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. ## Re: I need a mround type formula that only rounds up to a max number.

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

22. ## 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!

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