Functions: CHOOSE

As part of Excel ‘Lookup & Reference’ functions, we have already seen few details about LOOKUP & VLOOKUP in previous posts. In this post let’s see one more useful function under this category i.e. CHOOSE.

CHOOSE returns a value from list given as argument based on the position of the value in the list. For example, if you have 10 values in a list and want the value in 8th position, you can use CHOOSE function. Function syntax is below.

=CHOOSE (Index_num, value1, [value2],…..[value254])

Index_num: Is the position you want to return the value from.

• If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on. 
• If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error. 
• If index_num is a fraction, it is truncated to the lowest integer before being used. 

Value1 to 254 are the list items. Value1 is mandatory and all others are optional. So you can provide upto 254 values to choose from. Note that the limit is 29 if you are using Excel 2003 or earlier versions.

Example: Let’s see a practical example. One of my clients runs a business. He uses 2 rebate tables for his customers. Table 1 is for normal customers. Table 2 is for frequent / premium customers. Both rebates are based on the sales value, but % of rebate is more in Table 2. Here are the tables.

Now when he bills, he has a field to select if the customer should be given rebate under Table 1 or Table 2. What he wants now is when he selects the applicable table number, the rebate % should be auto picked. We already know that we can use VLOOKUP to pull out the rebate but that only works with one array. Since we have 2 tables, we have to use nested formula using IF and VLOOKUP functions. Sales amount is in cell B10. I need rebate % in D10. Formula looks like this.

This works pretty well within the given example. However what if you have multiple numbers of tables and multiple conditions to check with. You have to nest series of IF functions one in another and add that many LOOKUP functions. Your formula becomes gigantic and even you won’t understand it on a second look. An easy way around is CHOOSE function. Formula looks like this.

You just need to enter your table ranges inside CHOOSE function as values and let VLOOKUP pick the correct one based on the table to be applied.

Let’s analyse our function. VLOOKUP looks for sales which is in B10 and as a second argument CHOOSE function will decide which range VLOOKUP to look in. Based on the table number selected in C10, it picks either A2:B7 (first table) or D2:E7 (second table). In our fist example, we selected table number 1 so A2:B7 becomes the second argument for VLOOKUP. It returns 2nd column from the array which is rebate %. As a last argument for VLOOKUP we gave 1 i.e. approximate match.

Like most of the Excel functions, CHOOSE is more powerful when combined with other functions like VLOOKUP, SUM, MATCH etc. CHOOSE function looks less messy if you give names to your tables and use named ranges. We will talk more about named ranges in future posts. Happy learning!!

Functions: VLOOKUP

Hello. I am starting the new year with a post on VLOOKUP function. VLOOKUP is one of the most used Excel 'lookup & reference' functions in my experience. Though many of my friends and colleagues are having difficulty in understanding how to use this function, it's fairly simple once you understand the syntax and dirty your hands with couple of practical examples. Let's dive into VLOOKUP.

Introduction

VLOOKUP searches for a lookup value in first column of a selected array and returns a value in the same row of another column. V stands for vertical and it's fairly understood as this function works on columns. Function syntax is below.

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value: Is the value you are trying to search for.

table_array: Is the set of columns where you are searching the lookup_value in and want to retrieve corresponding value. In this array, lookup_value will always be searched in the first column.

col_index_num: Is the column number in table_array starting with first column of the array as 1 and counting towards right. This must always be a number equal to or more than 1. If you do not provide a number or provide a number less than 1 or provide a number which is greater than the number of columns in table_array, VLOOKUP results into an error.

range_lookup: This is optional. Is a logical value that specifies if you want to look for an exact match of lookup_value or an approximate match. Since this is logical value, only options available are either ‘True’ or ‘False’. In Excel language, True = 1 and False = 0. Hence you can use 1 and 0 also for this option. If you want an exact match, type 0 (zero) or false, else if you are fine with an approximate match then leave this option blank or type 1 (one) or True. Approximate match in this case means the next largest value that is less than the lookup_value. Point to note here is that if you want an approximate match, your first column must be sorted in ascending order just like in LOOKUP function.

Points to remember:

1. Make sure that lookup_value and table_array values contain the same formatting to the cells. I mean to say if lookup_value is stored as text and table_array is stored as numbers, though the value is available in table_array, VLOOKUP may give an incorrect result.
2. Also extra spaces or line break characters will not be visible to the naked eye but VLOOKUP sees it. So seemingly same cell values may not be same always.
3. You can use wild characters for lookup value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
4. You cant't have lookup_value appearing 2 or more times in the first column of table_array. Only the first instance will be considered by standard VLOOKUP. You will have to tweak the original function to work in these cases.
5. Table_array left most column should contain the data you are looking for.
6. Use absolute references in the functions, this is handy when you drag down the function to other cells.
7. If you use 1 or True or omit the last argument (to get approximate match), you MUST sort first column of Table_array in ascending order.

Example: Let's see an example to clearly understand how to use this function. For simplicity sake, I have my data in one workbook. So I have a price list data base for different products. What I want is that when I create a bill with some products, it's price should be auto picked. Let's see how we do it using VLOOKUP.

Price List:

Bill: I need price for product A to F. Here are the formulas I use under the price column.

Let's analyse the formula. For product A, I used VLOOKUP to lookup value in D3 which is "A". Lookup to be performed in Array A3 till B17 that is the place where my price list is available. I need the corresponding result from column 2 of the selected array which is where the price is entered. And since I need exact match for the product, as a last argument, I entered 0 (zero, remember you can also enter FALSE in place of 0). Excel did good job in finding the price 100. I checked this formula result and was happy. To fill the balance product prices I just dragged down the formula in below cells.

Now notice the formula for product B, second argument array is now A4:B18 instead of A3:B17 and so on for other products. Since my price list is in the same order as bill, Excel will still be able to return the correct result but most of the times your data wont be arranged this way and VLOOKUP returns error. Above formula is called relative reference whereas what you need is an absolute reference formula to keep the table array unchanged when you drag the formula below. To use absolute reference, after entering the VLOOKUP formula in first cell (i.e. product A in our example), edit (press F2) the formula. Select A3:B17 and press F4. Doing this places $ (dollar) symbol in front of each row and column reference in the formula. This tells Excel not to change the reference when the formula dragged down below. Updated formula looks like below. Now drag the formula to other cells and notice for product B and so on.

This is an example if you are looking for an exact match. But sometimes you may not want an exact match but an approximate match. One of such cases is while calculating tax. Let's see how VLOOKUP works in this case.

So I have tax rates in a workbook like below. For simplicity I used current year tax rates.

For an income of 2 lakhs we don't have any tax. For income crossing 5 Lakhs, we have minimum 10% of 3 Lakhs which is 30,000 plus 20% of income more than 5 Lakhs. So in above picture, minimum tax is the tax calculated if the income has crossed the limit on left side.

Once we make this table ready, calculating tax for any income using VLOOKUP is easy. I have income of my client in cell D1. If I just want to find out which tax bracket my client falls in, I use the below formula.

=VLOOKUP(D1,$A$2:$C$6,3,1)

I took a random income 9,38,938 and the formula show a result 20%. Let's see the function syntax. Lookup value is in D1, I have to look to find in A2 to C6 and used absolute references. I want to return value from 3rd column in array which contains the tax rate and last argument is 1 to say I want an approximate match (remember this argument is optional, you can use TRUE or simply leave out this argument in this case). Though my random income is not available in tax table, VLOOKUP was able to retrieve the correct tax rate for me. In fact if I am looking only to retrieve the tax rate, I don't need the middle column in my tax table. Then why did I add that? My intention is to calculate exact tax payable for that income. To do that, my formula should be something like below.

=VLOOKUP(D1,$A$2:$C$6,2,1)+VLOOKUP(D1,$A$2:$C$6,3,1)*(D1-VLOOKUP(D1,$A$2:$C$6,1,1))

That formula is a bit long but it gives tax payable as 1,17,788 which I checked back and found correct. Let's also see what we are doing in the above formula.

First VLOOKUP looks up for my random income number is tax table and retrieves the applicable minimum tax rate which is second column in the table. We have used 1 as last argument so approximate value will be searched. Result is 30,000. Next VLOOKUP looks for applicable tax rate from 3rd column and result is 20%. Last VLOOKUP finds applicable income bracket from first bracket which is 500000. Once all the VLOOKUPs did their job, it's simple mathematics done by operators placed in between the VLOOKUPs. I leave it to you to figure out the rest.

I have tried the same formula with different income figures as in picture below. Income figures are in column F.

That's it. You can look and find thousands of values in a fraction of seconds using VLOOKUP. However this function suffers from drawbacks. One is that you cannot return a value which is in the left side of array (LOOKUP function supports this). In the coming posts we will see how to tweak VLOOKUP function to work in that case and get more out of it. See you!!

Functions: Working with Arrays

Hi there. This is last post for this year. Let's say bye bye to year 2013 and welcome 2014 with heart full of joy.

As we started working with functions, we now know and has experienced that Excel allows us to perform fairly complex calculations with functions. You have to nest functions within functions to make it a mega function or do the calculations in multiple steps.

Assume a situation where in I have number of cells containing various text strings. I need to find out how many characters are present in all cells put together. What do I do normally is, insert a column next to text strings, enter a formula to calculate length of each cell (I know that Excel provides a formula i.e. LEN to calculate number of characters in a cell including spaces). Copy the formula all the way to bottom cell and enter another SUM formula one cell below the bottom cell to add up all the LEN results. This gives me what I wanted. Pretty straight forward, isn't it?

Right, what if I don't have the flexibility of inserting a row? What if I want to be less clumsy? What if I wanted a faster solution? Answers to these questions introduce us to another useful feature of Excel which is Arrays.

Though standard way of using functions works for many novice users, advanced users feel that working with Arrays are faster and efficient in many ways. Though there are drawbacks to Array functions they are lesser than the benefits. Let's go in to details.

Introduction

Simply put, an Array is a collection of cells. Collection of row cells is called one dimensional horizontal array and column cells collection is one dimensional vertical array. If you have multiple rows and columns, it is called a two dimensional array.

Array formula is a formula that works with arrays. There are multi cell array formulas and array formulas that calculate result in a single cell. Array formula works just like standard formula we enter in Excel. Only difference is that you have enter Ctrl+Shift+Enter whenever you are done with entering formula instead of just enter. So Array formulas are also called as CSE (Ctrl+Shift+Enter) formulas.

Let's take the above LEN example and see how Array formula will make life easier. I have a worksheet that looks like this.

What do I normally do is something like this. (Column B is just to show the formulas)

If I don't want extra column or simply cannot use one more column or doesn't have enough time for all this, I will just use an Array formula. I will select any available cell and enter the below formula.

=SUM(LEN(A1:A14))

Note that I have to enter Ctrl+Shift+Enter after I type the formula. Once done, Excel will show formula like this.

{=SUM(LEN(A1:A14))}

Note the braces entered by Excel. You should not put these braces manually. Result 59 is displayed in a single cell in a fraction of seconds, all that without any additional steps. Simple? Yeah. Thumbs up for Array formula.

Above is an example for single cell array formula. Result is spread across only one cell. Let's see an array formula which produces results that spread across cells. 

Take the same LEN example. Here is a quick recap on what we have. I have cells A1 to A14 filled with text and I need to find out number of characters in each cell and then total of characters across cells. Let's do the below steps.

1. Select cells B1 to B14
2. Enter the formula =LEN(A1:A14)
3. Press Ctrl+Shift+Enter
4. You should be able to see braces entered by Excel and result filled across B1 to B14
5. Enter formula =SUM(B1:B14) in B15 and enter
6. You should see result 59

So without entering or dragging LEN formula across the cells, we are finishing our work with single formula. Step 1 introduces an important concept in Arrays, that is, for multi cell formula, you have to select result cells in advance.

Did you notice the difference in standard usage of function and with Array. Standard function will have a different LEN formula for each cell. Array formula will have same LEN function for each of the cell. Look above picture carefully if you don't believe.

So since we have an understanding of how an array formula works, let's also see about major advantages and disadvantages of this.

Advantages:

1. Speed: We saved at least couple of steps in simple example above. Consider having thousands of rows of data and complex operations.
2. Flexibility: Array formulas offer greater flexibility towards nesting and performing complex calculations.
3. Consistency: Array formula is same across cells. This also ensures greater accuracy.
4. Safety: You cannot overwrite part of multi cell formula. In above example, try changing formula in one cell without selecting other array formula affected cells. Excel throws an error that you simply cannot do it.
5. You already know that multiple calculations makes Excel work slower and increases the file size. Array formula reduces this impact because we will be using fewer formulas.

Disadvantages:

1. You should always finish entering functions with Ctrl+Shift+Enter. If you forget this, your formula becomes normal formula and often Excel complains.
2. Array formulas are not so well known. If you send your file to other users, chances are there that they will be confused.
3. Large array formulas will slow down the system.

Points to remember:

• Always press Ctrl+Shift+Enter with Array functions
• Select all the cells where your result will spread before entering the formula.
• You can't change formula for partial cells in a multi cell array
• You can't insert new rows or columns or cells in between a multi cell array
• You can expand your array formula to include new cells but cannot shrink it. Only way to shrink is to delete existing formula and create a new one with fewer cells selected.

Though there is lot to know about Arrays practically, I will conclude it here for want of space and time. Post a comment to let me know if you have any particular doubts on this subject or want me to cover any specific area in future. 

Happy New Year!! May this new year gives you what ever you wish in life and much more. See you next year!! Bye.

Excel Lookup & Reference functions

Hello. Enough of introduction posts. Let's jump to see how to increase number crunching efficiency in Office which is the purpose of this blog. We will start with most used functions in day to day office work which are lookup and reference functions.

I use lookup functions very frequently. These functions are used to locate a value you are looking for in a set of rows and columns and returns it's address or corresponding value in any other column. 

Excel offers below functions under 'Lookup & Reference' category. (In Excel 2003, 2007, 2010)

• ADDRESS
• AREAS
• CHOOSE
• COLUMN
• COLUMNS
• GETPIVOTDATA
• HLOOKUP
• HYPERLINK
• INDEX
• INDIRECT
• LOOKUP
• MATCH
• OFFSET
• ROW
• ROWS
• RTD
• TRANSPOSE
• VLOOKUP

These functions makes life a lot easier for accountants. I use them for tax calculations, merge monthly budget figures to year end report, We will cover these functions over series of posts from now on. Stay tuned.

Functions: Introduction (2 of 2)

We read a brief about Microsoft Excel built in functions in earlier post. Now let's see a bit more of functions and related stuff in this final post on functions introduction.

If you enter incorrect inputs or do not follow the function syntax correctly, the result would be an error. Let's have a look on few common mistakes people do and how to correct those.

Types of Formula Errors

Though you would take precautions, formulas resulting an error is common most of the times. Let's look at common errors that your function results into. Note that the reasons for errors and how to resolve them are situation dependent hence will be different in different cases.

Now that we know the types of errors and what causes them, we can make sure formulas are entered as required. Next time we will see how to check where the errors occurred and how to debug them. See you then!!

Functions: Introduction (1 of 1)

If you are working on Excel for number crunching or analysis like I do, formulas and functions are the most used part of Excel. In fact, functions are what make Excel interesting. Without them, any spreadsheet is just a boring text editor.

What Is?

Excel formula is a way to perform calculations which always returns a value. Input for the function can be text, numbers, mathematical & logical operators, Boolean values, reference to other cells, named ranges and even other formulas.

Functions are built formulas in Excel that eliminates need to write a complex formula.

A formula may or may not contain a function in it. As said above, result of a formula will always be a value (for example, it means you cannot format a cell by using a formula). Result can be a text, number, Boolean. Excel automatically updates results of formulas whenever a dependent cell is changed.

Function should start with a ‘=’ sign however it works even if you enter ‘+’ (plus) or ‘-‘ (minus). The equal sign tells Excel that the succeeding characters constitute a formula.But be careful, a function starting with ‘+’ (plus) and same function started with ‘-‘are not same and returns different results.

Below are some examples of formulas:

Operator precedence

Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur. If you combine several operators in a single formula, Excel performs the operations in the order ^, * and /, + and -, &, =, < and >. If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication and division operator — Excel evaluates the operators from left to right.

To change the excel default order one will have to use a parenthesis (brackets). For example:

Formula ‘=5+2*3 ‘will calculates to 11 and formula ‘= (5+2)*3’ results 21.

How to access

• By typing function in a cell with operator (=, +, -)
• By typing operator in the cell and select a function in name box dropdown
• By typing function in formula bar
• Clicking on insert function button next to formula bar
• From Formulas tab (Excel 2007) or Insert menu (Excel 2003)
• From buttons available in different tabs (Excel 2007)
• From VBA macros
• From shortcut buttons placed

Function Categories

Excel has built in functions grouped under different categories. Below screenshot gives you quick look of categories in Excel 2003.

Here is another screenshot for categories in Excel 2010.

There are two more categories ‘Cube’ and ‘Compatibility’ which are not shown in above picture because of scroll.

Function Syntax

Built in functions or VBA written functions in Excel requires specific way of input in order to work as desired and not to end up with an error. This structure of the function is called ‘syntax’. One must follow the syntax required by the function. Now we shall see the parts of syntax.

1. We now know that every function must be started with ‘=’ (Equal sign).
2. Name: Type function name or select the correct function from the list (examples: SUM, IF, VLOOKUP)
3. Arguments: There are the inputs required by function. Each argument will have to be separated by a ‘,’ (comma, without quotes). Excel function are 2 types from arguments perspective.
• Function that requires no inputs hence no arguments (Ex: Function TODAY does not require any input but returns today’s date)
• Function that require inputs. (Ex: Function IF require 3 arguments: a logical test, value if true and value if false)
• Function that require at least one mandatory input and one or more optional inputs. (Ex: Function VLOOKUP requires 3 mandatory arguments: lookup value, table array, column index and contains 1 optional argument: range lookup). Function will not result in ‘Error’ even if you do not enter an input for optional argument. However since excel might have default values for optional arguments, formula may not return a value you are looking for in this case.
• ** Optional arguments are enclosed with [ ] in function syntax.
4. Parenthesis (brackets) are used to start function arguments and close the function
5. Comma: Arguments are separated by a comma

Let’s see VLOOKUP function syntax to get an visual understanding of above.

You can nest multiple functions and formulas in one cell and create a mega formula. We will see more about this in upcoming posts.

Click here for list of built in functions available in Excel 2003, 2007 & 2010. (Sorted alphabetical)

We shall look more about functions in the future posts.