check true and false if error on cell

[sksanjeev786]

Hi Team,

Need help on below data in column N3, O3 i need True if the data (C3,I3) are same

book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	POE Diapers	70	#DIV/0!	11%			POE Diapers	70	29%	11%		TRUE	TRUE	#DIV/0!	TRUE
2	Non POE Diapers	377	32%	11%			Non POE Diapers	377	32%	11%		TRUE	TRUE	TRUE	TRUE
3	Usual LD pack size Reg	0	#DIV/0!	#DIV/0!			Usual LD pack size Reg	0	#DIV/0!	#DIV/0!		TRUE	TRUE	#DIV/0!	#DIV/0!
4	Usual LD pack size XL	0	#DIV/0!	#DIV/0!			Usual LD pack size XL	0	#DIV/0!	#DIV/0!		TRUE	TRUE	#DIV/0!	#DIV/0!
5
6
7														Need True on yellow
8														Need False on orange
Sheet1

Cell Formulas
Range	Formula
L1:O4	L1	=A1=G1

Regards,
Sanjeev

 

[Toadstool]

Hi Sksanjeev786,

Assuming a #DIV/0 should always trigger a FALSE then:

=IFERROR(A1=G1,FALSE)

 

[jasonb75]

Based on the example, try this in N1

=IFERROR(C1=I1,NOT(COUNT(C1,I1)))

Note that with the exception of #N/A! there is no simple way of identifying the actual error in the cell. If there are errors other than #DIV/0! or if there are text entries instead of numbers then it may still give incorrect results.

Assuming a #DIV/0 should always trigger a FALSE then:

Assuming that you didn't read the comment in N7 ?

 

[sksanjeev786]

Hi Sksanjeev786,

Assuming a #DIV/0 should always trigger a FALSE then:

=IFERROR(A1=G1,FALSE)

Thank you Toadstool for your help on this.
it works as yo have mentioned.

Regards
Sanjeev

 

[sksanjeev786]

Based on the example, try this in N1

=IFERROR(C1=I1,NOT(COUNT(C1,I1)))

Note that with the exception of #N/A! there is no simple way of identifying the actual error in the cell. If there are errors other than #DIV/0! or if there are text entries instead of numbers then it may still give incorrect results.

Assuming that you didn't read the comment in N7 ?

Thanks Jason yes I have check if we getting #NA or other error it comes as True.
as of now, I am getting this error #Div/0! thanks for your time on this.

Will check later if we have a #NA error and can diferrencaite on that.
Thanks.
Sanjeev.

 

[jasonb75]

Will check later if we have a #NA error and can diferrencaite on that.

I think that you may have misunderstood what I meant, to try and make it a bit clearer.

The formula that I have suggested will assume that anything which is not a number is a #DIV/0! error. If you have #N/A! and #Div/0! then it will return TRUE.

It can be rewritten to show FALSE if it is needed, but only for #N/A! errors by using either the IFNA or ISNA functions.

For any other errors, #REF, #NAME?, #VALUE!, #SPILL, it is not possible to differentiate between them and #DIV/0! so comparing 2 different errors in this case will always return TRUE with simple formulas.

It might be possible to compare different results in such case by using the formula that returns the error or with a UDF (vba function) but a lot more information would be needed in order to consider attempting that.

 

[sksanjeev786]

I think that you may have misunderstood what I meant, to try and make it a bit clearer.

The formula that I have suggested will assume that anything which is not a number is a #DIV/0! error. If you have #N/A! and #Div/0! then it will return TRUE.

It can be rewritten to show FALSE if it is needed, but only for #N/A! errors by using either the IFNA or ISNA functions.

For any other errors, #REF, #NAME?, #VALUE!, #SPILL, it is not possible to differentiate between them and #DIV/0! so comparing 2 different errors in this case will always return TRUE with simple formulas.

It might be possible to compare different results in such case by using the formula that returns the error or with a UDF (vba function) but a lot more information would be needed in order to consider attempting that.

Thanks for the detailed information Jason

I Will try to implement the things you have mentioned above and will get back to you if need any clarifications