Jump to content

Welcome to Geeks to Go - Register now for FREE

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
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!

  • Administrator
  • 26,019 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!

  • Administrator
  • 26,019 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