All solutions IF Expert Solution – Excel IF Problems

Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a simple solution Bank balance - 7000 (1) Less unpresented Cheques - 500 (2) Add cheques lodged not credited - 200 (3) Total - 6700 (formula = 1+2-3 However if 2 is made 0 the total shows an error. Can we do a IF formula for the same please?
Solved by D. D. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 17/12/2017 - 11:24
Welcome, Thanks for choosing Got It Pro-Excel How may I help you today?
User 17/12/2017 - 11:25
Hi when I change the 500 to zero the total shows an error. Is there an IF formula for the same please?
Excelchat Expert 17/12/2017 - 11:25
I will share the solution with you through an excel Sheet
Excelchat Expert 17/12/2017 - 11:25
Here is the sheet
[Uploaded an Excel file]
Excelchat Expert 17/12/2017 - 11:27
is this what you want?
Excelchat Expert 17/12/2017 - 11:27
Change C4 to 0
Excelchat Expert 17/12/2017 - 11:27
C8 will show Error
Excelchat Expert 17/12/2017 - 11:28
Are you there?
Excelchat Expert 17/12/2017 - 11:30
Hello Sir are you there?
User 17/12/2017 - 11:30
Hi it is not working. When I change 500 to 0 it shows an error
Excelchat Expert 17/12/2017 - 11:30
Please perform that on the sheet and show me
Excelchat Expert 17/12/2017 - 11:30
It shouldn't show an error
Excelchat Expert 17/12/2017 - 11:30
In the sheet it doesn't show an erro
Excelchat Expert 17/12/2017 - 11:31
Right?
User 17/12/2017 - 11:31
in your sheet also it is showing an error when I change 500 to 0
Excelchat Expert 17/12/2017 - 11:31
Sir , in the sheet you uploaded and I do not see an error
Excelchat Expert 17/12/2017 - 11:32
Can you make the cellC4 = 0
Excelchat Expert 17/12/2017 - 11:32
in the uploaded sheet window?
Excelchat Expert 17/12/2017 - 11:32
Check your screen
Excelchat Expert 17/12/2017 - 11:32
I will change Cell C4 to but Total will be 6800
Excelchat Expert 17/12/2017 - 11:33
See the value got changed to 6800
Excelchat Expert 17/12/2017 - 11:33
Now changing back to 500
Excelchat Expert 17/12/2017 - 11:33
Now the Total shows 74300
Excelchat Expert 17/12/2017 - 11:33
7300
Excelchat Expert 17/12/2017 - 11:33
Do you get it?
Excelchat Expert 17/12/2017 - 11:33
=C2+C4-C6 will never show error when C4 = 0
Excelchat Expert 17/12/2017 - 11:34
Also try =SUM(A2,B2)-C2
Excelchat Expert 17/12/2017 - 11:35
Is there anything else you want to know regarding this issue?
User 17/12/2017 - 11:35
Thanks
User 17/12/2017 - 11:35
Much appreciated
Excelchat Expert 17/12/2017 - 11:35
Is there anything else you want to know regarding this issue?
User 17/12/2017 - 11:35
No, thanks
Excelchat Expert 17/12/2017 - 11:35
Thanks for using Got It Pro-Excel Please give your kind feedback for our service Have a good day ahead!
Excelchat Expert 17/12/2017 - 11:38
You may end this session now
User 17/12/2017 - 11:38
Hi one more query, if there are two deductions what do you do please in the SUm formula
Excelchat Expert 17/12/2017 - 11:38
=C2-C4-C6
Excelchat Expert 17/12/2017 - 11:39
Are you talking something like the above formula?
Excelchat Expert 17/12/2017 - 11:39
Please show me through the preview window
User 17/12/2017 - 11:39
= Sum(A2,B2)-C2-D2
Excelchat Expert 17/12/2017 - 11:39
Yes this should work
Excelchat Expert 17/12/2017 - 11:40
=SUM(C2,C4)-C6-C7
User 17/12/2017 - 11:42
=IF(B2>0,A2+B2-C2,"Error")
User 17/12/2017 - 11:42
Is this formula correct as well?
Excelchat Expert 17/12/2017 - 11:42
Nope this is a different formula
Excelchat Expert 17/12/2017 - 11:43
This will be of no use to you in this current scenario
User 17/12/2017 - 11:43
Thanks
Excelchat Expert 17/12/2017 - 11:43
You are welcome !
User 17/12/2017 - 11:43
The SUM formula seems fine
Excelchat Expert 17/12/2017 - 11:43
Yes interesting formula
User 17/12/2017 - 11:44
Many thanks and have a nice day!
Excelchat Expert 17/12/2017 - 11:44
For every cells you want to add , use the cell number within the brackets
Excelchat Expert 17/12/2017 - 11:44
Thank you Sir
Excelchat Expert 17/12/2017 - 11:44
Have a nice day to you too!
User 17/12/2017 - 11:44
Tell me more about the brackets
Excelchat Expert 17/12/2017 - 11:44
=SUM(A1,A2,A3) will sum A1 A2 and A3
User 17/12/2017 - 11:45
Yes
Excelchat Expert 17/12/2017 - 11:45
SImilarly =SUM(A1,A2,A3,A4) will sum A1,A2,A3,A4
User 17/12/2017 - 11:45
and if there are 4 deductions
Excelchat Expert 17/12/2017 - 11:46
In the same way as in =SUM(C2,C4)-C6-C7
Excelchat Expert 17/12/2017 - 11:46
add cell reference after C7
Excelchat Expert 17/12/2017 - 11:46
The series would continue to include four deductions
User 17/12/2017 - 11:47
=SUM(c2,c4)-(c6,c7) will this work
Excelchat Expert 17/12/2017 - 11:47
Definiterly
Excelchat Expert 17/12/2017 - 11:47
It will not work
Excelchat Expert 17/12/2017 - 11:48
As the bracket is for use with functions only
Excelchat Expert 17/12/2017 - 11:48
for C6,C7 you are not declaring any function in the same way you are doing for =SUM(C2,C4)
User 17/12/2017 - 11:48
and if C6 is changed to 0 will the formula still work
Excelchat Expert 17/12/2017 - 11:48
Yes it will still work
Excelchat Expert 17/12/2017 - 11:49
Nothing changes for 0
User 17/12/2017 - 11:49
Thanks my friend
Excelchat Expert 17/12/2017 - 11:49
Thank you for choosing Excel Chat
Excelchat Expert 17/12/2017 - 11:50
Is there anything else you want to know regarding this issue?
User 17/12/2017 - 11:51
Can I ask you one more question please
Excelchat Expert 17/12/2017 - 11:51
Yes please
Excelchat Expert 17/12/2017 - 11:51
Yes please
User 17/12/2017 - 11:52
Can you please give me a simple formula for the debits credits
User 17/12/2017 - 11:52
IF formula
Excelchat Expert 17/12/2017 - 11:52
What formula
Excelchat Expert 17/12/2017 - 11:52
IF formula doesn't work everywhere
Excelchat Expert 17/12/2017 - 11:52
What formula do you need?
User 17/12/2017 - 11:53
If it is debit it is minus and if it is credit add
Excelchat Expert 17/12/2017 - 11:53
The formatting is not correct and no formula will work in the way you have formatted the debits credit total table
Excelchat Expert 17/12/2017 - 11:54
I will try formatting your table
User 17/12/2017 - 11:57
=IF(B3>0,D2-B3+C3,D2+C3)
Excelchat Expert 17/12/2017 - 11:57
It will work for a single cell
Excelchat Expert 17/12/2017 - 11:57
But not in the way for Total Column
Excelchat Expert 17/12/2017 - 11:58
Where do you want to put the above formula in
Excelchat Expert 17/12/2017 - 11:58
Show me in the document preview window
User 17/12/2017 - 11:58
in the total column
Excelchat Expert 17/12/2017 - 11:59
Please show me in document preview
Excelchat Expert 18/12/2017 - 12:04
I got a solution
Excelchat Expert 18/12/2017 - 12:07
Please check the formula I have used
Excelchat Expert 18/12/2017 - 12:07
=IF(ISBLANK(J6),I6,J6)
Excelchat Expert 18/12/2017 - 12:08
In the first row I have used this
Excelchat Expert 18/12/2017 - 12:08
=IF(ISBLANK(J7),K6-I7,K6+J7)
Excelchat Expert 18/12/2017 - 12:08
In the second row I have used this
Excelchat Expert 18/12/2017 - 12:09
Are you there?
Excelchat Expert 18/12/2017 - 12:09
Will you please check
User 18/12/2017 - 12:09
yes
Excelchat Expert 18/12/2017 - 12:09
Do you want me to check the formula?
Excelchat Expert 18/12/2017 - 12:09
Or is it fine?
User 18/12/2017 - 12:09
if you can please
Excelchat Expert 18/12/2017 - 12:09
Also inform me whether you understood the formula
User 18/12/2017 - 12:09
No
Excelchat Expert 18/12/2017 - 12:10
The formula is fine and working in sync with your manual calculations
User 18/12/2017 - 12:11
Can you please send me your sheet
Excelchat Expert 18/12/2017 - 12:11
I am working int document preview window only
Excelchat Expert 18/12/2017 - 12:11
I can download and send you the sheet if you want
User 18/12/2017 - 12:12
i cannot see any formulas on the preview sheet
Excelchat Expert 18/12/2017 - 12:12
Check Column L you will find the formulaes listed there
Excelchat Expert 18/12/2017 - 12:12
You need to double click to see formulaes
Excelchat Expert 18/12/2017 - 12:12
Check Column L
Excelchat Expert 18/12/2017 - 12:12
Double click to see formula
Excelchat Expert 18/12/2017 - 12:12
anyways I have mentioned the formulae above right?
Excelchat Expert 18/12/2017 - 12:15
Formulae in Column E to
Excelchat Expert 18/12/2017 - 12:15
Simply drag the formula downwards to work for more entries in table
Excelchat Expert 18/12/2017 - 12:15
Are you able to follow?
User 18/12/2017 - 12:15
How do I save the preview sheet
Excelchat Expert 18/12/2017 - 12:16
Auto Saved in your drive
Excelchat Expert 18/12/2017 - 12:16
You can download from your drive
Excelchat Expert 18/12/2017 - 12:16
Alternatively I can send you the sheet
User 18/12/2017 - 12:16
please send one
Excelchat Expert 18/12/2017 - 12:16
Here you go
[Uploaded an Excel file]
Excelchat Expert 18/12/2017 - 12:18
Does that work for you?
User 18/12/2017 - 12:18
Thanks a ton
User 18/12/2017 - 12:18
Great help!
Excelchat Expert 18/12/2017 - 12:18
You are welcome
User 18/12/2017 - 12:18
Bye
Excelchat Expert 18/12/2017 - 12:18
Is there anything else you want to know regarding this issue?
User 18/12/2017 - 12:18
No thanks
Excelchat Expert 18/12/2017 - 12:18
Thanks for using Got It Pro-Excel Please give your kind feedback for our service Have a good day ahead!
User 18/12/2017 - 12:18
You too!
Excelchat Expert 18/12/2017 - 12:19
You may want to end the session now
User 18/12/2017 - 12:19
Yes please
Excelchat Expert 18/12/2017 - 12:21
You may click on End Session to end the session

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc