If(And formula yields three values - shouldn't it be two?

[pam5cashfan]

First time posting...advanced beginner level skills. Currently working on data set of 5000 lines/month and I'll need to aggregate at least 4 months in each file.

Here's what i'm trying to flag with as simple a formula as possible:

If there is no change in column A in consecutive rows, and there is a change in column B in consecutive rows - that's what I want to know.

The data in these columns is in text.

I'm getting three outputs - FALSE, TRUE, and what I thought I had proposed as value-if-true "Switch"."Switch" appears when the first condition has not been met (A2<>A3), but I want it to appear when both conditions have been met (A2=A3, B2<>B3)

How can three outputs exist? Should I be using nested If's instead of If(And ?

Thanks.

 

[AhoyNC]

Maybe use OR instead of AND?

Excel Workbook
	A	B	C
1	Test1	M1
2	Test1	M1
3	Test1	M1
4	Test2	M2	Switch
5	Test3	M2	Switch
6	Test4	M4	Switch
7	Test4	M4
Sheet

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup>

L 4

 

[Fluff]

My take on this

Excel 2013/2016
	A	B	C
1	Test1	M1
2	Test1	M1
3	Test1	M2	switch
4	Test2	M2
5	Test3	M2
6	Test4	M4
7	Test4	M4
8	Test4	M5	switch
9	Test4	M4	switch
10	Test4	M4
B

Cell Formulas
Range	Formula
C2	=IF(AND(A2=A1,B2<>B1),"switch","")

 

[pam5cashfan]

Thank you!
The change to IF(OR eliminated the TRUE result, which was interesting.
Now that I have the MrExcel HTML Generator, it'll be way easier to elaborate on my question.

I want to flag only those rows (as "Switch") where A1=A2 but B changes. I don't at this moment need to know when A changes.

Like this

Book1
	A	B	C	D
1	Row	UniqueID	Color	DesiredOutput
2	1	TEMP001	Blue
3	2	TEMP001	Blue
4	3	TEMP001	Green	Switch
5	4	TEMP002	Yellow
6	5	TEMP002	Yellow
7	6	TEMP003	Yellow
8	7	TEMP003	Red	Switch
9	8	TEMP003	Green	Switch
10	9	TEMP003	Red	Switch
Sheet1

Thanks.

 

[Fluff]

In B3 copied down
=IF(AND(A3=A2,B3<>B2),"switch","")

 

[pam5cashfan]

Works, thank you very much Fluff. I had used parentheses around both conditions - Aha!

 

[Fluff]

You're welcome & thanks for the feedback