Display 1 or 0 based on multiple conditions. If one is accomplished, stop the second one from running.

[Merenguito]

Hey guys

I have another problem if you could please help me, thank you. I have this setup:

I want to display 1 in column M for the first item that has the condition Yes, column K is a drop down with Yes and No. I don't know how to stop the second formula to run if the first one is accomplished. I use this now:
=IF((AND(K1="Yes", K2="No")), "1", "0") and =IF((AND(K2="Yes", K1="No")), "1", "0")

When I pick Yes, M turns into 1, but if I pick Yes for the second one, both turn into 0 again. I'd like to keep the first 1, and all other duplicate items that get Yes to remain 0. Is it possible without scripts?

Thank you

 

[Merenguito]

Solved with circular references After you get the hang of it Excel starts to look for logical. Cheers!

 

[jtakw]

Hi,

Don't know what you mean by

Solved with circular references

Anyway, are you saying you want the answer as 1 (One), if Either K1 or K2 is "Yes" ?

 

[Merenguito]

A circular reference in an Excel 2013 formula is one that depends, directly or indirectly, on its own value. The most common type of circular reference occurs when you mistakenly refer in the formula to the cell in which you're building the formula itself. - https://www.dummies.com/software/microsoft-office/excel/circular-references-in-excel-2013/

Iterative calculations - Options - Formulas - Right side

I solved my problems with something like this:
=IF(C5="No",0,IF(M6="1", "0", IF(C5="Yes", "1", "0")))
=IF(C6="No",0,IF(M5="1", "0", IF(C6="Yes", "1", "0")))

The conditions are interdependent. If I have multiple duplicates, I use another step in the middle, an OR like OR(M5=1, M12=1, M15=1). What I did not know and made me loose 1 hour is that in OR you don't use " because the conditions will not work.
Multiple duplicates: =IF(C17="No",0,IF(OR(M20=1, M23=1, M25=1, M27=1), "0", IF(C17="Yes", "1", "0")))

 

[jtakw]

I know what "circular reference" is.

What I don't understand is what you're trying to accomplish.

So the data is in C column and your result cell is in corresponding M column?

If you can explain what's in what cell, and what is M supposed to do.

And why are you using quote marks around 1 and 0 ??

Edit: 1 is Not the same as "1", if you are to compare the two values, they Won't match.

 

[Merenguito]

I used it without quotes and it didn't work, that's why I use ". Thank you, but I already said in my second post that I solved my problem. No need to further develop this Cheers, have a happy new year!

 

[jtakw]

It didn't work without quotes is because your formula result gave it Quotes.

=IF(C5="No",0,IF(M6="1", "0", IF(C5="Yes", "1", "0")))
=IF(C6="No",0,IF(M5="1", "0", IF(C6="Yes", "1", "0")))

Then, you were trying to compare 1 to "1"
and 0 to "0"

I think this will do what you need, try to stay Away from circular referencing if at all possible:

Book1
	C	M
5	Yes	1
6	Yes	0
Sheet438

Cell Formulas
Range	Formula
M5	=IF(C5="Yes",1,0)
M6	=IF(AND(C6="Yes",M5=0),1,0)

Play around with the Yes and No in C5 and C6.

 

[jtakw]

And, if you have a long list of Yes and No in column C, and want column M to show a 1 (one) Only for the first occurrence of Yes, then use these:

Book1
	C	M
5	No	0
6	No	0
7	Yes	1
8	Yes	0
9	No	0
Sheet438

Cell Formulas
Range	Formula
M5	=IF(C5="Yes",1,0)
M6	=IF(AND(C6="Yes",COUNTIF(M$5:M5,1)=0),1,0)

M6 formula copied down as far as you need.

 

[Rick Rothstein]

Hey guys

I have another problem if you could please help me, thank you. I have this setup:

I want to display 1 in column M for the first item that has the condition Yes, column K is a drop down with Yes and No. I don't know how to stop the second formula to run if the first one is accomplished. I use this now:
=IF((AND(K1="Yes", K2="No")), "1", "0") and =IF((AND(K2="Yes", K1="No")), "1", "0")

If I understand your question correctly, this formula (without circular references) should do what you want...

=IF(AND(K1="Yes",COUNTIF(K$1:K1,"Yes")=1),1,0)

 

[jtakw]

And, you can do away with the IF altogether by using "double unary", same formulas as my Post # 8:

Book1
	C	M
5	No	0
6	No	0
7	Yes	1
8	Yes	0
9	No	0
Sheet438

Cell Formulas
Range	Formula
M5	=--(C5="Yes")
M6	=--(AND(C6="Yes",COUNTIF(N$5:N5,1)=0))