#VALUE! display when a variable is missing in formula.

I am getting the #VALUE! display when a variable is missing in formula. In the formula =IF((J2+L2)=0," ",(K2*1000)/(J2+L2)), L2 is zero in many cases but K2 and J2 have values which are non-zero.

Any suggestions?

Hi Jim,
It can be difficult without the data to understand the need. Try these two formula and see if they work for you.

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

Attached is the Test Macro Buttons Excel spreadsheet and there are three (3) buttons currently used. Abstract, Annual and Monthly on the far left under Macros. If a field is blank, it gives the #VALUE! instead of the actual answer. According to some of the equations, I was expecting the fields to be calculated if a variable was missing and I understand the #DIV/0! I would prefer the #VALUE! to be calculated and the #DIV0! to be blank.

Thanks!

I don't see any buttons or code.

This is an add-in so how do I send that with an Excel file?

If you are in the visual basic editor you should be able to choose the add in project and see the code.

You can upload the code.

Please Login or Register  to view this content.

Jim you have quite a few posts, so I'm going to have to insist you use code tags. Go to your last post, choose the edit button, select all the code text and click the # button on the toolbar. This will put code tags at the beginning and end of your code. Choose to save the changes and your code will look like mine below.

Please make this a habit when posting code.

Please Login or Register  to view this content.

Finished adding code tags and thanks for the tip. There are two (2) macros in the code Prod_Abstract and Prod_Annual. Prod_Annual is cleaner and more succinct whereas Prod_Abstract is a recorded macro.

You have an empty string in your cells which is causing the errors in your formulas.

In other words the cells are not blank, it's the equivalent of clicking into a cell and pushing the space bar and hitting enter.

When I click into a cell that looks empty and hit the delete key, removing the empty string, the formula works fine.

Thanks. Is there VBA code that I can add that will remove the empty strings?

btw all your sum() functions are unnecessary