Avatar of Jegajothy vythilingam
Jegajothy vythilingamFlag for United States of America

asked on 

Excel 2016 formula

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.
Microsoft ExcelMicrosoft OfficeSpreadsheets

Avatar of undefined
Last Comment
Jegajothy vythilingam
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

You Don't need if formula in this case You need a simple formula in E4 which will be..


Avatar of Excel amusant
Excel amusant

Here is the if formula.


Please see attached example.

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

Avatar of Excel amusant
Excel amusant

Blurred text
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.
See Pricing Options
Start Free Trial
Excel amusant,

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


Does the same what your if formula does??

Avatar of Excel amusant
Excel amusant


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.. :-)

Avatar of Doug
Flag of United States of America image

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


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.

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


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

Avatar of Jegajothy vythilingam


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

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

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo