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

HelloWorld-Macro

Hello World Macro

This would easily be the easiest first macro for anybody to create, especially if one is just beginning to learn how to program macro's in the Visual Basic Editor in Microsoft Excel (Any version). The first step is to create a workbook and call it "Hello_World_Macro.xlsm" and save it to your computer in your favoured location, usually "My Documents".

STEP 1: Create a Shape or WordArt:

Refer to Figure 1 above, in this case I have used a WordArt Text on the worksheet from where I want to execute the macro.

STEP 2: Add the following code:

Sub Hello_World()
'---------------------------------------------------------------------------------------
' Procedure : Hello_World
' Author    : vsmathur-onms
' Date      : 17/10/2019
' Purpose   :
'---------------------------------------------------------------------------------------
'

   On Error GoTo Hello_World_Error

    MsgBox "Hello World !!", vbOKOnly

Hello_World_Exit:

    On Error GoTo 0
    Exit Sub

Hello_World_Error:

    MsgBox "Error " & Err.Number & " on Line # " & Erl & " (" & Err.Description & ") in procedure Hello_World of Module m_HelloWorld"
    Resume Hello_World_Exit


End Sub

STEP 3: Assign Macro and Click to Execute Macro

You can right-click the WordArt created in Step 1 above and select "Assign Macro" - in the dialog box that opens, select the Sub called "Hello_World". Finally, click the WordArt and you should see a Message Box appear with "Hello World !!" written in it. There, that is all that there is to creating your very first Macro!! Cheers !!

CA Vikram S. Mathur17-Oct-2019 | 10:15 Hours IST

Beginning to learn VBA

Beginning to learn VBA

CA Vikram Shankar Mathur

This document should get you started with how to program in the Visual Basic for Applications (VBA) in Microsoft Excel and the other MSOffice365 products straight off. Cheers !

So,  what is VBA?

Visual Basic for Applications (VBA) Introduced:

When you are in any of the three applications mentioned above, and specifically in Microsoft Excel, pressing [Alt]+[F11]  would bring you into what is called the VBE (Visual Basic Editor) window, where you would see what would look somewhat like the figure below.

Visual Basic Editor (VBE)

Want to insert a picture from your files or add a shape, text box, or table? You got it! On the Insert tab of the ribbon, just tap the option you need.

The ‘elusive’ Developer Tab

Oh, before I forget, you will need to make the ‘Developer’ tab visible in your Microsoft Office (Excel/Word) application, if you wish to quickly access the VBA Window from the main application. To do this, you need to access [File->Options->Customize Ribbon] to reach the screen depicted below:

Why Learn VBA?

Basically, VBA is not meant for the weak-hearted or the person who wants thing-done-0in-a-jiffy!! To elucidate the point, it took me a good 15+ years to evolve to the point that I could consider myself worthy of making this first post on any of my blogs. Admittedly, I got a hefty kick-start in the start of my career in 1989, when my boss insisted that I prepare all my documentation to be submitted to him on the computer. At that time, we only had WordStar as the word processor, Lotus 1-2-3 as the spreadsheet and dBase/FoxBase as the database programs.

Well, let me not digress here, to conclude this post, I evolved into the Microsoft Excel’95 world without much effort, thanks to my experience of Macro-Programming in Lotus 1-2-3. The real question here, however, is where do you (the layman reading this blog, not the experts who read it for whatever reason 😊 😊 ). I will answer that question in my next post, where I will go one step ahead in getting you stated on actually writing your first “Macro” in either Microsoft Excel or Microsoft Word.

Thanks for reading so patiently.

CA VIKRAM S. MATHUR

03-Sep-2019 | 13:50 Hours IST