IFERROR not (consistently) working

Cell K60 and K67 are now blank because they also contain an IFERROR function with a currency format. The L60 and L67 are e percentage cell but in this case also empty. The weird thing is that both lines 60 and 67 have the exact same content (empty) and formulas, but only from 60 down it's indicating $0.00 while the rest of the IFERROR (60 and up) show the requested empty cell.

Originally Posted by 6StringJazzer

What is in cells K60, L60, K67, and L67?

If you do arithmetic on an empty cell, it is interpreted as 0. So returning $0.00 seems perfectly normal to me. I would not expect your formula to encounter an error unless there is a non-numeric in K or L, or unless K or L contains a formula that returns blank.

Cell K60 and K67 are now blank because they also contain an IFERROR function with a currency format. The L60 and L67 are e percentage cell but in this case also empty. The weird thing is that both lines 60 and 67 have the exact same content (empty) and formulas, but only from 60 down it's indicating $0.00 while the rest of the IFERROR (60 and up) show the requested empty cell.

Originally Posted by JohnTopley

What do the IFERROR in K60 /K 67 return?

Suggests cells are not empty but have a space.

Yes, they are both empty so the IFERROR function here works fine for both.

This in K60 produced a result of "blank" (forcing an IFERROR condition)

=IFERROR(VLOOKUP($C$2,$M$1:$N$6,2,0),"")

this produced a result of 0

=IFERROR(VLOOKUP($C$2,$M$1:$N$6,2,0),0)

with the formula

=IFERROR(K60+(K60*L60),"")

Let me just give you all the data so you can reproduce my calculation and check what your result is of you keep the cells all blank...

The 'problem' cell has a format 'currency' with the formula: =IFERROR(K60+(K60*L60),"")
Cell K60 (blank) has a format 'currency' with the formula: =IFERROR(F60*G60*I60,"")
Cell L60 (blank) has a format 'percentage' and is a cell where you insert a percentage
Cell F60 (blank) has a format 'general' and is just a cell where you insert an amount (quantity in this case)
Cell G60 (blank) has a format 'general' and is just a cell where you insert an amount (duration in this case)
Cell I60 (blank) has a format 'currency' and is a cell where a lookup function inserts an amount (price in this case). I think if you just leave this as an empty cell with currency format you'll be fine.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Use on these cells Home-Editing-Clear-Clear All and check again
picture is useless because nobody can check or reproduce error(s)

so attach your original workbook after delete all confident data but leave everything what is necessary for your formula(s)
Like on picture.

I got it! I fixed the problem with custom cell format by punching in the code 0.00;-0;"" and that works.

Thank you for the help Sandra and check out the workbook

You are welcome
(sandy not sandra )
so
02repsolv.gif

Originally Posted by sandy666

You are welcome
(sandy not sandra )
so
Attachment 514690

Thank you Sandy!

Oh, and the code is not 0.00;-0;"" but $0.00;-0;"" Otherwise the dollar sign wont show

That's ok but mark thread as SOLVED from Thread Tools because most of the people looking for solutions in threads labeled as SOLVED

Originally Posted by ChemistB

If you want the root cause of your issue, here is what was happening.
In some of your earlier cells, you were using
=IFERROR(VLOOKUP($C$2,$M$1:$N$6,2,0),"")
This does not produce a "Blank" cell. If you use the formula =ISBLANK(K62), it will come up false. It is a null set and excel sees it as text. (=ISTEXT(K62) = TRUE). When you try to add or multiply text, you get an error so the IFERROR function takes over and you get "" in your cell.
If the cell is really blank or is 0, then the arithmetic works perfectly and you get a result of 0, not an error.

Since you don't seem to want zero's or errors, one way to fix your problem would be with this equation

=IFERROR(1/(1/(K60+(K60*L60))),"")

Thank you for this explanation and corrected equation ChemistB! I tried this and works perfectly fine too...

Have the thread on SOLVED now with two solutions for this problem.