**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.*