Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

# Create an IF formula

### #1 kill_hXc Posted 28 October 2008 - 08:46 AM

kill_hXc

Member

• Member
• 103 posts
Hi,

The title says it all really.

I need to know how to make one cell say either 'Yes' if 3 other cells say yes or 'No' if they dont.

i.e.

If A1, B1 and C1 says 'Yes' (selected from a drop down list) I want D1 to say 'Yes' and be coloured green.

If A1, B1 and C1 says 'No' (selected from drop down list) I want D1 to say 'No' and be coloured Red.

Any help will be greatly appreciated.

Tom.
• 0

### #2 dsenette Posted 28 October 2008 - 09:20 AM

dsenette

Je suis Napoléon!

• 26,023 posts
what you would want to do to get the color option is place conditional formatting on the D column that says if the cell is equal to "Yes" make it green and if it's "no" make it red

the other part is a bit funkier...

this formula
`=IF(AND(A1="yes",B1="yes",C1="yes"),"YES","NO")`
will make the cells in column D (assuming that's where you put this formula) say "YES" if and ONLY if A1,B1, and C1 all say yes....any other condition (i.e. yes no yes, no no yes, no no no, etc..) will result in a NO...it's probably possible to much around with that and see what you could do...there are ways to do a nested if etc... that might get it...i'll play around with it
• 0

### #3 kill_hXc Posted 28 October 2008 - 10:06 AM

kill_hXc

Member

• Topic Starter
• Member
• 103 posts
Fantastic!

Thankyou so much.

I was messing around with things like but not quite that!

You can close this thread now if you want.
• 0

### #4 Jonesey Posted 28 October 2008 - 10:50 AM

Jonesey

Member

• Member
• 335 posts
That's definitely the way I'd do it desenette.

2 things to bear in mind:

1 - The 'Yes's' and 'No's' in the formula aren't case senstivie, so any combination of YES, Yes yes etc will work - you don't have to be picky about spelling.

2 - Conditional formatting is VERY resource intensive and If you have a large number of rows, might well lock up the spreadsheet.

Just my 2 cents.
• 0

### #5 dsenette Posted 28 October 2008 - 11:30 AM

dsenette

Je suis Napoléon!

• 26,023 posts

2 - Conditional formatting is VERY resource intensive and If you have a large number of rows, might well lock up the spreadsheet.

indeed...but it's the only way i know of to change the color of a cell based on criteria..

Edit:also...i attempted a nested if statement that basically said "if a,b, and c are yes make d yes....but if a,b, and c are no make d no, else make d no regardless" but it didn't work .
• 0

### #6 Jonesey Posted 28 October 2008 - 01:17 PM

Jonesey

Member

• Member
• 335 posts

2 - Conditional formatting is VERY resource intensive and If you have a large number of rows, might well lock up the spreadsheet.

indeed...but it's the only way i know of to change the color of a cell based on criteria..

Edit:also...i attempted a nested if statement that basically said "if a,b, and c are yes make d yes....but if a,b, and c are no make d no, else make d no regardless" but it didn't work .

Yep - the only other way would be via a macro which obviously has to be run and wouldn't work on the fly.
• 0

### Similar Topics

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users