I have a fairly simple equation with an IFERROR function to calculate some currency ---> =IFERROR(K67+(K67*L67),"")



If there is no input I want it to show nothing ("") and if there is input it shows the amount in dollars. That function works fine and comes back quite a bit in this sheet. Nevertheless, it stops working fine after line 66 so line 67 - which is just a copied version of the 66 one - does not show a blank (empty) cell but $0.00. Does anyone have an idea how this is possible? All functions are exactly the same.

Working function ---------> =IFERROR(K60+(K60*L60),"")
Not working function ----> =IFERROR(K67+(K67*L67),"")

You might want to try:

=IF(COUNT(K60:L60)<2;"",K60+(K60*L60))

I tried that but that doesn't work... Excel says there is something wrong with the formula. What I think is weird that it does go right in the cells above.

Can't you upload your file?

Try replacing the semicolons in xor's formulas with commas...

Thank you NBVC.

Now and then I unfortunately forget to replace some semicolons (which is default for me) with commas.

no problem

See attachment. But the problem has been solved, there are two ways to fix the problem.

First is changing the formaula to this one: =IFERROR(1/(1/(K60+(K60*L60))),"") (ChemistB deserves the credits for this solution)

The second solution is by formatting the cell from currency to customs and type $0.00;-0;"" in the 'type' bar.

I have showed both options in the red sections of the Excel-sheet with option one on the right red cell and option two on the left.

I'm sure there are more options but these work.


ChemistB described the root cause for those who want to know:
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))),"")

Attached Images

Capture.jpg (95.2 KB, 15 views)

Attached Files

Book1.xlsx (9.0 KB, 7 views)

Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!


Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
https://www.excelforum.com/excel-for...working-2.html

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post