IF Functions in conditional formats

marello

New Member
Joined
Nov 15, 2005
Messages
13
I don't really understand IF functions (or Excel) in any great depth! At the moment I'm trying something that I think is a bit above my head and I'd love it if someone could help me and explain why you do what you do.

I have 3 colums (A,B+C), each with about 1,500 values in ranging from 0 to 11. the 5th colum in the row display a value based on a formula. I'm trying to do a conditional format so if two of the first colums have a 0 in then the answer is a certain colour, if there's only one another, and if there aren't any zero's a final colour.

So i'm trying to put an IF formula into my conditional format box.

The formula for colum number 5 is:
=SUMPRODUCT($A$1:$C$1,A205:C205)

So I think I want to write something along the lines of:

=IF($A=0$B=0, and then have my true value set to the colour, say blue.

But how do I write this properly, I really dont understand!

Also as a side note, if I had an IF function, what would i need to write in the true value space to get the cell just to display the result of a formula?

Thank you very much for your help.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
With Conditional Ifs, you don't actually need to place an IF; it is understand by the nature of the function.


So you would insert the formula =And(A1=0,B1=0) and choose Format

for either condition, use =OR(A1=0,B1=0)

and for none, =AND(A1<>0,B1<>0)
 

marello

New Member
Joined
Nov 15, 2005
Messages
13
Thank you NBVC each of those formulas is what I'm after.

However if you're only allowed 3 conditional formats per colum I need to condense them down? The conditional format is based on 3 colums, so for example

=OR(A1=0,B1=0) works great, but I also have to put in =OR(B1=0,C1=0) and =OR(A1=0,C1=0), is there any way of combining the 3 so that I could have one conditional format to say "if any two colums contain zeros colour the answer blue" so that I could then go on to say "i only one colum has a zero make it green" and then "if none of them have a zero make it red"?

Thanks!
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
I think this is what you are after, then:

=Or(And(A1=0,B1=0),and(B1=0,C1=0),and(A1=0,C1=0))
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

Try

=COUNTIF(A1:C1,0)=2 for blue

=COUNTIF(A1:C1,0)=1 for green etc.....
 

marello

New Member
Joined
Nov 15, 2005
Messages
13
Out of curiosity how would I write a formula that says:

if there are two zeros in colums A to C copy the value in colum E into a new cell?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,995
Messages
5,834,777
Members
430,321
Latest member
yemisimi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top