If And Function?

[melbwarrior]

Hello all

Looking for some help on what will probably be a simple excel formula:

A	B	C
a	b	1
a	b	1
a	b	1
c	s	2
d	s	3
e	s	2
f	s	2
f	s	2
f	s	2
f	s	2

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>

If A1 is the same as A2 etc then return true, but when the cell content changes like for example when a4 goes from a to c my IF formula breaks down or doesn't return the result required

I would want this repeated for columns B and C returning the value in D.

So if the contents in A match and B match and C match = then return true.

a	b	1
a	b	1
a	b	1

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

The above for example is true as it displays matching criteria in the 3 columns

c	s	2
d	s	3
e	s	2

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

This would be false



Hope that makes sense

Thanks in advance

 

[jtakw]

Hi,

If I understand correctly, the IF function is not needed:

Excel 2010
	A	B	C	D
1	a	b	1	TRUE
2	a	b	1	TRUE
3	a	b	1	FALSE
4	c	s	2	FALSE
5	d	s	3	FALSE
6	e	s	2	FALSE
7	f	s	2	TRUE
8	f	s	2	TRUE
9	f	s	2	TRUE
10	f	s	2	FALSE
Sheet11

Cell Formulas
Range	Formula
D1	=AND(A1=A2,B1=B2,C1=C2)

D1 formula copied down.

 

[melbwarrior]

Hi,

If I understand correctly, the IF function is not needed:

Excel 2010

	A	B	C	D
1	a	b	1	TRUE
2	a	b	1	TRUE
3	a	b	1	FALSE
4	c	s	2	FALSE
5	d	s	3	FALSE
6	e	s	2	FALSE
7	f	s	2	TRUE
8	f	s	2	TRUE
9	f	s	2	TRUE
10	f	s	2	FALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula D1 =AND(A1=A2,B1=B2,C1=C2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

It fails when you there is change in value - in this instance the first 3 rows should be returning true

D1 formula copied down.

it fails when there is change in value - in this instance the first 3 rows should be returning true.

 

[jtakw]

Your written description says going downwards, not both upwards And downwards, if that's what you want, I need you to insert a blank row at the beginning:

Excel 2010
	A	B	C	D
1
2	a	b	1	TRUE
3	a	b	1	TRUE
4	a	b	1	TRUE
5	c	s	2	FALSE
6	d	s	3	FALSE
7	e	s	2	FALSE
8	f	s	2	TRUE
9	f	s	2	TRUE
10	f	s	2	TRUE
11	f	s	2	TRUE
Sheet11

Cell Formulas
Range	Formula
D2	=OR(AND(A2=A3,B2=B3,C2=C3),AND(A2=A1,B2=B1,C2=C1))

 

[melbwarrior]

Your written description says going downwards, not both upwards And downwards, if that's what you want, I need you to insert a blank row at the beginning:

Excel 2010

	A	B	C	D
1
2	a	b	1	TRUE
3	a	b	1	TRUE
4	a	b	1	TRUE
5	c	s	2	FALSE
6	d	s	3	FALSE
7	e	s	2	FALSE
8	f	s	2	TRUE
9	f	s	2	TRUE
10	f	s	2	TRUE
11	f	s	2	TRUE

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula D2 =OR(AND(A2=A3,B2=B3,C2=C3),AND(A2=A1,B2=B1,C2=C1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

looks good - thank you very much

 

[jtakw]

You're welcome, welcome to the forum.

 

[melbwarrior]

Appreciate it.