Question description:
This user has given permission to use the problem statement for this
blog.
using excel as a checkbook -- formula =IF(ISBLANK(E283),"",N282-J283+L283) after 280 lines it is now just saying VALUE in the balance column (N)
What have I done wrong?
Solved by T. Y. in 44 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
04/10/2018 - 05:02
did I make my question clear?
Excelchat Expert
04/10/2018 - 05:02
Hello, I understand that you need help in checking where the problem is with the formula you are using, right?
User
04/10/2018 - 05:02
yes
Excelchat Expert
04/10/2018 - 05:03
Alright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
04/10/2018 - 05:03
At first glance, it doesn't look like there is a problem with the formula itself so are you able to share your file so I can inspect other elements?
User
04/10/2018 - 05:04
not sure how to do that
Excelchat Expert
04/10/2018 - 05:04
There is a clip icon next to this chat. CLick that then select your file.
User
04/10/2018 - 05:06
I just sent it
[Uploaded an Excel file]
Excelchat Expert
04/10/2018 - 05:06
Thank you.
Excelchat Expert
04/10/2018 - 05:06
Give me a few minutes to analyze it.
User
04/10/2018 - 05:06
thank you
Excelchat Expert
04/10/2018 - 05:08
Thank you for waiting. I have found the reason for the error.
Excelchat Expert
04/10/2018 - 05:08
It's because you L283 isn't really empty
Excelchat Expert
04/10/2018 - 05:09
Instead, it has a space on it.
Excelchat Expert
04/10/2018 - 05:09
So if you go to L283 then press delete, the formula in N will work.
Excelchat Expert
04/10/2018 - 05:09
Let me know once you've tried that.
User
04/10/2018 - 05:11
Not sure how that happened. When I want to continue the formula I just hook the corner of the last one with the crosshair symbol and drag down a few more spaces
Excelchat Expert
04/10/2018 - 05:11
The space bar may have been accidentally pressed while your cursor was in L283. It happens to the best of us.
Excelchat Expert
04/10/2018 - 05:12
You may also have to delete the value of N302
User
04/10/2018 - 05:14
How do you think I put that space in there. And how did you know it wasn't actually empty?
Excelchat Expert
04/10/2018 - 05:15
There are a lot of possible scenarios and I can't really confidently say that it was you who placed the space in there. It's just that it can happen. I, myself, have made this same mistake in the past so I know that it is one possible reason so it's one of those things that I check.
User
04/10/2018 - 05:16
And I will check that first from now on -- I REALLY like using excel as a checkbook -- much happier than using Quicken or QuickBooks
Excelchat Expert
04/10/2018 - 05:17
I really have not tried both Quicken and Quickbooks but yes, I know a few people who prefers Excel for checkbooks as well.
User
04/10/2018 - 05:18
Btw it fixed it instantly. THANK YOU!!!!
Excelchat Expert
04/10/2018 - 05:18
By the way, you may want to consider using this formula instead:
Excelchat Expert
04/10/2018 - 05:18
=IF(ISBLANK(E3),0,N2-J3+L3)
Excelchat Expert
04/10/2018 - 05:19
"" and 0 can be considered as having the same value but in reality, there's a big difference.
Excelchat Expert
04/10/2018 - 05:19
You may encounter issues if you use "" instead of 0.
Excelchat Expert
04/10/2018 - 05:19
By using the above formula, you will just need to delete the space in L283 and the rest will work.
Excelchat Expert
04/10/2018 - 05:20
Here's your file. I updated the formula to use 0 instead of "".
[Uploaded an Excel file]
Excelchat Expert
04/10/2018 - 05:20
I'll let you decide which one you'd like to use.
User
04/10/2018 - 05:23
Thank you --- very quickly do you know what kind of issues ,"", could cause? And I'm guessing with the formula you recommend using 0 (which is zero, right) I wouldn't face any issues -- or I'll say shouldn't face...
Excelchat Expert
04/10/2018 - 05:24
The most common issue you will face when using "" instead of 0 is when performing some calculations "" may cause an error.
Excelchat Expert
04/10/2018 - 05:25
Try this:
Excelchat Expert
04/10/2018 - 05:25
Write =A1+B1 in cell C1
Excelchat Expert
04/10/2018 - 05:25
Then type ="" in A1
Excelchat Expert
04/10/2018 - 05:25
Your formula in C1 will return an error.
Excelchat Expert
04/10/2018 - 05:27
Here's an example:
Excelchat Expert
04/10/2018 - 05:27
[Uploaded an Excel file]
Excelchat Expert
04/10/2018 - 05:28
Look at row 1, it has an error because the formula in A1 is using "" instead of 0 like row 2.
Excelchat Expert
04/10/2018 - 05:31
Still with me?
Excelchat Expert
04/10/2018 - 05:37
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon. I sincerely hope we were able to address your concern.
Excelchat Expert
04/10/2018 - 05:41
I can see that you've extended the session. Do you have any other questions regarding the original concern?
Excelchat Expert
04/10/2018 - 05:45
Are you still there?
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
