Tag Archives: Excel

MINIF and MAXIF in Excel 2003 or 2007

July 29, 2009MicrosoftExcel, Tips

Like COUNTIF and SUMIF, it is helpful to have a conditional MAX or MIN. Excel doesn’t have it for now (yeah, “for now”, ‘coz MS has steadily been adding such useful functions with every new release).

Well, the good news is that there is a work-around.

MAXIF
The approach is to have an array formula on the IF column returning a bool, and multiplying it with the column from which MAX is to be found.

Say, we need to find the MAX in column F based on a condition on Column A and K5 having the compare value. We can do this as below.

=MAX((A2:A30=K5)*(F2:F30))
Since it is an array formula, do a Ctrl+Shift+Enter and it’ll become {=MAX((A2:A30=K5)*(F2:F30))}. Do NOT manually add the braces.

MINIF
This is very similar to what we did in the above activity. Just replace MAX with MIN.

=MIN((A2:A30=K5)*(F2:F30))

Again, this is an array formula and need to do Ctrl+Shift+Enter.

Note that empty cells in column F might break this formula.

EMI Calculation Explained – Excel

July 26, 2009MicrosoftEMI, Excel, Finance, Tips

EMI Calculation is a rather complex math with the denominator being the integration of a series (or something of that sort). It is fairly complex to take a pen and paper and calculate. Well, that is if you do not have access to Excel or a similar spreadsheet. In a spreadsheet, it is a very straight-forward calculation to arrive at an EMI.

In this article am explaining how to calculate EMI and amortization schedule of your loan using MS Excel. Also, you can see by changing the EMI how your tenure changes. I’ve attached a sample sheet here with the calculation.

You can download it here: EMI Explained

This is the EMI formula in Excel
=PMT(D4/12,D6,-D3)
Where
D4 has the per annum interest
D6 has the tenure in months
D3 has the loan amount.

So what if your loan is quarterly diminishing (or compounding, as they say) even though you are paying monthly. In that case the formula changes a bit.
=PMT(D4/4,D6,-D3)/3
Where
D6 has the tenure in quarters (i.e. # of years multiplied by 4)
D3 and D4 remain unchanged.

Note the interest changed from D4/12 to D4/4 and tenure from Years * 12 to Years * 4. Also, there is a division by 3 at the end to make it monthly. Similarly you can extend it to any compounding interval.

With slight changes you can use this to calculate the return of a SIP, assuming an average return over the tenure you plan to invest.

The attached sheet has more than just the EMI formula. It’ll generate the amortization schedule for you, given a tenure so that you can play with the EMI amount to reach at a preferred tenure.

Update: Came to know later that Microsoft has similar template for amortization schedule and loan analysis. You can get it here.

Excel VBA Tips & Tricks

February 18, 2009MicrosoftExcel, VBA

[adsense slot=”5402167102″ width=”468″]

Am consolidating here some commonly required Excel VBA snippets in an FAQ form.

How to find the last row in a Worksheet?

    Dim LastRow As Integer, LastColumn As Integer

    'Option 1: Start from A1, and look at the first empty row occurance downwards
    LastRow = SheetName.Cells(1,1).End(xlDown).Row

    'Option 2: Start from A65000, and look at the first empty row occurance upwards
    LastRow = SheetName.Cells(65000,1).End(xlUp).Row

    'Needless to say, you have to pick a column having values throughout
    'Similarly, last column can be found too. Note the To in xlToRight
    LastColumn = SheetName.Cells(1,1).End(xlToRight).Column

Dim LastRow As Integer, LastColumn As Integer

'Option 1: Start from A1, and look at the first empty row occurance downwards

LastRow = SheetName.Cells(1,1).End(xlDown).Row

'Option 2: Start from A65000, and look at the first empty row occurance upwards

LastRow = SheetName.Cells(65000,1).End(xlUp).Row

'Needless to say, you have to pick a column having values throughout

'Similarly, last column can be found too. Note the To in xlToRight

LastColumn = SheetName.Cells(1,1).End(xlToRight).Column

How to clear the clipboard after a copy/paste operation?

    'Add the below line after a copy/paste operation to clear the excel clipboard
    'Incidentally, this will also avoid the alert while closing a sheet
    'asking if clipboard should be discarded
    Application.CutCopyMode = False

'Add the below line after a copy/paste operation to clear the excel clipboard

'Incidentally, this will also avoid the alert while closing a sheet

'asking if clipboard should be discarded

Application.CutCopyMode = False

Why am I not able to copy a range?

    'For copy/paste to work, you should activate the sheet first
    SourceSheetName.Activate
    SourceSheetName.Range("A1:H50").Copy
    TargetSheetName.Activate
    TargetSheetName.Range("B1:I50").PasteSpecial xlPasteValues

'For copy/paste to work, you should activate the sheet first

SourceSheetName.Activate

SourceSheetName.Range("A1:H50").Copy

TargetSheetName.Activate

TargetSheetName.Range("B1:I50").PasteSpecial xlPasteValues

If I get a Column number, how can I get the Column alphabet?

    'First the function
    Function CChr(ColumnNumber As Integer) As String
        Dim FirstChar As Integer
        FirstChar = (ColumnNumber - ColumnNumber Mod 26) / 26
        If FirstChar > 0 Then
            ColumnNumber = ColumnNumber Mod 26
            CChr = Chr(64 + FirstChar)
        End If
        CChr = CChr & Chr(64 + ColumnNumber)
    End Function

    'Now you can call the function elsewhere in the code or worksheet
    LastColumn = SheetName.Cells(1,1).End(xlToRight).Column
    Set HeaderRow = Range("A1:" & CChr(LastColumn) & "1")

'First the function

Function CChr(ColumnNumber As Integer) As String

Dim FirstChar As Integer

FirstChar = (ColumnNumber - ColumnNumber Mod 26) / 26

If FirstChar > 0 Then

ColumnNumber = ColumnNumber Mod 26

CChr = Chr(64 + FirstChar)

End If

CChr = CChr & Chr(64 + ColumnNumber)

End Function

'Now you can call the function elsewhere in the code or worksheet

LastColumn = SheetName.Cells(1,1).End(xlToRight).Column

Set HeaderRow = Range("A1:" & CChr(LastColumn) & "1")

Is there something else you would like me to add here? Just leave a comment.

Excel Default Colors for VBA

February 16, 2009MicrosoftExcel, VBA

This is a handy reference list for the default Workbook.Colors list in excel.

excel-colors-vba

In case you are interested in generating the same list, here is the code. ‘Colors’ here refer to the sheet name and you may have to replace it with a relevant value (say, Sheet1).

Sub ColorMe()
    Dim RowCount As Integer
    Dim R As String, G As String, B As String
    For RowCount = 2 To 57
        Colors.Cells(RowCount, 1).Interior.ColorIndex = RowCount - 1
        SplitColors ThisWorkbook.Colors(RowCount - 1), R, G, B
        Colors.Cells(RowCount, 1) = RowCount - 1
        Colors.Cells(RowCount, 2) = R & G & B
        Colors.Cells(RowCount, 3) = Val("&H" & R)
        Colors.Cells(RowCount, 4) = Val("&H" & G)
        Colors.Cells(RowCount, 5) = Val("&H" & B)
    Next
End Sub

Sub ColorMe()

Dim RowCount As Integer

Dim R As String, G As String, B As String

For RowCount = 2 To 57

Colors.Cells(RowCount, 1).Interior.ColorIndex = RowCount - 1

SplitColors ThisWorkbook.Colors(RowCount - 1), R, G, B

Colors.Cells(RowCount, 1) = RowCount - 1

Colors.Cells(RowCount, 2) = R & G & B

Colors.Cells(RowCount, 3) = Val("&H" & R)

Colors.Cells(RowCount, 4) = Val("&H" & G)

Colors.Cells(RowCount, 5) = Val("&H" & B)

End Sub

And wait, SplitColors is not a built in function. This is a small piece of code that takes in a Long color and returns 3 hex strings. Basically, a long to hex converter for a color. That one follows.

Function SplitColors(RGB As Long, ByRef R As String, ByRef G As String, ByRef B As String)
    Dim sRGB As String
    sRGB = Right("000000" & CStr(Hex(RGB)), 6)
    R = Left(sRGB, 2)
    G = Mid(sRGB, 3, 2)
    B = Right(sRGB, 2)
End Function

Function SplitColors(RGB As Long, ByRef R As String, ByRef G As String, ByRef B As String)

Dim sRGB As String

sRGB = Right("000000" & CStr(Hex(RGB)), 6)

R = Left(sRGB, 2)

G = Mid(sRGB, 3, 2)

B = Right(sRGB, 2)

End Function

This is not a tutorial on how to use ColorIndex to set colors using Excel VBA. For details on that, take a look at this.

code.Snip

bits and bites. byte a piece.

Tag Archives: Excel

MINIF and MAXIF in Excel 2003 or 2007

EMI Calculation Explained – Excel

Excel VBA Tips & Tricks

Excel Default Colors for VBA