formatting

[Guest]

I have a column that looks up a value (ex 4.34%) I have a column that has a
value of 10.70%. I want the change the color of the cell if the variance
between the cells is more than 3%. I have tried conditional formatting and I
am either doing it wrong for it does not work. Can anyone give me an idea on
how to accomplish this. Thank you in advance for any help you can give me.



thank you mac

 

[Ronald Dodge]

Sure thing.

Try setting it to Cell Value is:

and for the result, use an If, Then, Else function like the following:

If column I and Column J are the 2 columns being compared to each other, and
you are putting this conditional format on Cell K5, then setup the formula
within the Result box as:

=IF(ABS(I5-J5)>0.03,K5,"A")

After you put in the formula, get out of the format conditional format, then
go back into it and make sure it didn't put in any extra double quotes. If
it did, remove the double quotes and it should be fine the second time
around.

Note, this is assuming K5 will never equal the value of "A". If this is
false, then change it to a value that K5 will never equal to.

I have used this trick for numerous things, which I discovered on my own.
Too bad I can't use this same sore of trick within chart series as that
allows only for absolute referencing without necessarily having to use VBA.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000