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

Pickup	Delivery	TOTAL
68,000	0	68,000
64,000	6,000	70,000
60,000	12,000	72,000
56,000	18,000	74,000
52,000	24,000	76,000
48,000	30,000	78,000
44,000	36,000	80,000
40,000	42,000	82,000
36,000	48,000	84,000
32,000	54,000	86,000
28,000	60,000	88,000
24,000	66,000	90,000
20,000	72,000	92,000
16,000	78,000	94,000
12,000	84,000	96,000
8,000	90,000	98,000
4,000	96,000	100,000
0	102,000	102,000

<colgroup><col width="67" span="3" style="width:50pt"> </colgroup><tbody>
</tbody>

 

[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.