Min Max Functions on Weight Totals Warning

ajstole

New Member
Joined
Apr 3, 2018
Messages
8
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.

PUDELTOTAL
68,000068,000
64,0007,00071,000
60,00014,00074,000
56,00021,00077,000
52,00028,00080,000
48,00035,00083,000
44,00042,00086,000
40,00049,00089,000
36,00056,00092,000
32,00063,00095,000
28,00070,00098,000
24,00077,000101,000
20,00084,000104,000
16,00091,000107,000
12,00098,000110,000
8,000105,000113,000
4,000112,000116,000
0119,000119,000

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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,452
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
Joined
Sep 18, 2013
Messages
5,001
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 18, 2013
Messages
5,001
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 3, 2018
Messages
8
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!

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>


</b3,"no>
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
5,001
Office Version
  1. 365
Platform
  1. Windows
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:

Forum statistics

Threads
1,176,287
Messages
5,902,328
Members
434,962
Latest member
sgilmoreBBP

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top