LinkBack URL
About LinkBacks
Hi everyone! I have encountered a problem; I need to find difference of 2 cell, but since one of these cells in my data is always blank, the formula returns a value error. How do I find a way around it?
Thanks
Hi everyone! I have encountered a problem; I need to find difference of 2 cell, but since one of these cells in my data is always blank, the formula returns a value error. How do I find a way around it?
Thanks
If it was truly blank, it would not return a value error. I'm guessing it contains "" as a result of a formula?
If it's ALWAYS blank, then why are you subtracting it. If you know column B is always blank, then A-B = A. What am I missing?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
yes, it contains "" as a result of formula although I have converted the formulas into values but it still is giving value error. Sometimes the column A is blank, other times column B is blank and there are almost 5000 such entries.Originally Posted by ChemistB
B1 should be blank itself, or returns "", " ", " " from calculation
A1-B1 should be:
=A1-IF(TRIM(B1)="",0,B1)
Quang PT
@terry
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
Okay, so if your data starts in row 2 and we are subtracting A - B
=IF(A2<>"", IF(B2<>"", A2-B2, A2), IF(B2<>"", -B2, 0)
The above formula returns -B2 if A2 is blank (and B2 contains a value) and it returns 0 if both are blank
Perhaps
= N(A2)-N(B2)
Does that work for your data?
@ Chemist, Yes, that does. Thanks a lot
After converting formula blanks to values there is some kind of leftover "junk" in the cell even though you can't see it or calculate it.
Consider this example.
A1 formula: =IF(10>0,"","X")
10 is > 0 so the formula returns blank "".
Copy>Paste Special>Values
The cell looks blank but it's not.
=LEN(A1) = 0 (so you'd think the cell is empty but it's not)
=A1="" = TRUE (so you'd think the cell is empty but it's not)
=COUNTA(A1) = 1 WTH!
The only way to get rid of the leftover "junk" is to clear or delete the cell contents.
Last edited by Tony Valko; 12-02-2015 at 04:17 AM.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
If they are really "" and not " " or something like that I've found AutoFiltering for "Blanks", selecting those cells and hitting delete works.
Dave
Hi there,
I am new to the forum and have an excel formula question that I haven't been able to crack.
I am trying to calculate the difference between two cells and some of the cells are blank and it's not returning the info I am seeking.
I'm currently using =A2-B2 and getting the correct difference. However, when I have a blank cell in the "B" column I'm getting the value in the "A" column as the answer. I need a formula that will give an answer with a blank column as false or something that won't equal column "A". This is so I can get an accurate amount of my over/under for my budget. Thank you for any advise and help on this. Please see the example attached.
Hello BigJ7013. Welcome to the forum.
Administrative Note:
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks