Combining IF and INDEX functions...I think

[ajstole]

Unable to post attachments but numbers on bottom..

1. I have two sets up numbers
2. As one goes up, the other comes down, and vice versa
3. If they are all pickups, I need to "mark red" if they go over the max amount (68,000)
4. If they are all deliveries, I need to "mark red" if they go over the max amount (102,000)

.....So far, so good - here is the mystery to me.....

When I combine the two weights, how can I get it to "marks red" if for example...
a. Pickups = 44,000 and deliveries = 36,000 OR
b. Pickups = 16,000 and deliveries = 78,000


[TABLE="width: 201"]
<colgroup><col width="67" span="3" style="width:50pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 67"]Pickup[/TD]
[TD="class: xl64, width: 67"]Delivery[/TD]
[TD="class: xl65, width: 67"]TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]68,000[/TD]
[TD="class: xl67, width: 67"]0[/TD]
[TD="class: xl68, width: 67"]68,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]64,000[/TD]
[TD="class: xl69, width: 67"]6,000[/TD]
[TD="class: xl68, width: 67"]70,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]60,000[/TD]
[TD="class: xl69, width: 67"]12,000[/TD]
[TD="class: xl68, width: 67"]72,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]56,000[/TD]
[TD="class: xl69, width: 67"]18,000[/TD]
[TD="class: xl68, width: 67"]74,000[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 67"]52,000[/TD]
[TD="class: xl72, width: 67"]24,000[/TD]
[TD="class: xl73, width: 67"]76,000[/TD]
[/TR]
[TR]
[TD="class: xl74, width: 67"]48,000[/TD]
[TD="class: xl75, width: 67"]30,000[/TD]
[TD="class: xl76, width: 67"]78,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]44,000[/TD]
[TD="class: xl69, width: 67"]36,000[/TD]
[TD="class: xl68, width: 67"]80,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]40,000[/TD]
[TD="class: xl69, width: 67"]42,000[/TD]
[TD="class: xl68, width: 67"]82,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]36,000[/TD]
[TD="class: xl69, width: 67"]48,000[/TD]
[TD="class: xl68, width: 67"]84,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]32,000[/TD]
[TD="class: xl69, width: 67"]54,000[/TD]
[TD="class: xl68, width: 67"]86,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]28,000[/TD]
[TD="class: xl69, width: 67"]60,000[/TD]
[TD="class: xl68, width: 67"]88,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]24,000[/TD]
[TD="class: xl69, width: 67"]66,000[/TD]
[TD="class: xl68, width: 67"]90,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]20,000[/TD]
[TD="class: xl69, width: 67"]72,000[/TD]
[TD="class: xl68, width: 67"]92,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]16,000[/TD]
[TD="class: xl69, width: 67"]78,000[/TD]
[TD="class: xl68, width: 67"]94,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]12,000[/TD]
[TD="class: xl69, width: 67"]84,000[/TD]
[TD="class: xl68, width: 67"]96,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]8,000[/TD]
[TD="class: xl69, width: 67"]90,000[/TD]
[TD="class: xl68, width: 67"]98,000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 67"]4,000[/TD]
[TD="class: xl69, width: 67"]96,000[/TD]
[TD="class: xl68, width: 67"]100,000[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 67"]0[/TD]
[TD="class: xl69, width: 67"]102,000[/TD]
[TD="class: xl68, width: 67"]102,000[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Its not possible to answer your question. You have provided the rules for single events but havent provided any rules for the combinations.

 

[ajstole]

Apologies, the rules really are rounding up to the next number. For example, if I say pickup is at 5000, then round up up to the nearest 4,000 which is 8,000. At the same time, same rules, if I have a delivery set at 98,000, that rounds up to 102,000 (every 6,000), then the total would then be 110,000 ...... however, now, the total combined is over the max.

As pickups go down or up, the deliveries go in the opposite direction. Currently, there is no way to tell me if the combination of the two is over the total on the right.

 

[steve the fish]

Ok so you have this table. What are you wanting to check? Do you have a couple of cells with values in them?

 

[ajstole]

I'm not sure at this point how else to explain it. All the cells with values in them was posted. If I have 12,000 in pickups, I need excel to keep track that I have up to 84,000 that I can use on deliveries. If I have 12,000 pickups and I input 85,000, I need excel to let me know that I have exceeded my max. The same story goes with the rest of the cells.