Jump to content

Welcome to Geeks to Go - Register now for FREE

Need help with your computer or device? Want to learn new tech skills? You're in the right place!
Geeks to Go is a friendly community of tech experts who can solve any problem you have. Just create a free account and post your question. Our volunteers will reply quickly and guide you through the steps. Don't let tech troubles stop you. Join Geeks to Go now and get the support you need!

How it Works Create Account
Photo

Create an IF formula


  • Please log in to reply

#1
kill_hXc

kill_hXc

    Member

  • Member
  • PipPipPip
  • 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.

Thanks in advance,

Tom.
  • 0

Advertisements


#2
dsenette

dsenette

    Je suis Napoléon!

  • Community Leader
  • 26,047 posts
  • MVP
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

kill_hXc

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 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

Jonesey

    Member

  • Member
  • PipPipPip
  • 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

dsenette

    Je suis Napoléon!

  • Community Leader
  • 26,047 posts
  • MVP

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

Jonesey

    Member

  • Member
  • PipPipPip
  • 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

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP