How to compare two cells when one has a #VALUE! error?

[JenniferMurphy]

I'm working on a UDF to convert Moneyline odds into percentages. I created a 2-column table with a bunch of Moneyline odds in Column 1 and manually entered the correct percentages in Column 2. I added Column 3 that calls my UDF to generate the correct percentages based on the values in Column 1. I added Column 4 to compare Columns 2 & 3 to ensure that the UDF is working.

My problem is that the UDF also checks for invalid Moneyline odds and returns a #VALUE! error. I put the text string “#VALUE!” in Column 2, but the compare expression returns a value error not true or false. This is the compare expression I'm using. Is there one that will work?

=Table1[@[UDF]]=Table1[@[Manual]]

And here's an except of the table.

 

[RoryA]

You could try something like:

=IF(ISERROR(Table1[@[UDF]]),IF(ISERROR(Table1[@[Manual]]),ERROR.TYPE(Table1[@[UDF]])=ERROR.TYPE(Table1[@[Manual]]),FALSE),Table1[@[UDF]]=Table1[@[Manual]])

If you just want to check for errors, then enter Error instead of #VALUE! and you could use:

=IFERROR(Table1[@[UDF]],"Error")=Table1[@[Manual]]

 

[Alex Blakenburg]

This might work for you:

=COUNTIFS(Table1[@[Manual]:[UDF]],[@Manual])=2

Book1
	A	B	C	D
5	Moneyline	Manual	UDF	Equal?
6	100	50%	50%	TRUE
7	-100	75%	50%	FALSE
8		#N/A	#N/A	TRUE
9		#VALUE!	#VALUE!	TRUE
Sheet1

Cell Formulas
Range	Formula
D6:D9	D6	=COUNTIFS(Table1[@[Manual]:[UDF]],[@Manual])=2

 

[JenniferMurphy]

You could try something like:

=IF(ISERROR(Table1[@[UDF]]),IF(ISERROR(Table1[@[Manual]]),ERROR.TYPE(Table1[@[UDF]])=ERROR.TYPE(Table1[@[Manual]]),FALSE),Table1[@[UDF]]=Table1[@[Manual]])

If you just want to check for errors, then enter Error instead of #VALUE! and you could use:

=IFERROR(Table1[@[UDF]],"Error")=Table1[@[Manual]][/code]

Your suggestion gave me an idea which seems to work.

2025 Warriors Odds.xlsm
	C	D	E	F	G
5	Moneyline	Manual	UDF	Equal?	Equal ?
6	+100	50.0000%	50.0000%	TRUE	TRUE
7	-100	50.0000%	50.0000%	TRUE	TRUE
8	+500	16.6667%	16.6667%	TRUE	TRUE
9	-500	83.3333%	83.3333%	TRUE	TRUE
10	+1000	9.0909%	9.0909%	TRUE	TRUE
11	-1000	90.9091%	90.9091%	TRUE	TRUE
12	0	100.0000%	100.0000%	TRUE	TRUE
13		#VALUE!	#VALUE!	#VALUE!	TRUE
MrExcel Equal

Cell Formulas
Range	Formula
E6:E13	E6	=CvtOdds([@Moneyline],"M","%")
F6:F13	F6	=[@UDF]=[@Manual]
G6:G13	G6	=IFERROR([@UDF]=[@Manual],TRUE)

 

[JenniferMurphy]

This might work for you:

=COUNTIFS(Table1[@[Manual]:[UDF]],[@Manual])=2

It looks like that might work as well. I'm gonna go with Rory's solution because I understand it better. Thanks.

 

[Alex Blakenburg]

Your final solution in column G, doesn't work. If one column gives an error and the other doesn't it will return True when you really want False.
The issue is that almost all functions in Excel will error out when they see an error. The only exception that I have found that actually recognises the error as a value in its own right is the CountIf(s), SumIf(s) family of function.

In those functions you can put =COUNTIFS(Table1[@[Manual]:[UDF]],#VALUE!)
and it will count the #VALUE!'s.
Every other function I have seen will just return an error.

In your requirement you want Manual to equal UDF, so if you take the value in either column and use that as the count criteria if both have that value the count will be 2. (Since you are using a value from one of the 2 columns being counted it will always be either a count of 1 or 2.
My suggested formula is:
=COUNTIFS(Table1[@[Manual]:[UDF]],[@Manual])=2
The long form of that would be:
=IF(COUNTIFS(Table1[@[Manual]:[UDF]],[@Manual])=2, TRUE, FALSE)

20250314 Recognising Errors VALUE NA etc using CountIfs JenniferMurphy.xlsx
	A	B	C	D	E	F
3	Moneyline	Manual	UDF	Jennifer	Rory 1	Alex
4	100	50%	50%	TRUE	TRUE	TRUE
5	-100	75%	50%	FALSE	FALSE	FALSE
6		50%	#VALUE!	TRUE	FALSE	FALSE
7		#VALUE!	50%	TRUE	#VALUE!	FALSE
8		#N/A	#VALUE!	TRUE	FALSE	FALSE
9		#N/A	#N/A	TRUE	TRUE	TRUE
10		#VALUE!	#VALUE!	TRUE	TRUE	TRUE
Data

Cell Formulas
Range	Formula
D4:D10	D4	=IFERROR([@UDF]=[@Manual],TRUE)
E4:E10	E4	=IF(ISERROR([@UDF]),IF(ISERROR([@Manual]),ERROR.TYPE([@UDF])=ERROR.TYPE([@Manual]),FALSE),[@UDF]=[@Manual])
F4:F10	F4	=COUNTIFS(Table1[@[Manual]:[UDF]],[@Manual])=2

 

[JenniferMurphy]

Your final solution in column G, doesn't work. If one column gives an error and the other doesn't it will return True when you really want False.

You're probably right. It was just a quick and dirty solution on my part. This is all kind of a quick and dirty project. All I really care about is that the numerical values are equal. Both columns do that. I can assess the cases where the function returns an error visually.

I'm sure your solution works. All of your solutions over the years have worked. I just don't understand it based on a quick look and I don't have time right now to dig into it in more detail. If I need to in the future, I know where I can come back to try and figure it out.

In the meantime thanks for the time and effort that you always put into every reply. Perhaps this solution will be of use to others. Thank you