Struggling with an If, and, or, formula

[ajt8888]

I have what I though was a fairly simple problem but I cant work it out.

I have two columns

Col1 contains either a Yes or No

Col2 contains a score correct to 1 decimal place

What I need in one formula for a 3rd column is a formula as follows:

If Col1 = Yes and Col2 is Greater than 36.8 then Green if not then Red or if Col1 = No and Col2 greater than 42.3 then Green or if Col2 is between 35.6 and 42.2 then Amber or if Col2 is less than 35.5 then Red

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I need a formula If and or
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[ajt8888]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I need a formula If and or
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies but because I hadn't had a reply i thought id try on here

 

[Fluff]

You still need to let us know.

How about

=IF(A2="Yes",IF(B2>36.8,"Green","Red"),IF(A2="No",IF(B2>42.3,"Green",IF(B2>35.5,"Amber","Red")),""))

 

[ajt8888]

Thanks for trying but it doesn't work properly I have the same scores coming out with different results. I will have to think of some other way of doing this. It may just be easier to buy a database.

 

[Fluff]

In what way doesn't it work?

Can you post some sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ajt8888]

Its ok it was my error thanks very much for that. Can't see a "Mark as solution" button but its worked so great.

 

[Fluff]

You're welcome & thanks for the feedback.

Can't see a "Mark as solution" button

It's the Tick Mark to the right of the post that helped you the most.