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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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)
 
Upvote 0
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!
 
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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
Back
Top