Excel, how to change cell color based on an IF statement and not conditional formatting?
I'm using a formula to make decisions. I want to color a cell, a particular color, based on the decision of the formula. The problem is that the value of the cell does not reflect any pertinent information.
For example, i want to fill a bag with 10lb of oranges without going over. I have a number of bins filled with oranges of known weight. The formula then check the total weight against what is in the bin to see if it would exceed the weight.
bins; 7lb, 5lb, 2lb, 1lb.
Using these bins and an example; the first bin is 7lb which is less than 10lb and is added to the bag. Since 7lb is added to the bag the cell reflects the total weight in the bag and needs to be colored. The second bin is 5lb and if added to the bag would exceed 10lb so it not added. The cell continues to reflect 7lb because nothing was added and no color is needed. The third bin is 2lb and when added to the bag would not exceed 10lb. The cell then reflects the total in the bag of 9lb and needs colored. Finally the last cell is 1lb and if added to the bag would not exceed 10lb. The cell then reflects the total in the bag of 10lb and needs to be colored.
Here is an example of what i mean. The colomn on the left show what bins are available. The following colomns show different desired combonations. The cell that corresponds to the bin that is added is colored green.
Conditional formatting won't work because the cells don't show pertinent information that can be compared to something. The formula that made the decision on whether to add to the total or not should make the decision.
Is there a way to color the cell depending on what the IF statement decided?
21 Answer
There is a couple of things you are trying to solve. So first up is you need a running horizontal total. I will assume that these are in a,b,c,d,e columns and there will be never more than 4 bags. I assume A will hold the bag size (10 in your example). I assume row 1 contains header names and data starts on row 2. This is an example only, and you may need to tweak as needed for your actual work.
Step 1 make a if formula that resolves to true or false for each cell. True means it is in the bag, false is not in. The name to the left is the name that will be used in future formula's. The name manager is on the formula ribbon and allows giving names to formulas.
- BIN=if($b2>$a2,false,true)
- CIN=if($c2>$a2-(BIN*$b2),false,true)
- DIN=if($d2>$a2-(BIN*$b2)-(CIN*$C2),false,true)
- EIN=if($d2>$a2-(BIN*$b2)-(CIN*$C2)-(DIN*$D2),false,true)
To use these formulas, go into conditional formatting and select use a formula, then for the formula for the b column use =Bin, for the C column use =Cin, etc.
