IFERROR results from adding two statements

[janema]

I am trying to get the below Excel formula to return zero if the result of adding those two are an error. It is taking the amount in cell P22 and multiplying it by the dollar amount in cell AI1 then adding that total to the amount that the vlookup returns. If this answer ends up being an error, I want it to return zero, but I am still seeing a #VALUE! result in many cells. Help please! Thank you so much in advance for your guidance.

=IFERROR(P22*$AI$1,"N/A")+IFERROR(VLOOKUP(A22,'Option Pivot 9.17.24'!A:C,4,FALSE),0)

 

[janema]

OK, that tells me that P22 is NOT really empty, but has something in it.
You can easily confirm it with this formula:

=LEN(P22)
If it returns anything other than 0, it is NOT empty.
Perhaps you have a single space or something like that in there?
A space is considered math, so you cannot do math on it.
How is cell P22 having data entered into it?

When I do =LEN(P22) it returns zero.

 

[janema]

When I do =LEN(P22) it returns zero.

This is why I am stumped and confused.

 

[Joe4]

That makes no sense.
What version of Excel are you using?

Is there any possibility you could upload a file (removing any sensitive data first) with a real simple of one not working to a file sharing site, and provide the link here?
I think this may be something that we need to see.

 

[janema]

OK, that tells me that P22 is NOT really empty, but has something in it.
You can easily confirm it with this formula:

=LEN(P22)
If it returns anything other than 0, it is NOT empty.
Perhaps you have a single space or something like that in there?
A space is considered math, so you cannot do math on it.
How is cell P22 having data entered into it?

When I use =LEN(P22), it returns a zero. Very odd.

I ended up getting the formula to work. I used:

=IFERROR(P22*$AI$1,0)+IFERROR(VLOOKUP(A22,'Option Pivot 9.17.24'!A:G,4,FALSE),0)

 

[Joe4]

When I use =LEN(P22), it returns a zero. Very odd.

I ended up getting the formula to work. I used:

=IFERROR(P22*$AI$1,0)+IFERROR(VLOOKUP(A22,'Option Pivot 9.17.24'!A:G,4,FALSE),0)

OK, that was one of the options I mentioned in my first reply.

Anyway, glad you got it working out.

 

[Alex Blakenburg]

Ideally we should sort out what you have in P22. I suspect =unicode(P22) will return 8203.
An alternative might be:
=N(P22)*$AI$1+IFERROR(VLOOKUP(A22,'Option Pivot 9.17.24'!A:G,4,FALSE),0)

 

[janema]

OK, that was one of the options I mentioned in my first reply.

Anyway, glad you got it working out.

Thank you so much for all your help any patience.