Relative & Absolute References

In formulas and functions of Excel, we refer to other cell or group of cells as arguments. This is called referring to a range and the range being referred is known as 'reference'. Today we are going to discuss about different types of references that Excel uses. 

Excel accepts cell references in what are called absolute and relative ranges. 

Relative Reference 

Relative cell references are basic references that adjust and change when copied or when using AutoFill. This is the most widely used type of cell reference in formulas. Excel by default keeps the reference as relative. 

For example: You want to get value of cell A1 in cell B1 through a formula. Our formula in cell B1 will be “=A1” (without quotes). Excel interprets the formula as the relative position of cell A1 compared to B1. Since A1 is one cell left to B1, Excel remembers this formula as ‘where ever this formula is copied to, get the value from one cell to the left’. So if you paste this formula in cell B2, Excel places a formula “=A2” (A2 is exactly one cell left to B2, right?). Accordingly, if you paste the formula in cell F25, Excel places a formula “=E25” (also one cell to the left) 

Point here is to remember the relative position of the referenced cell compared to the cell where formula is placed. Let’s see if we got this correct by another quick exercise. 

I have a formula in cell F1 as “=A6”. What will be the formula if I copy and paste this in cell K8? 

You are correct if you said “=F13”. Since A6 is 5 columns to the left and 5 rows down from cell F1, where ever you paste this formula, Excel take the reference as 5 columns to the left and 5 rows down. Calculating the same from cell K8, 5 columns left and 5 rows down will be F13. 

This brings up a question, what if I don’t want to change the reference when I copy the formula to other cells. Read on. 

Absolute Cell Reference 

In those situations where a cell reference must remain the same when copied or when using AutoFill, we use what is called an ‘absolute reference’. Dollar signs are used to hold a column and/or row reference constant. For example, "$C$3" refers to cell C3, and "$C$3" will work exactly the same as "C3", except when you copy the formula, Excel will not change the reference. The trick is, deciding before you copy a formula what cell references you want, relative or absolute. 

Absolute cell reference can be used when a particular cell reference needs to be constant. Cell references are often used in formulas, charts, functions and other commands. In some scenarios, it becomes important to keep a cell reference constant when it is copied to a different cell. 

So in our first example where you want to get the value of cell A1 in B1, if you use “=$A$1” in cell B1, no matter where you copy this formula to, Excel will always keep the formula same and always retrieve value from row 1 of column A.

How will you place absolute cell reference in formulas? After entering formula in a cell, you can either type $ symbol in front of row and column reference or there is a shortcut for placing absolute cell references in your formulas! When you are typing your formula, after you type a cell reference - press the F4 key. Excel automatically makes the cell reference absolute.

Example

See the example below. I have rebate % in cell B1 and want to apply this on sales value to find out rebate. Look at the below picture when I use ‘relative reference’ in cell C4 and copied over till C9.

Notice the B1 changed to B2 in cell C5. It kept on changing till C9 and my formula result is not what I expected. Let’s use absolute reference and see what happens. Look at the picture below. 

Just the way we wanted, right? We also have another example under VLOOKUP post. So depending on the situation, you have to choose relative or absolute references. Otherwise results may not be what you expect.

Alright, I got another question. What if I want only row to be relative and column to be absolute or the other way around? Excel facilitates this hybrid functionality too. By continuing to press F4, Excel will cycle through all of the absolute reference possibilities. See below for a summary of the same. 

A common use of mixing absolute and relative range specifications is to create a running total of a column of numbers. See below example of using mixed references. 

For example, if you have data in cells B4:B9, the formula “=SUM(B$4:B4)” in cell C1 and filled down to cell C9 will return the running total for the numbers in column B.

Point to remember

Even with an absolute referencing style, Excel will still change row and column references when you insert a row or column. To have a truly absolute cell reference that will not change under any circumstances, use the INDIRECT function. For example =INDIRECT("A1") will always refer to cell A1, regardless of any changes made to the worksheet. This works because Excel does not interpret the string "A1" as an address. Instead, it treats it as plain text and therefore does not change it. 

Are we 'absolutely' clear now?

Active Desktop Recovery

Guess I am giving my computer too much stress to cope with. It does give me trouble sometimes but thankfully, they are not so serious and I solve them myself. Recently it shown an error 'Active Desktop Recovery'. Thought these workarounds could be material for a new post. So here we go!!

My computer shows a weird white screen in place of my beautiful desktop image (or company logo in office system). It looks like below in Windows XP.

Clicking on Restore my Active Desktop button doesn't solve the problem (if it does, we are good) but would throw up another window as below.

Clicking Yes or No buttons as above would make the pop up screen disappear but does nothing to the original error. If this is your problem, read further to see how to solve this issue.

What Causes It

Microsoft says that Internet Explorer 7 has been causing some errors with the Active Desktop, if you have Internet Explorer 7 on your computer read this article for the solution to fix the problem. This could also be caused by a corrupted user profile.

Solutions

There are below known solutions to this problem. Try one after another to check your luck.

Solution 1

1. Right click on a blank area of the desktop and select Properties.
2. Select Desktop Tab and press Customize Desktop button. (If you use office laptop, your admin can restrict this view, then try next solution).
3. In General Tab, make a change to the Desktop Icons section by selecting or deselect one of them and press OK.

Your active desktop should be back by now. If it doesn't, try your luck in the next solution.

Solution 2

1. Right click on a blank area of the desktop and select Properties.
2. Select Settings Tab and change Screen resolution. (you can obviously change it back, so no worries)
3. Right click on the desktop and choose Refresh

No luck yet? Go to the next solution.

Solution 3

'Desktop.htt' file controls the size and position of your desktop and the wallpaper you have on it, plus it includes a little activeX control to allow you to reshape it. IE7 is a little bit incompatible with the old profile you may have had already. Since this file is automatically generated by windows, the best fix is to delete the old one[s] and let windows create a new one. 

1. Go to C:\Documents and Settings\<your username>\Application Data\Microsoft\Internet Explorer
2. Select Desktop.htt and delete (it is a hidden file so you will need to change the view first. Click View - Folder Options - View)
3. Close Explorer
4. Right click on the desktop and choose Refresh

Got it now? 

Solution 4: (You should have access to registry editor to try this fix)

1. Go to Run and type regedit in Open and click OK.
2. Now navigate to HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Desktop\SafeMode\Components
3. Find a key named DeskHtmlVersion on right side.
4. Right click the key and select Modify
5. Under the label Base, select the radio button Decimal
6. Change Value data from 272 to 0
7. Click OK

This should do the trick. If the error screen doesn't go, then try restarting your PC for the changes to take effect.

Solution 5: (You need to have access to run vbs files)

1. Copy the below text into a notepad 

2. HKEY_CURRENT_USER = &H80000001

   strComputer = "."
   
   

   Set objReg = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")

   strKeyPath = "Software\Microsoft\Internet Explorer\Desktop\SafeMode\Components" 

   strValue = "0" 

   ValueName = "DeskHtmlVersion" 

   objReg.SetDWORDValue HKEY_CURRENT_USER, strKeyPath, ValueName, strValue

3. Save the notepad as activedesktop.vbs 
4. Run the file (if required, as administrator)
5. Logoff and Login again

Solution 6:

Active desktop recovery error may appear because of corrupted user profile. So try creating a new profile.

1. Log on as the Administrator or as a user with administrator credentials.
2. Click Start, and then click Control Panel
3. Click User Accounts
4. Click the Advanced tab, and then click Advanced
5. In the left pane, click the Users folder
6. On the Action menu, click New User
7. Enter the appropriate user information, and then click Create

When you create a new user profile, you need to copy the existing user profile related files and settings to new profile. Otherwise new profile is just like you are starting a brand new system. So follow these steps to copy your files.

1. Log on as a user other than the user whose profile you are copying files to or from
2. In Windows Explorer, click Tools, click Folder Options, click the View tab, click Show hidden files and folders, click to clear the Hide protected operating system files check box, and then click OK
3. Locate the C:\Documents and Settings\Old_Username folder, where C is the drive on which Windows XP is installed, and Old_Username is the name of the profile you want to copy user data from
4. Press and hold down the CTRL key while you click each file and subfolder in this folder, except the following files:
• Ntuser.dat
• Ntuser.dat.log
• Ntuser.ini
5. On the Edit menu, click Copy
6. Locate the C:\Documents and Settings\New_Username folder, where C is the drive on which Windows XP is installed, and New_Username is the name of the user profile that you created in the "Create a New User Profile" section
7. On the Edit menu, click Paste
8. Log off the computer, and then log on as the new user

Solution 7:

You can try doing a 'repair install' of operating system. You need Windows OS CD.

You reached end of all the solutions. If none of the above did the trick for you, take a deep breathe and call your service provider / IT team / log a ticket. Good Luck!!

Visual Basic for Applications - The Appearance

Hello again!! We saw a brief history and uses and problems of VBA in our last two posts. Now let's see how to access VBA from Excel and how does it look.

We know that VBA is a special program that is hosted by Excel. To access this program, follow any one of these steps.

In Excel 2007 or later

• Press Alt+F11
• Right click on any sheet name tab and click on 'View Code'
• If macros are already available, click on View Tab > Macros. From the Macros dialogue box, select any macro available and click on Edit. 
• If you make Developer tab visible, click on 'Visual Basic' in it.
• Right click on any object in your workbook and click on Assign macro. In the Assign Macro dialogue box, click on New or Edit (if macro is already assigned).

In Excel 2003 or earlier

• Press Alt+F11
• Right click on any sheet name tab and click on View Code
• If macros are already available, click on Tools Menu > Macros > Macros. From the Macros dialogue box, select any macro available and click on Edit. 
• Click on Tools Menu > Macros > Visual Basic Editor
• Right click on any object in your workbook and click on Assign macro. In the Assign macro dialogue box, click on New or Edit (if macro is already assigned).

Performing any one of the above options will open up VBA. Windows and appearance of VBA windows that Excel opens up will be different in different options. Let's try to learn about each one of them.

Best way to learn VBA is to experience by yourself. So, along with reading below info, make sure you open and do a little R&D in VBA module.

1. Project Explorer

When you open the VBA window for the first time, project explorer is the most likely item you see. If you don't see this by any chance, make it visible by clicking on View > Project Explorer or simply press Ctrl+R. (We are talking about VBA menus and short cuts here, so make sure you are clicking in VBA window and not in Excel).

Project Explorer displays a hierarchical list of the projects and all of the items contained in and referenced by each of the projects. See below a closer look at project explorer window.

What this window contains: (Refer numbering in above picture)

1. View Code - Displays the Code window so you can write and edit code associated with the selected item. 
2. View Object - Displays the Object window for the selected item, Excel front end or UserForm.
3. Toggle Folders - Hides and shows the object folders while still showing the individual items contained within them.
4. Project Name - Excel book name will be the project name
5. List window - Lists the all loaded projects and the items included in each project. For a plain Excel book, it lists down all the sheets and one more item called ThisWorkBook.

Though we will cover about them during our future posts, I will give a heads up now. You will save code that is specific to the sheets under respective sheet's code window (example: macro code to execute when a sheet is activated). Code that is applicable to entire workbook should be saved under ThisWorkbook  (example : code to execute before saving a workbook). When you record a macro from Excel, VBA creates another list item called 'Module' and recorded macro code will be saved under it.

Double clicking any of the list item opens up a white space towards the right which is the container for code for that item. Clicking on + / - will expand/collapses the list.

Right clicking on any list item or in the white space in Project Explorer will give you further options like rename your project name, lock and protect your code, insert UserForms, import / export code from other files etc.

2. Code Window

This is where a developer spends most of the time. You write, save and display your code here. You can open as many code windows as required (one for each item in the list window). Just double click on each item in the Project Explorer and you can notice the code window for that object opens up. You can minimise, maximize, restore and close the code windows by clicking on the respective buttons on top right corner which are just below the main VBA buttons. You can select the code from one code window and drag it to another window.

But be careful, it is not possible to identify which item's related code window is open at any given point of time. So if you want to open a particular item's code window, better double click on that item rather than toggling.

As said, we will be working mostly in the code window. So surely we will learn more about this window while coding.

3. Properties Window

Every object in Excel will have properties. For example, if you take a sheet, name of the sheet is a property of the sheet, visibility of the page (hidden or visible) is one of the property.

Properties window contains properties for the item selected in Project Explorer. You can view and modify the properties of the item from code window. Each item might have view only properties or few properties will be editable. So make sure the property you are trying to modify is editable, otherwise VBA complains. Code for the item need not be displayed in order to show the properties.

4. Immediate Window

Immediate window allows you to give a line of code and it will show the result instantly without actually executing the code. This is used extensively while writing code to see if the code is resulting into an error.

5. Locals Window

Locals window displays all of the declared variables in the current procedure and their values. It is automatically updated every time a code line is executed. This is used to observe the variables behaviour in the code testing.

Using the Locals window is easier to display variable values than examining the value from the Immediate window. For simple variable types (e.g., Long and String variables), the value is displayed in one line. For complex types or objects (e.g., a Range variable), its properties are displayed in a collapsible tree-like structure.

6. Watch Window

You can set up watch criteria to either momentarily stop executing your code when the condition is met. The Watch window displays all the Watches in effect. You can display the Watch window by choosing Watch Window from the View menu. A Watch is an instruction to VBA to pause code when an expression is True or when the variable being watched changes value. To create a Watch on a variable, open the Watch window and right-click in the Watch window and choose Add Watch from the popup menu or choose Add Watch from the Debug windows. In the Add Watch dialogue  enter in the Expression text box a variable name whose value you want to watch. Then choose 'Break When Value Changes'. When you run the code, execution will pause at the line after the line that modifies the variable's value. When code pauses, the value of the variable will have already been updated.

To remove a Watch, right-click it in the Watch window and choose Delete Watch from the popup menu. To modify a Watch, right-click it in the Watch window and choose Edit Watch from the popup menu.

Okay, guess that's enough to get you started. As I mentioned above, the best way to learn VBA is to experience it by yourself. So make sure you open and do a little R&D in VBA module. Happy learning!!

Click here to know about VBA object structure.

Introduction to Visual Basic for Applications

Alright guys!! I guess the time has come to go beyond the regular posts on Excel front end, into developer front. Before diving deep into developer stuff, I thought it's appropriate to look a little bit about Excel programming history first.

Some Technical Info

Excel programming language is called Visual Basic for Applications (VBA) which is a dialect of Visual Basic (VB). VB was released by Microsoft and quickly became famous for its ease of use to create application components.

Before VB, various macro languages were previously supplied with Office applications to automate tasks. It was Microsoft's idea to cut all those different macro languages and make one universal language to embed in applications. But VB is a separate programming language altogether and it needs to be purchased and installed separately to work with. Hence a cut down version of VB is made and named as VBA. That's how VBA was born and evolved.

VBA is distributed using host applications like MS Office products such as Excel, Word etc. So, writing code, saving and executing can only be done within a host application rather than as a standalone program. But you can use VBA to control and work with other VBA hosted applications. For example, if you are using VBA in Excel, you can open and read the contents of MS Word file. VBA is proprietary to Microsoft hence you cannot use VBA written program in any third party application.

VBA code is written in Visual Basic Editor (VBE). Code is then compiled by host application into an intermediate language called 'p-code' which then be executed by a virtual machine which is also hosted by host application. Code you write in VBA would almost look like common spoken English. We will see different terms used and appearance of VBA in forthcoming posts. 

Quick History

• VBA was first launched with MS Excel in 1993. It became an instant success among developers to create corporate solutions using Excel.
• VBA 4.0 is the next famous release with a totally upgraded version compared to previous one. Released in 1996, it is written in C++ language and became object oriented language.
• VBA 5.0 was launched in 1997 along with all of MS Office 97 products. Only exceptions for this is Outlook which was using VBScript to automate things.
• Year 1998 saw the launch of VBA 6.0 and continued to be supplied with MS Office suite.
• Office 2010 is included with VBA 7.0. There is nothing much offered to developers from VBA 6.0 except for 64-bit support.

Once entered in the world of VBA, we will learn how to automate simple tasks, write bits of complex codes and custom made functions etc. We will dismantle the code to analyse what it means and how to develop Excel applications using VBA. We will also see a series of practical situations and write code to work efficiently. The journey to future will be surely exciting and truly rewarding in terms of knowledge gain. Stay tuned!!

Click here to read about why and why not we should use VBA.

Functions: RANK

Hi there!! Departing from the regular series on functions (Lookup & Reference) that we are seeing in last few posts, today I am going to introduce RANK function in this post. RANK can be found under 'Statistical' category in Excel built in functions. I thought of writing about this function because I recently used it and found myself researching about tweaking this so that I can adapt it to more practical solutions.

RANK function returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. That means, if you sort the list, the rank of the number would be its position from top. For example, you have a list of marks secured by a set of 7 students and you want to give them ranks from 1 to 7 based on their performance in which 1 being the top and 7 being the lowest marks. You can use RANK in order to get the desired result. Let's further analyse this function syntax and usage.

RANK Function Syntax

RANK(number,ref,[order])

number: Is the number you are looking to find a RANK for.

ref: Is the list of numbers in which you want to find the rank of a number

order: This is an optional argument. If you omit or enter 0 or False, RANK returns the rank from high to low. In our example above, person who secured the top marks will get a rank of 1. Any other value for this argument returns the rank from low to high. Same example above returns rank of 1 to lowest mark.

Example:

Following our example above, I have a list of marks for 7 students and want to find the ranks for each of them. I want to give them ranks from 1 to 7 based on their performance in which 1 being the top and 7 being the lowest marks. See below on how RANK can help us.

That's it. We get the ranks for each item. Other way around, if I want to rank them in reverse order, a formula like below would help.

Points to remember

• If you are applying RANK to multiple numbers by dragging it to subsequent cells, make sure you use absolute reference in ref.
• Of course, number you are looking to give a rank should be available in the list of numbers. Otherwise RANK returns an error (#N/A).
• Make sure that list for second argument contains numbers only. Non-numbers or numbers formatted as text will result into #N/A error. RANK will not give rank to this item and continues ranking other numbers.
• Zero is also a number for RANK function hence it will be given a rank. But because of any reason, what if you want to exclude zero while giving ranks? Actually this is the situation I faced couple of days back. I was analysing returns given by certain mutual funds in FY13. There is a big list of numbers varying from -40% to +120%. A return of 0% means that the mutual fund did not participate in that category or data is not available. Hence it should not be ranked. I end up tweaking RANK function to suit this requirement. Refer to my solution in 'Avoid zeros' section below.
• RANK gives same rank to duplicate numbers but effects the sequence of ranks. For example, if you have a list of numbers 10, 20, 20, 30, 40, 50 and applied RANK function for all the numbers, ranks of second AND third number will be 2 and 2 but the fourth number will have a rank 4. Meaning there is no rank 3 given to any number in the list.

Avoid Zeros

In some practical cases, we need not give ranks to zeros. If your data contains zeros, RANK assigns a rank to them. See the standard RANK performance in below picture. See the cells highlighted in blue. Zeros got the rank 5.

So what if I do not want to rank zeros in the above example because zero means return figure is not available hence cannot be ranked. Let's see how we can use IF and RANK combination to do this.

Same data. But see the highlighted cells. There is no ranking to zeros and other numbers got ranks ignoring zeros. (Notice the difference of 2 in all ranks compared to earlier ranks including zeros). Mission accomplished.. Let's analyse what our formula is doing here.

We used a basic IF formula to check if number we are checking is zero. If true then return a null value (""), if false then further use rank function. For cell B2, Excel evaluated the function like below.

1. Check if A2 = 0
2. If above result is True then show "" as a result. No further analysis.
3. If result is False then result will be result of 

RANK(A2,$A$2:$A$11)-IF(A2<0,COUNTIF($A$2:$A$11,0),0)

So our first requirement, showing null (blank in the cell) for zeros is achieved by above simple steps. Showing correct ranking for cells not containing zeros is the work of 3rd step above. First part of the above function calculates the rank including zeros using standard RANK syntax.

RANK(A2,$A$2:$A$11)

Result for first number using this formula would fetch 10. Careful now. Ignoring zeros in ranking WILL NOT effect positive numbers rankings but the negative number rankings will be reduced by number of zeros in the list. Notice above 2 pictures and find out that positive number rankings have not changed. So the second part of the above function evaluates like shown below.

1. Check if A2 is less than 0 (means negative)
2. If true then count number of zeros in the list using COUNTIF
3. If false then result is 0

Result of second part is 2 in our example (number of zeros are 2, right?). This number will be deducted from result of first evaluation (which is 10) and revised rank 8 is given.

Drag it over other cells and you get ranking ignoring zeros. Though our example shows ignoring zeros, with bit of modification, you can set what ever criteria you wish for. 

Post a comment if you have anything to share regarding this. Happy learning!!

Functions: VLOOKUP - Left lookup

Hi again!! We saw about VLOOKUP function in last post. We also saw that it works to find a value which is in the first column of table array or towards right from it. So VLOOKUP fails to lookup a value to the left. However there are some cases where you have to apply lookup but have required value to the left side of lookup_value position in table_array.  You do not have permission to change the column sequence or simply do not want to do that. In this case your hope is LOOKUP or a tweaked version of VLOOKUP.

In this post I will show you a way to tweak the standard function and make it work this way. Let's see a quick recap of how VLOOKUP works.

VLOOKUP function looks for a value (lookup_value) in a set of rows and columns (table_array) and returns a value from the same row where lookup_value has been found but from a different column we ask for (col_index_num). You can also request an exact match or an approximate match (range_lookup). Looking closely at table_array description, we can notice that this argument supports the following; 

1. a range reference (like $A$3:$A$17) or 
2. a named range (example: if you have a table named as MyTable, you can simply type MyTable as the second argument instead of range) or
3. a formula that results in the range reference

What we used in our VLOOKUP example in earlier post is the range reference. To lookup a value to the left we will use 3rd property above that tricks VLOOKUP to believe the given array is on the right side where as in reality it will be towards the left. Let's see one example.

I have a list of asset codes along with asset names in a data base. As you see in the below picture, asset code is the first column and next column (to the right) is asset name.

Given the asset code, I can easily use VLOOKUP to retrieve asset name. But if I have asset name given and have to find out asset code which is on the left, VLOOKUP gives up. See the below formula.

Since VLOOKUP always look for lookup_value in the first column and in our example it will not be able to find a match, it results in to an error (#N/A). So what to do now? In this particular example you can use the flexibility of LOOKUP.

But if you want to use VLOOKUP, let’s see how to tweak this function to work in these cases. Enter the below formula and see how it works, below I will explain what we did.

You can cross check the results manually from data base to see results are in fact correct. Once you made sure we are good with the formula, we will now analyse the above function.

We just followed VLOOKUP syntax as required. As we know that VLOOKUP finds a value in the table_array provided and return corresponding value from a chosen column , we have provided table_array that is created by CHOOSE function by combining column B first and then column A. This tricks VLOOKUP to believe column B is indeed first column and column A is second. We will slow down now and analyse what CHOOSE function is doing, bit by bit.

Let’s have a closure look on how we use CHOOSE and what it means. We used the below as an argument in VLOOKUP above.

CHOOSE({1,2},$B$1:$B$11,$A$1:$A$11)

We are giving 2 ranges to CHOOSE function which is B1 to B11 and A1 to A11 (last part of the function) and asking it to combine them. Notice that we are giving column B first, then column A and we asked it to combine first one and second one. VLOOKUP considers the combined range for second argument instead of our initial database.That’s the trick. You may get a doubt now. Can we give column A first, then column B and ask CHOOSE to combine 2^nd one first and 1^st one next? Answer is YES. Below formula also works perfect.

CHOOSE({2,1},$A$1:$A$11, $B$1:$B$11)

Essentially, both of above syntaxes creates an array with Column B and Column A. Try these steps to see it yourself. We use CHOOSE as an array function below.

1. Select a 11 rows * 2 columns range.
2.  Type / paste the function “= CHOOSE({1,2},$B$1:$B$11,$A$1:$A$11)” without quotes
3.  Press Ctrl+Shift+Enter

Now you can see Excel filled up the selected range like below.

Notice that Asset name is in first column and Asset code later. Our original database is other way around. Once the CHOOSE function does this with in the Excel memory, VLOOKUP does the remaining job of finding and returning the value which is actually to the left of the table.

You can add any number of columns like this to CHOOSE function and extend this function to suite your need. For example, if you have 3 columns in your data (A, B and C) and you want to create an array with  an order B, C & A, just use CHOOSE like below.

CHOOSE({2,3,1} ,$A$1:$A$11, $B$1:$B$11, $C$1:$C$11)

That’s it guys! This way you can make VLOOKUP work with the help of CHOOSE function to return left side value. Post a comment if anything seems confusing above. See you next time!

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!!