# Min Max Functions on Weight Totals Warning

#### ajstole

##### New Member
So, I posted a few days ago, but had an invalid explanation of what I was looking for. Apologies to the community. Here is a what I was looking for.

I have these group of cells: (See Below). My problem is, if for example, I input, on another cell, pickup weight forecasted at 52,000, and Delivery weight forecasted at 7,000, then I should have up to either 64,000 lbs on PU to play with, or...up to 28,000 lbs on DEL to play with. If the cap is reached, on either side, I need a warning, I was thinking conditional format, to tell me that I have reached my capacity (TOTAL). I tried using conditional format for the TOTAL weight, however, 102,000 on Pickups is way different than 102,000 on deliveries. My guys would die on a 119,000 pickup, but not 119,000 delivery.

 PU DEL TOTAL 68,000 0 68,000 64,000 7,000 71,000 60,000 14,000 74,000 56,000 21,000 77,000 52,000 28,000 80,000 48,000 35,000 83,000 44,000 42,000 86,000 40,000 49,000 89,000 36,000 56,000 92,000 32,000 63,000 95,000 28,000 70,000 98,000 24,000 77,000 101,000 20,000 84,000 104,000 16,000 91,000 107,000 12,000 98,000 110,000 8,000 105,000 113,000 4,000 112,000 116,000 0 119,000 119,000

<colgroup><col span="3"></colgroup><tbody>
</tbody>

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### AlphaFrog

##### MrExcel MVP
If the cap is reached, on either side, I need a warning,

What caps?

I'm guessing (a big guess) you want a conditional format in column C where; the value in column A is over some yet unknown cap OR column B is over some yet unknown different cap.

If this is correct, select column C2:C20 (or whatever range you like) and put in this conditional formatting formula.
=OR(\$A2 > 100000, \$B2 > 50000)

Change the 100000 and the 50000 to whatever cap limits you want.

#### StephenCrump

##### MrExcel MVP
Based on your two posts, I think this is what you are trying to do?

B3: =CEILING(B2,4000)
C3: =CEILING(C2,7000)
B4: <b3,"no go!",(119000-c3)="" 7000*4000-b3)
=IF((119000-C3)/7000*4000 < B3,"No go!",(119000-C3)/7000*4000-B3)
C4: =IF(B4="No go!","No go!",(68000-B3)/4000*7000-C3)

Book1
ABC
1PickupDelivery
2Actual50,00015,000
3Rounded up?52,00021,000
4Extra room4,0007,000
5
6PickupDelivery
7Actual25,0000
8Rounded up?28,0000
9Extra room40,00070,000
10
11PickupDelivery
12Actual020,000
13Rounded up?021,000
14Extra room56,00098,000
15
16PickupDelivery
17Actual50,00040,000
18Rounded up?52,00042,000
19Extra roomNo go!No go!
Sheet1
</b3,"no>

Last edited:

#### StephenCrump

##### MrExcel MVP
Or perhaps:

B3: =IF((119000-CEILING(C2,7000))/7000*4000 < CEILING(B2,4000),"No go!",(119000-CEILING(C2,7000))/7000*4000-B2)
C3: =IF(B3="No go!","No go!",(68000-CEILING(B2,4000))/4000*7000-C2)

Book1
ABC
1PickupDelivery
2Actual50,00015,000
3Extra room6,00013,000
4
5PickupDelivery
6Actual25,0000
7Extra room43,00070,000
8
9PickupDelivery
10Actual020,000
11Extra room56,00099,000
12
13PickupDelivery
14Actual50,00040,000
15Extra roomNo go!No go!
Sheet1

Last edited:

#### ajstole

##### New Member
WOW! Could you possibly send both versions to me via excel? I see the outcome, just not sure what cells you are using for both scenarios!

Based on your two posts, I think this is what you are trying to do?

B3: =CEILING(B2,4000)
C3: =CEILING(C2,7000)
B4: <b3,"no go!",(119000-c3)="" 7000*4000-b3)
=IF((119000-C3)/7000*4000 < B3,"No go!",(119000-C3)/7000*4000-B3)
C4: =IF(B4="No go!","No go!",(68000-B3)/4000*7000-C3)

ABC
1PickupDelivery
2Actual50,00015,000
3Rounded up?52,00021,000
4Extra room4,0007,000
5
6PickupDelivery
7Actual25,0000
8Rounded up?28,0000
9Extra room40,00070,000
10
11PickupDelivery
12Actual020,000
13Rounded up?021,000
14Extra room56,00098,000
15
16PickupDelivery
17Actual50,00040,000
18Rounded up?52,00042,000
19Extra roomNo go!No go!

</tbody>

</b3,"no>

#### StephenCrump

##### MrExcel MVP
I think the latter of the two is probably what you're looking for:

E2: =IF((119000-CEILING(C2,7000))/7000*4000 < CEILING(B2,4000),"No go!",(119000-CEILING(C2,7000))/7000*4000-B2)
F2: =IF(E2="No go!","","OR")
G2: =IF(E2="No go!","",(68000-CEILING(B2,4000))/4000*7000-C2)

Workbook here: https://app.box.com/s/yowjz5ytjgrmz3k91n75qbyfas7cbnk0

Note that I haven't referred to your table. Instead, because it's regular, I have coded it into my formulae.

Let us know if it fits the bill, or if you need changes?

Book1
ABCDEFG
1Job/DayPickupDeliveryExtra pickup capacityExtra delivery capacity
2155,00015,0001,000OR6,000
3225,000043,000OR70,000
43020,00056,000OR99,000
5450,00040,000No go!
Sheet1

Last edited:

#### ajstole

##### New Member
Thank you so much Stephen. Made my life much easier.

#### StephenCrump

##### MrExcel MVP
That's good to hear, thanks.

Replies
4
Views
381

1,171,654
Messages
5,876,720
Members
433,207
Latest member
Bitt0101

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back