# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Conditional Formatting with If Statement

## Merlin54k

I have a spreadsheet with budget figures and I am trying to use conditional formatting to show increases from one month to another by a certain percentage.

Cell H26 has a July amount.
Cell I26  has a Aug amount.

Using conditional formatting, I want to show when the expensed amount in August (I26) is greater than July (H26) by 80% turning it red with conditional formatting. If Aug is less than July by %80 it should do nothing. 



=IF($I26-$H26>0,$I26-H26/I26>0.8,0)

----------


## Paul

Hi Merlin,

You can do this a couple ways using conditional formatting as shown below.  Also, when using conditional formatting, you're already using an IF (hence the "conditional"), so it's usually not necessary to have an IF within the formula.

1. Using "Cell Value Is"
 a. In C.F. window, choose 'Cell Value Is'
 b. Then choose 'Greater than or equal to'
 c. In the textbox, type:  =H26*1.8
 d. Set a format, click OK

2. Using "Formula Is"
 a. In C.F. window, choose 'Formula Is'
 b. In formula textbox, type: =I26>=H26*1.8
 c. Set a format, click OK

If nothing happens, go back into conditional formatting and check to see if Excel put quotation marks around your formulas.  If so, remove them.

----------


## Merlin54k

Thank you so much for your assistance.

What can I do when K21 is zero? Multplying it by J21*1.8 would format a cell in red as per the conditional formatting when it shouldn't.

Cell J21 has $500
Cell K21 has $0


=IF(K21>J21,(IF(J21>0,K21>=J21*1.8)))

----------


## cdnguy68

I'm trying to do something similar, but I'm not quite getting what I want.

I have a series of results from an employee survey.

B30 has the question, E30 has the percentage of "I agree" responses as 38%.  F30 has a departmental percentage.  This department had a 15% "I agree" response.  Because the difference between these two percentages is greater than 10, I want that text in red. 

So the logic of what I am trying to do is as follows:

IF _department response_ is lower than _company response_ by 10 percentage points or more, Then make that cell *RED*
ORIF _departmental response_ is HIGHER than _company response_by 10 percentage points or more, then make that cell *green*.  
ELSE, leave it alone.

THEN I need to copy that format to other similar results.

Does that make sense?

I've included an example of what I am working on.  I've changed some of the values, but the formula I need is as stated above.

----------


## taz1227

I am working on a spreadsheet where I am doing conditional formatting. Example: I have a value in A1 and a value in A5. I want A1 to have the conditional formatting comparing it to A5. I want to copy that conditional formula to B1 and have it compare to B5 not A5. And then C1 compared to C5. How do I make the conditional formatting a "series" if that is the correct term? I have tried EVERYTHING, I think and it keep comparing the first value only. A1 to A5 then B1 back to A5 then C1 back to A5. Is that understandable? Thank you!

----------


## ChemistB

Taz
If you highlight all the columns when entering the formula and enter it for the first column (A1>A5), the rest will all follow suit.  If you already have it formatted, copy and Paste Special >Formats and it will copy for B, C, etc.  UNLESS you have anchored it using $ signs.  If so, remove those first.

----------


## funkel13

I am trying to complete one formula to complete a few things.  Many lab results numbers I get in are with < symbols and excel does not recognize them as numbers.  The formula I have so far is
=+if(or(g6="<.03",g6="0.03"),0.03,g6
This formula works, but in addition to this I want to have it so that any numerical value that is over 0.2 would be populated in a red font.  Is this possible, and how?

----------


## ChemistB

Funkel,
Are any values greater than 0.03 presented as numbers, at least in appearance?  In other words, will they have < or > symbols in them also?   If they are all presented as numbers but in text format, you can modify your formula to 
=+if(or(g6="<.03",g6="0.03"),0.03,g6+0)
Then you can apply routine conditional formatting constraints on it.  Does that work for you?

----------


## madie

Help? Please.  :Smilie: 
So I'm trying to make this work, and I've gotten some of it, but I can't seem to figure this bit out:
In my spreadsheet, I need to say that if a cell in column V is less than or equal to 0, that the corresponding cells in Columns C, D, E, and F, will be highlighted in bright yellow, but if the cell in column V is greater than 0, that the corresponding cells in C, D, E, and F stay the same with their current formatting - all of column V already has its own conditional format, and if I try to select that column or cells in that column as a reference, it tries to alter the existing formatting, which I need to leave as-is. I've looked online and can't seem to find what it is I'm trying to do in a tutorial or anything. Any help would be greatly appreciated and TIA.
btw, I'm on Excel 2002
So I need if V3 is less than or equal to 0, then C3, D3, E3, F3 are yellow (and so on).
-madie  :Smilie:

----------


## Paul

Madie,

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

----------

