Min Max Functions on Weight Totals Warning

A

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.

[TABLE="width: 276"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]PU[/TD]
[TD]DEL[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD="align: right"]68,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]68,000[/TD]
[/TR]
[TR]
[TD="align: right"]64,000[/TD]
[TD="align: right"]7,000[/TD]
[TD="align: right"]71,000[/TD]
[/TR]
[TR]
[TD="align: right"]60,000[/TD]
[TD="align: right"]14,000[/TD]
[TD="align: right"]74,000[/TD]
[/TR]
[TR]
[TD="align: right"]56,000[/TD]
[TD="align: right"]21,000[/TD]
[TD="align: right"]77,000[/TD]
[/TR]
[TR]
[TD="align: right"]52,000[/TD]
[TD="align: right"]28,000[/TD]
[TD="align: right"]80,000[/TD]
[/TR]
[TR]
[TD="align: right"]48,000[/TD]
[TD="align: right"]35,000[/TD]
[TD="align: right"]83,000[/TD]
[/TR]
[TR]
[TD="align: right"]44,000[/TD]
[TD="align: right"]42,000[/TD]
[TD="align: right"]86,000[/TD]
[/TR]
[TR]
[TD="align: right"]40,000[/TD]
[TD="align: right"]49,000[/TD]
[TD="align: right"]89,000[/TD]
[/TR]
[TR]
[TD="align: right"]36,000[/TD]
[TD="align: right"]56,000[/TD]
[TD="align: right"]92,000[/TD]
[/TR]
[TR]
[TD="align: right"]32,000[/TD]
[TD="align: right"]63,000[/TD]
[TD="align: right"]95,000[/TD]
[/TR]
[TR]
[TD="align: right"]28,000[/TD]
[TD="align: right"]70,000[/TD]
[TD="align: right"]98,000[/TD]
[/TR]
[TR]
[TD="align: right"]24,000[/TD]
[TD="align: right"]77,000[/TD]
[TD="align: right"]101,000[/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]84,000[/TD]
[TD="align: right"]104,000[/TD]
[/TR]
[TR]
[TD="align: right"]16,000[/TD]
[TD="align: right"]91,000[/TD]
[TD="align: right"]107,000[/TD]
[/TR]
[TR]
[TD="align: right"]12,000[/TD]
[TD="align: right"]98,000[/TD]
[TD="align: right"]110,000[/TD]
[/TR]
[TR]
[TD="align: right"]8,000[/TD]
[TD="align: right"]105,000[/TD]
[TD="align: right"]113,000[/TD]
[/TR]
[TR]
[TD="align: right"]4,000[/TD]
[TD="align: right"]112,000[/TD]
[TD="align: right"]116,000[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]119,000[/TD]
[TD="align: right"]119,000[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
ajstole said:
If the cap is reached, on either side, I need a warning,
Click to expand...

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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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!

StephenCrump said:
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
Actual
Rounded up?
Extra room
Actual
Rounded up?
Extra room
Actual
Rounded up?
Extra room
Actual
Rounded up?
Extra room

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Pickup[/TD]
[TD="align: right"]Delivery[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50,000[/TD]
[TD="align: right"]15,000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]52,000[/TD]
[TD="align: right"]21,000[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]4,000[/TD]
[TD="align: right"]7,000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Pickup[/TD]
[TD="align: right"]Delivery[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]25,000[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]28,000[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]40,000[/TD]
[TD="align: right"]70,000[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Pickup[/TD]
[TD="align: right"]Delivery[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]20,000[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]21,000[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]56,000[/TD]
[TD="align: right"]98,000[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Pickup[/TD]
[TD="align: right"]Delivery[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]50,000[/TD]
[TD="align: right"]40,000[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]52,000[/TD]
[TD="align: right"]42,000[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]No go![/TD]
[TD="align: right"]No go![/TD]

</tbody>


</b3,"no>
Click to expand...
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Combining IF and INDEX functions...I think
Replies
4
Views
616
ajstole
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top