too many "if"s ?

[excelNewbie22]

hi,
i placed on dozens of cells an if formula, with or,
but it return the wrong result,
i checked several times,
and all conditions for returning "0" - the wanted result, are met,
but it keep returning "1",
at first i thought there's too many "if",
so i delete half to them, just to check,
and still
please tell me where i'm wrong...

=IF(OR(BA2=1,BB2=1,BC2=1,BD2=1,BE2=1,BF2=1,BG2=1,BH2=1,BI2=1,BJ2=1,BK2=1,BL2=1,BM2=1,BN2=1,BO2=1,BP2=1,BQ2=0,BR2=0,BS2>1,BT2>4,BU2=1,BV2=1,BW2=6,BW2=0,BX2=0,AK2+BY2=2,BZ2=1,CA2=1,CB2>0,CC2=1,CD2>1,CE2>2,CF2>3,CG2>4,CH2>4,CI2>3,CJ2>3,CK2>2,CL2>2,CM2>1,CN2>2,CO2=0,CP2=0,CQ2=5,CR2=5,CS2=5,CT2=5,CT2=0,CU2>0,CV2=1,CW2=1,CX2=1,DG2>0,DH2>0,DI2>0,DA2>3,DB2>4,DC2>5,DD2>0,DE2>0,DF2=0,CY2=1,DK2>2,DL2>1,DM2=0,DN2+DO2=0,DQ2+DR2+DS2=0,DT2=0,DU2=1,DV2=1,DW2=1,DX2=1,DY2>4,DZ2>4),1,0)

 

[etaf]

a blank cell is seen as zero
try
just using in a cell
=B2=0
and b2 is blank - you see it return true

=B2=0

Book3
	A	B
1	1
2	TRUE
Sheet3

Cell Formulas
Range	Formula
A1	A1	=IF(OR(BA2=1,BB2=1,BC2=1,BD2=1,BE2=1,BF2=1,BG2=1,BH2=1,BI2=1,BJ2=1,BK2=1,BL2=1,BM2=1,BN2=1,BO2=1,BP2=1,BQ2=0,BR2=0,BS2>1,BT2>4,BU2=1,BV2=1,BW2=6,BW2=0,BX2=0,AK2+BY2=2,BZ2=1,CA2=1,CB2>0,CC2=1,CD2>1,CE2>2,CF2>3,CG2>4,CH2>4,CI2>3,CJ2>3,CK2>2,CL2>2,CM2>1,CN2>2,CO2=0,CP2=0,CQ2=5,CR2=5,CS2=5,CT2=5,CT2=0,CU2>0,CV2=1,CW2=1,CX2=1,DG2>0,DH2>0,DI2>0,DA2>3,DB2>4,DC2>5,DD2>0,DE2>0,DF2=0,CY2=1,DK2>2,DL2>1,DM2=0,DN2+DO2=0,DQ2+DR2+DS2=0,DT2=0,DU2=1,DV2=1,DW2=1,DX2=1,DY2>4,DZ2>4),1,0)
A2	A2	=B2=0

 

[Fluff]

You haven't exactly given us much to go, but maybe you should be using and rather than or.

 

[jorismoerings]

Hi,

Currently we can't check if any of the conditions in the formula should return TRUE instead of False as it currently does.
It would really help if you posted the source data to help us investigate and come up with a solution instead of current assumptions.

 

[Joe4]

These long formulas can be an absolute bear to maintain/understand (as you can plainly see).
What is worse is if somewhere down the road you need to make an edit to it.
IMO, when tasked with something this long/complex, I usually prefer to create a User Defined Function (UDF) in VBA, where you can more easily see what is going on, and update/follow/debug it much easier.

Also note I think you may be able to shorten your formula a bit by incorporating some COUNTIF functions, i.e.
this whole section:

OR(BA2=1,BB2=1,BC2=1,BD2=1,BE2=1,BF2=1,BG2=1,BH2=1,BI2=1,BJ2=1,BK2=1,BL2=1,BM2=1,BN2=1,BO2=1,BP2=1

could be replaced with:

COUNTIF(BA2:BP2,1)>0

If that is supposed to be an AND instead of an OR, then you could just use this instead:

COUNTIF(BA2:BP2,1)=16

 

[excelNewbie22]

thank you all for helping,
copied the values only with the if formula

test
	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	42	43	44	45	46-7	48	48	49		90	91	92	93	94	95	96	96	97	98	50	101	102	103	103	104	105	105	105	105	106	107	108	109	110	110	0 ?
2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	1	0	0	0	2	3	0	0	0	0	0	0	0	1	0	0	2	0	1	0	0	1	1	2	2	3	0	2	0	0	0	0	0		0	0	0	0	0	1	1	0	0		0	1	4	6	6	?	0	0	1	1	0	0	0	0	1	1	1
test

Cell Formulas
Range	Formula
EA2	EA2	=IF(OR(BA2=1,BB2=1,BC2=1,BD2=1,BE2=1,BF2=1,BG2=1,BH2=1,BI2=1,BJ2=1,BK2=1,BL2=1,BM2=1,BN2=1,BO2=1,BP2=1,BQ2=0,BR2=0,BS2>1,BT2>4,BU2=1,BV2=1,BW2=6,BW2=0,BX2=0,AK2+BY2=2,BZ2=1,CA2=1,CB2>0,CC2=1,CD2>1,CE2>2,CF2>3,CG2>4,CH2>4,CI2>3,CJ2>3,CK2>2,CL2>2,CM2>1,CN2>2,CO2=0,CP2=0,CQ2=5,CR2=5,CS2=5,CT2=5,CT2=0,CU2>0,CV2=1,CW2=1,CX2=1,DG2>0,DH2>0,DI2>0,DA2>3,DB2>4,DC2>5,DD2>0,DE2>0,DF2=0,CY2=1,DK2>2,DL2>1,DM2=0,DN2+DO2=0,DQ2+DR2+DS2=0,DT2=0,DU2=1,DV2=1,DW2=1,DX2=1,DY2>4,DZ2>4),1,0)

etaf - checked for empty cells and there are none (in the formula)
Fluff - can't use AND because it needs to be an OR,
joe - when the formula will be fixed i will glad using the
COUNTIF(BA2:BP2,1)>0

p.s
AK2=0

 

[Fluff]

BQ2 is 0, which is why you are getting 1 as a result.

 

[excelNewbie22]

didn't noticed, but still... if i try manually change BQ2 to 1, i still getting 1

 

[Fluff]

On the formula tab click Evaluate Formula, you can then go through the formula step by step to see where it's returning true.

 

[jorismoerings]

Adding to @Fluff

Your OR function contains 74 conditions of which 17 of them will return TRUE hence 1 from the IF function.