jessek12:
msulaman:
Hello Scott, thanks so much for all your contributions to this forum, much appreciated.
Is there a way to do conditional formatting in Excel so that the appearance of one cell (or a group of cells) depends on the value of another cell not in this group?
In other words can I say if the value of cell B2 is negative, then make cells C3 to C6 blue, or something like that.
Next question: Can I do conditional math with Excel? Like if B2 is greater than 5, then use one calculation for cell C3 and if the value is less than 5, use another calculation? As far as I know there is no way to do this.
Thanks in advance for your help.
Yes to both.
#1 - Highlight the cells you want to be formatted. Under the Conditional Formatting menu, you'll see "New Formatting Rule". In that menu, the last option is "Use a formula". Just create the formula, in your case "B2<0", and set the format. Should work. You may need an absolute reference to the cell, so use $B$2 in your example.
#2 - Not conditional math like the example above, but using a formula, specifically an IF statement. For the example you gave: Click in cell C3 and type: =IF(b2>5,(calculation when true),(calculation when false))
Unsure if I'm reading query #2 correctly but I'd just like to add that the IF function can be used on numerous occasions within the same cell.
e.g.
Awarding the correct points to a specific team's total in a football (soccer) match.
3 pts for a win. 1 point for a draw. 0 points for a loss
Cell A1 (home team) Cell B1 (away team)
=IF(A1>B1,3,IF(A1=B1,1,0))
In the above if Cell A1 is greater than Cell B1 then 3 points are awarded.
If Cell A1 is not greater than Cell B1 then the 2nd IF 'kicks in'.