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)

 

[Joe4]

Your first formula returns a "N/A" and then tries to add it to the second.
You obviously cannot do addition with "N/A".

You either need to make that one 0 too.
Or, if you want to return "N/A", only have one IFERROR around the WHOLE formula, and return "N/A" or 0 there, depending on what you want.

 

[janema]

Your first formula returns a "N/A" and then tries to add it to the second.
You obviously cannot do addition with "N/A".

You either need to make that one 0 too.
Or, if you want to return "N/A", only have one IFERROR around the WHOLE formula, and return "N/A" or 0 there, depending on what you want.

I did try doing that around the entire formula, like this:

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

But this returned all values at zero which is incorrect.

 

[Joe4]

If you break it apart, which one returns the error?

=P22*$AI$1

or

=VLOOKUP(A22,'Option Pivot 9.17.24'!A:C,4,FALSE)

 

[janema]

If you break it apart, which one returns the error?

=P22*$AI$1

or

=VLOOKUP(A22,'Option Pivot 9.17.24'!A:C,4,FALSE)

In this particular cell, they both return an error. The first one returns the #VALUE! error and the second one returns #N/A error.

 

[Joe4]

Can you show/tell us exactly what currently is in cells P22, AI1, and A22?

 

[janema]

Can you show/tell us exactly what currently is in cells P22, AI1, and A22?

P22 = empty
AI1 = $10
A22 =3L38USJER (employee ID# for the Vlookup statement)

The result of the Vlookup statement for this person is 0.

 

[Joe4]

If your first formula is returning an error, then either P22 or AI1 is NOT really blank or numeric.

If you enter these formulas in any blank cells, please tell us what they return:
=ISNUMBER(AI1)
=ISNUMBER(P22)

 

[janema]

If your first formula is returning an error, then either P22 or AI1 is NOT really blank or numeric.

If you enter these formulas in any blank cells, please tell us what they return:
=ISNUMBER(AI1)
=ISNUMBER(P22)

For AI1 it returns TRUE
For P22 it returns FALSE

 

[Joe4]

For AI1 it returns TRUE
For P22 it returns FALSE

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?