Jegajothy vythilingam

asked on

My OS is win 10 pro and I have Excel 2016 with a simple spreadsheet.

Col C is the Credit col, D is the Debit Col and E is the balance col.

In Col c3 I have 3,970; D3 = 0; and in E3 the formula is =sum(c3-D3); thus the result in E3 is 3,970.

What is the syntax for an IIF statement,

Row 3 is the first line of data in the spread sheet.

where if in C3 there is data, then in E3 the formula would be : =sum(c3-d3)

and in subsequent rows, like

if in C4 there is NO data, and in D4 = 300, then in E4, the formula would be E3-d4.

Can I do a single IIF formula for both the scenarios. thank u.

Col C is the Credit col, D is the Debit Col and E is the balance col.

In Col c3 I have 3,970; D3 = 0; and in E3 the formula is =sum(c3-D3); thus the result in E3 is 3,970.

What is the syntax for an IIF statement,

Row 3 is the first line of data in the spread sheet.

where if in C3 there is data, then in E3 the formula would be : =sum(c3-d3)

and in subsequent rows, like

if in C4 there is NO data, and in D4 = 300, then in E4, the formula would be E3-d4.

Can I do a single IIF formula for both the scenarios. thank u.

Last Comment

Jega,

The reason why you don't need an if formula is because if you want running total i.e. credit what comes in and debit what goes out..Then in that case you need to use the formula that i provided as it will give you an updated total on the cell rather then the last entry as what you mentioned if you apply if formula and the value is not blank then it ignores the last balance which you have...

What i mean is if C4 has a value it will ignore E3..Not sure how you going to treat then that E3 in that case...

Saurabh...

The reason why you don't need an if formula is because if you want running total i.e. credit what comes in and debit what goes out..Then in that case you need to use the formula that i provided as it will give you an updated total on the cell rather then the last entry as what you mentioned if you apply if formula and the value is not blank then it ignores the last balance which you have...

What i mean is if C4 has a value it will ignore E3..Not sure how you going to treat then that E3 in that case...

Saurabh...

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Excel amusant,

Help me understand why do you need if formula in this case..Won't this formula...

=(C4+E3)-D4

Does the same what your if formula does??

Saurabh...

Help me understand why do you need if formula in this case..Won't this formula...

=(C4+E3)-D4

Does the same what your if formula does??

Saurabh...

Saurabh,

The OP has requested If formula. Maybe there is a reason behind that.

Also your formula won't work if the OP does not want to update the sum of E column.

The OP has requested If formula. Maybe there is a reason behind that.

Also your formula won't work if the OP does not want to update the sum of E column.

Hmm..I know but i wanted to understand what's the difference between your last post and the original formula that i posted..??

Not between the first post of yours..The reason i'm asking this that you posted the same thing in a different formula..

Your first post is completely different which i agree..but not the last one.. :-)

Saurabh...

Not between the first post of yours..The reason i'm asking this that you posted the same thing in a different formula..

Your first post is completely different which i agree..but not the last one.. :-)

Saurabh...

Checking for ISBLANK would be better since 0 might be legitimate data. The OP sounded like the formula should check whether data existed.

ASKER

In response to the solution, u have understood my problem and your solution is the one that I was expecting for, but just could not remember the syntax, or the logic. Thank u so much for the solution. Very well done, thank u.

jegajothy,

Have you looked into this Solution which i posted earlier which is:-

=(C4+E3)-D4

This solution is essential same what excelamusmant posted later..So not sure what is the difference that you found in the same if you can help me understand..

Saurabh...

Have you looked into this Solution which i posted earlier which is:-

=(C4+E3)-D4

This solution is essential same what excelamusmant posted later..So not sure what is the difference that you found in the same if you can help me understand..

Saurabh...

ASKER

on reflection, your solution looks similar, though I feel comfortable that the cells of C and D have been looked at.

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY

=(C4+E3)-D4

Saurabh...