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.
