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?
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?
Last edited by Jim15; 01-25-2016 at 01:18 PM.
Jim15
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.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
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.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
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.
Last edited by Jim15; 01-21-2016 at 05:03 PM.
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
Josie
if at first you don't succeed try doing it the way your wife told you to
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks