Excel formula to return value based on certain criteria

[Ekguy41]

Hello,

I am using excel 2016 and trying to create ta formula that will return a certain value based on multiple criteria. I have set out the logic below but everything I try doesn’t work

if(and(a1=“Green”,a2<>”-“),”Good”,”Bad”
if(and(a1=“Amber”,a2<>”-“),”Good”,”Bad”
if(and(a1=“Red”,a2 <>”-“),,”Good”,”Bad”
if(and(a1=“-“,”-“),”Bad”
if(and(a1=“-“,a2<>”-“),”Bad”

any help or a better way to write the formula would be greatly appreciated. Been at this one for a couple of days.

 

[Ekguy41]

Hello,

I am using excel 2016 and trying to create ta formula that will return a certain value based on multiple criteria. I have set out the logic below but everything I try doesn’t work

if(and(a1=“Green”,a2<>”-“),”Good”,”Bad”
if(and(a1=“Amber”,a2<>”-“),”Good”,”Bad”
if(and(a1=“Red”,a2 <>”-“),,”Good”,”Bad”
if(and(a1=“-“,”-“),”Bad”
if(and(a1=“-“,a2<>”-“),”Bad”

any help or a better way to write the formula would be greatly appreciated. Been at this one for a couple of days.

Last two lines should read a2=“-“ and a2<>”-“

 

[Fluff]

I am using excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=IF(A2="-","bad",IF(OR(A1={"Green","Amber","Red"}),"good","bad"))

 

[Ekguy41]

Thanks for your response. I have updated my profile.

the formula is nearly there I just need to one more part that says if a1 = green and a2 =“-“ then good

 

[Fluff]

How about

=IF(A2="Green","good",IF(A2="-","bad",IF(OR(A1={"Amber","Red"}),"good","bad")))

 

[Ekguy41]

Thank you it’s near perfect. Only issue is if a1=green and a2 <> - the that should be bad and if a1=- and a2 <> - the that should be bad as well. Everything else works perfectly

 

[Fluff]

Only issue is if a1=green and a2 <> - the that should be bad

That's not what you said originally

if(and(a1=“Green”,a2<>”-“),”Good”,”Bad”

 

[Ekguy41]

Apologies, I have listed the correct scenario below
if a1 = green and a2 = - then good
if a1 = green and a2 <> - then bad
if a1 = amber and a2 = - then bad
if a1 = amber and a2 <> - then good
if a1 = red and a2 = - then bad
if a1 = red and a2 <> - then good
if a1 = - and a2 = - then bad
if a1 = - and a2 <> - then bad

 

[Fluff]

Ok, how about

=IF(A2="-",IF(A1="green","good","bad"),IF(OR(A1={"amber","red"}),"good","bad"))

 

[Ekguy41]

Thank you so much!!! It works perfectly. I really do appreciate you giving me your time and help.