20191118-Running-Totals-Formulas-Excel-Worksheet

20191118-Running Total Formula in Microsoft Excel Worksheet for Computation of Total Income of an Assessee

I have already made a similar post in my Wordpress Blog as VBA Beginners Guide - (November 2019), but here is a more refined version of the same with detailed explanations about the need to create such a running formula.

What is a "Running Total" and when is it significant in an Excel Worksheet?

The above image is the last part of the image that was added to the final image right at the top of this page, replicated here to ease the explanation of why a "running total" can be required in an excel worksheet. Suppose you had a worksheet, as in my case it is a worksheet that computes the Total Income of an Income-Tax Assessee for the Financial Year ended 31-March-2019, wherein the various allowed heads under Section 80C, namely, "Public Provident Fund Contribution", "Life Insurance Premium", "Children's Education Tuition Fees" etc. are entered from Row Number 40 through to 47, in Column E of the worksheet, the running total being calculated in the Column F (Qualifying Deduction), that is, the column immediately following the entry column E (Gross Deduction). As you can see from the figure above, the starting row has been frozen, as in E$40 (semi-absolute reference restricted to the row only). Don't worry about the formula itself, it is replicated in a copy-able format at the end of this post, so just follow along with the narative at the moment.

Therefore, freezing the row in the formula achieves the desired result of a "Running Total" in the sense that it can be copied as it is from Cell E40 down to Cell E47, giving us the desired result. Now as to why it is required, I wanted to have an idea how much of each amount entered in the Gross Deduction Amount was going to be allowed as a Qualifying Deduction Amount, so that a greater analysis of the same could be done while I was entering the details of the dedudction(s) per assessee.

Discussion about the Break-up of the formula itself

The first part of the formula [=IF(E47=0,0,IF(SUM(E$40:E47)=0,0] is easy to understand. If the entered Gross Deduction amount in cell E47 is zero, then obviously the Qualifying Deduction amount in cell F47 has to be zero. Since the construction of an "=IF(" formula is of the type "=IF(<Condition>, <True Part>, <False Part>)" the <Condition> being "E47=0",  the <True Part> being "0" and the <False Part> beginning as "IF(SUM(E$40:E47)=0,0".

Before I progress further, an explanation of "Sec80CLimit" is required, for it comes up in the next part of the formula. Elsewhere in my worksheet, I have defined a Range Name as Sec80CLimit, which bears a value of Rs. 1,50,000/-, the current maximum allowable limit under Section 80C of the Income-Tax Act, 1962. A similar logic follows the second part and third part of the formula. Essentially, you need to view the formula in basically three parts (<Condition>'s).

The first part (<Condition>) being if the entered Gross Deduction amount is Zero (E47=0), the second part (<Condition>) being if the entered Gross Deduction amount is less than or equal to the Sec80CLimit defined above (SUM(E$40:E47)<=Sec80CLimit) and the last part (<Condition>) being if the entered Gross Deduction Amount is greater than the Sec80CLimit (IF(SUM(E$40:E47)>Sec80CLimit).

And finally, the entire formula
[=IF(E47=0,0,IF(SUM(E$40:E47)=0,0,IF(SUM(E$40:E47)<=Sec80CLimit,E47,IF(SUM(E$40:E47)>Sec80CLimit,Sec80CLimit-SUM(F$39:F46),0))))] 
is replicated in a copy-able format, as promised above.

CA Vikram Shankar Mathur
18-Nov-2019 | 00:10 Hours IST