Struggling to understand IF and conditional formatting

[Guest]

Two questions:

1) I want a cell to change colour to red if it does not match the value of
another cell. I can not seem to work out how this happens.

2) I want two cells to add together but only if one of the has a value of
over 100.
For this i was thinking if would be =IF(c2>100, [=SUM(c2+c1)], [c1])
Basically I want c3 to show the value in c1 unless c2 is over 100 in which
case I want it to show the sum of the two.

 

[Guest]

Try this:

1) Conditional formatting
If cell A2 should change color when its value does not match cell B2

Select the cell (assuming cell A2)
Then....From the Excel main menu:
<format><conditional formatting>
Click the dropdown and select:
Formula is: =A2<>B2
Click the [formatting] button and set the format
Click the [OK] buttons to finish

2) Conditional summing
C3: =C1+(C2>100)*C2
or
C3: =C1+IF(C2>100,C2,0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

1.) Format/Conditional Formatting should work. If you pick red as the text
color, nothing will happen if the cell is blank, you need to make the
background red.

2.)that formula will work

 

[JE McGimpsey]

1)
Assume the two cells are A1 and B1. With cell A1 active, choose
Format/Conditional formatting and set the dropdowns and input box to

CF1: Cell Value is not equal to =B1
Format1: <patterns>/<red>

2)

C3: =C1+IF(C2>100,C2,0)

 

[Guest]

Thanks RC.

I've got the conditional summing to work however I am still struggling with
the conditional formatting. I'm using excel 2007 so the menu structure is a
little different to how you describe.

Try this:

1) Conditional formatting
If cell A2 should change color when its value does not match cell B2

Select the cell (assuming cell A2)
Then....From the Excel main menu:
<format><conditional formatting>
Click the dropdown and select:
Formula is: =A2<>B2
Click the [formatting] button and set the format
Click the [OK] buttons to finish

2) Conditional summing
C3: =C1+(C2>100)*C2
or
C3: =C1+IF(C2>100,C2,0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Two questions:

1) I want a cell to change colour to red if it does not match the value of
another cell. I can not seem to work out how this happens.

2) I want two cells to add together but only if one of the has a value of
over 100.
For this i was thinking if would be =IF(c2>100, [=SUM(c2+c1)], [c1])
Basically I want c3 to show the value in c1 unless c2 is over 100 in which
case I want it to show the sum of the two.

 

[David Biddulph]

1) Conditional formatting:
Either
Cell value: not equal to: your other cell
or
Formula is: =A1<>B1

2) =IF(C2>100,C2+C1,C1)
Excel has help facilities for all functions (with the strange exception of
DATEDIF), and includes examples. It's worth looking at this when you are
trying to use functions with which you are unfamiliar.

One minor point is that you don't need SUM and + together. Either
SUM(C2,C1) or C2+C1 will do.

 

[Gord Dibben]

Dom

1. Format>CF>Formula is: =A1<>A2

Note you can use $ signs to "fix" the cell references like =$A$1<>$A$2

2. =IF(C2>100,SUM(C2+C1),C1)


Gord Dibben MS Excel MVP