Keep from going over cell Value

I have a sheet that gives me the amount of lots I need to run in a week and the maximum amount of lots I can run in a week. My problem is, I cannot consistently keep my needed lot sizes to stay close AND not go over my maximum lots allowed.

1. I find out what I need (B2-C2)+D2 (Need to fill to Inventory MAX)
2. I multiply total need (E12) by each Item line. (Percent to Need)
3. I multiply percent against Max Lots Allowed in one Week to get "Proposed"
4. I then round each line (MROUND) to nearest lot size (Proposed Rounded to Lot)

I tried using MIN instead of MROUND and it was too far below MAX Lots allowed.

MAX Need.png
Going over Max.xlsx

Originally Posted by Bernie Deitrick

In G11, change the formula to

=B14-SUM(G2:G10)

I can use this to stay under. Thanks alot!

I there a way to make sure the number always stays close to the MAX (within a lot size or so)? If I change the stock or demand around, I can be as much as 15,000 or so from the MAX.

I figured it out. I had your formula in the sum column and not on the last item. Thank you for you help, it seems to be working great now.