Right now I have a table that has cells with formulas in it, but because some cells are missing certain input values not yet entered, The cells with formulas either display numbers from the cells it's pulling data from, or DIV/0.
Is there anyway to have these cells be completely blank until all of the necessary data is entered at which point the formula would "activate" and displays it's proper output instead of DIV/0?
Last edited by DarkSoul; 07-27-2014 at 08:50 AM.
Try this:
=iferror(your formula,"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Or if using excel 2003 or earlier:
=if(iserror(your formula),"",your formula again)
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
You could use something like this:
=If(certain cell is blank,"",your formula)
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Excellent, the IFERROR clears up the div/0 errors nicely.
The next step however might be more complicated....or not...
I think this is kind of what I need now.
My table calculates losses and returns....without getting into too many details.
So the cells that had errors are cleaned up, they're cells/rows that have no values as of yet.
Some rows have some numbers, but aren't complete in that the "loss" calculations comes up to 100%, and the "returns" calculations come up as 0% because one of the values has not yet been entered to do the full calculation.
Is it possible to retain the "IFERROR" clause, but to also blank out cells where the formulas are missing one of the values to do the calculation properly?
That probably doesn't make sense but I have to go out for a short while, so when I get back I will post a sample of my spreadsheet to make it more clear what I want to do, if it's possible.
Try this. I just used a very simple formula to act as an example...
Delete a1 or B1...
or delete c1.
In effect what you are wanting to do is use the =IF(YOUR FORMULA="","",YOUR FORMULA) and surround it by the IFERROR.
=IFERROR(IF(YOUR FORMULA="","",YOUR FORMULA),"")
Thank you, I worked it out and applied it to my spreadsheet. It's looking pretty professional now.
The only problem I have now is with a chart I wanted to throw in.
I have 2 sets of numbers. In, and Out.
In is always a larger number than Out.
When I chart them, the Out numbers are above the In numbers on the chart, even though Out is lower. it's a line graph, so the line for Out is above the line for In.
Can you supply a sample of the data for the chart that has the lesser value over the greater value?
Nevermind, it appears to be working now.
Thanks guys.
OK. If that's your problem sorted, can you mark the thread as solved and (preferably) say thanks to all who helped by clicking the Add Reputation button at the foot of their post(s)?
rep'd and solved
Thanks for that.
Thank you for the feedback and good luck.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks