Min Max Functions on Weight Totals Warning

[ajstole]

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>

 

[AlphaFrog]

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]

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
	A	B	C
1		Pickup	Delivery
2	Actual	50,000	15,000
3	Rounded up?	52,000	21,000
4	Extra room	4,000	7,000
5
6		Pickup	Delivery
7	Actual	25,000	0
8	Rounded up?	28,000	0
9	Extra room	40,000	70,000
10
11		Pickup	Delivery
12	Actual	0	20,000
13	Rounded up?	0	21,000
14	Extra room	56,000	98,000
15
16		Pickup	Delivery
17	Actual	50,000	40,000
18	Rounded up?	52,000	42,000
19	Extra room	No go!	No go!
Sheet1

</b3,"no>

 

[StephenCrump]

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
	A	B	C
1		Pickup	Delivery
2	Actual	50,000	15,000
3	Extra room	6,000	13,000
4
5		Pickup	Delivery
6	Actual	25,000	0
7	Extra room	43,000	70,000
8
9		Pickup	Delivery
10	Actual	0	20,000
11	Extra room	56,000	99,000
12
13		Pickup	Delivery
14	Actual	50,000	40,000
15	Extra room	No go!	No go!
Sheet1

 

[ajstole]

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)

	A	B	C
1		Pickup	Delivery
2	Actual	50,000	15,000
3	Rounded up?	52,000	21,000
4	Extra room	4,000	7,000
5
6		Pickup	Delivery
7	Actual	25,000	0
8	Rounded up?	28,000	0
9	Extra room	40,000	70,000
10
11		Pickup	Delivery
12	Actual	0	20,000
13	Rounded up?	0	21,000
14	Extra room	56,000	98,000
15
16		Pickup	Delivery
17	Actual	50,000	40,000
18	Rounded up?	52,000	42,000
19	Extra room	No go!	No go!

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

</b3,"no>

 

[StephenCrump]

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
	A	B	C	D	E	F	G
1	Job/Day	Pickup	Delivery		Extra pickup capacity		Extra delivery capacity
2	1	55,000	15,000		1,000	OR	6,000
3	2	25,000	0		43,000	OR	70,000
4	3	0	20,000		56,000	OR	99,000
5	4	50,000	40,000		No go!
Sheet1

 

[ajstole]

Thank you so much Stephen. Made my life much easier.

 

[StephenCrump]

That's good to hear, thanks.