This page reviews the few functions that you need for developing all sorts of things in excel analyses ranging from project finance models to corprorate finance models to database analysis to statistical analysis. I maintain that just about all you really need are the following functions. These include:

1. The INDEX Function for scenario selection, database selection, data item selection, quick graphs

2. The LOOKUP Function (please, not the vlookup or hlookup) that finds data depending on defined selection criteria

3. The SUMIF or AVERAGEIF Functions that allow for quick data presentation, reporting and error checking

4. Sometimes the MATCH Function that can be used with the the INDEX function resulting in the famous INDEX/MATCH.

The functions are often most effective when the arguments for the function (the inputs to the function) are an entire row or column. For example, the MATCH function can find a row number if an entire colum is selected; the INDEX function can select a number in the whole column if you provide a column number; the LOOKUP function can be operated in a simple manner by an entire row of items to look up and keys for the lookup. Finally, the AVERAGEIF and the SUMIF functions can be used with enitre columns of data to very quickly give you the average for a year or quarter or month if there is detailed data.

Using the principle that you should look for the laziest way to do things in excel (that is about the only best practice), I think the LOOKUP is much better than INDEX/MATCH. You only put in one function and get exactly the same result.

INDEX Function:


INDEX Function for Scenario (should be named SELECT Function)


- A very common use of the INDEX function is in creating scenarios. The INDEX function should named SELECT -- that is why it so useful for scenario analysis. Other common uses are selecting something to graph from a database or selecting a row to graph from an entire sheet. The INDEX function needs either a row or a column number or both to make the selection. In the case of scenario analysis, I use a code number. The scenarios can be established in different rows and then the INDEX function can be used to select the scenario that will be operational in the model. This is illustrated in the excerpt below.

- To use the INDEX function for scenarios, you need a code number (a row number) which is just the row number for the selecting one of the scenarios.

- If you use the drop down box from the DEVELOPER tab, it will almost always be used with the INDEX function.

- A good way to use the INDEX function is to select a row and then refer to the scenario number as illustrated below.


INDEX Function.PNG

INDEX Function for Making a Quick Graph of Everything on a Page (through selecting a particular row)


To graph the data in a flexible manner the INDEX function is effective. This example illustrates how you can be more efficient when you use the entire row or column. When you select the entire row, the INDEX function will have something like "C:C" in the exceprt below. Generally, you do not have to press the F4 function and this is a very quick way to make analysis.

To make a graph on anything on a page, first enter a row number for the graph (this is like the scenario number) in the previous example. Then, type the INDEX function and first click on the entire column (generally from another sheet, meaning you make the graph in a separate page). After this, copy the INDEX function across the page and make sure you lock-in (with the F4), the row number as shown below.

After making the INDEX function, put the year above the INDEX function year as illustrated below. Make sure that you do not put anything in the first column that has the title (again from the INDEX function that should be copied across the page. Finally, press F11 to make the graph.


Index and Quick Graph.png


LOOKUP Function:


The LOOKUP function uses a single value from the model and then associates that variable with a stream of variables, in our case a row of years. Once the lookup-value (the single year) and the stream of years are defined, the values associated with the years is selected. You should never again use VLOOKUP or HLOOKUP if you use the TRUE switch at the end. You should also always try to arrange you inputs so that you can use an entire row or column (see the excerpt below). Finally, if your are working with things like dates that are in ascending order, you should NEVER use INDEX/MATCH again. To use the LOOKUP function with an entire row you can do the following:

- Enter the LOOKUP function
- Define the LOOKUP value – a single number that is often a date or a period number. Note that the lookup only works when the dates or periods are increasing.

- Enter a string of values – a whole row – that corresponds to the LOOKUP VALUE. For example, if the LOOKUP VALUE is a date, then the entire row of values that is the second argument should have some dates that greater than or equal to the first value.

- Enter a string of values – a whole row – that contains the numbers that you want to find and that is associated with the string of values.

lookup.JPG


- I have created a very similar function to the lookup function, called the INTERPOLATE function. This function works in a very similar way, but instead or stepping up or down, the amounts are interpolated. You can go to the UDF page to see how this is build and how it works in more detail.



Capture.JPG





- Using the LOOKUP and AVERGAEIF Function

- If the data is in a daily format and you want a monthly or yearly format, you can use the LOOKUP function and the AVERAGEIF function
- To use this, you must first insert a column for the year or the end of month with EOMONTH

- Then if you want average
- First, use EIS to create a year column

SUMIF, COUNTIF and AVERAGEIF Functions:


- Then use the AVERAGE if and first click on the detailed columns of years


- Then click on the year in the year column for the criteria


- Then click on the entire column of the data