vlookup(), match() and offset() in Excel Formula – explained in plain english [spreadcheats]

VLOOKUP may not make you tall, rich and famous, but learning it can certainly give you wings. It makes you to connect two different tabular lists and saves a ton of time. In my opinion understanding VLOOKUP, OFFSET and MATCH worksheet formulas can transform you from normal excel user to a data processing beast.

What is the syntax for Match, Vlookup and Offset?

Here is the syntax for these three very powerful functions in plain English:



What are vlookup () and match () ?

VLOOKUP and MATCH are your way of asking excel to find a needle in haystack. Imagine you have all your customer contact information in one sheet in the range A1:D5000 in the format phone number, name, city and date of birth. Now you need to find out which customer has the phone number “            936-174-5910      ″. How do you do it?

You guessed it right, you use VLOOKUP and summon excel to do the search and return with customer name.

While VLOOKUP is used to fetch value a based on what you are looking for, MATCH is used to fetch the position of the value you are looking for.

See this illustration to understand :



So what is Offset() then?

OFFSET is your way of telling excel to fetch a portion from large range of values. You can compare OFFSET to what you see from your car window while driving. As your car moves, you see different things from the window.

OFFSET returns a reference to the portion of a large range you have supplied based on 5 parameters. For eg. OFFSET (A1, 3,4, 5,6) would return 5×6 cell range from E4 (A+4 columns, 1+3 rows = E4) thus: E4: J9

See this illustration to understand how OFFSET spreadsheet formula works:



So how are Offset() and Match() linked to each other?

Since MATCH returns the position of the item you are looking for in a list, you can then use this position in OFFSET to fetch values surrounding the searched value.

Finally

Remember, both VLOOKUP and MATCH throw a fail error of #value! if the value you are looking for is not there. Also, OFFSET returns a range so make sure you pass the value to another function like SUM that accepts ranges.

Related Topic:-
How to Program Macros in Microsoft Excel
How to Write a Simple Macro in Microsoft Excel
Adding a Range Name in Excel
Convert a Row to a Column in Excel the Easy Way
Microsoft Excel 2010 Support

Read more »

Excel Formula Errors – Understand and Debug Them

“Trial and Error” has to be the most effective ways to learn something new. Most of the stuff I learned in my life is through that. Be it insurance or excel or programming. My learning has always gone up when I make a mistake. I am sure most you agree with me…

So in this installment of spreadcheats we will learn about errors. Those annoying #SOMETHING!s that you see when your excel formulas have something wrong with them.

#DIV/0! Formula Error

This is the easiest of all. When you divide something with 0, you see this error. For eg. a cell with the formula =23/0 would return in this error.

How to fix #DIV/0 error?

Simple, do not divide the value by Zero.  You know the answer anyway.

#NAME? Formula Error

The most common reason why you see this error is because you misspelled a formula or named range. For eg. if you write =summa(a1:a10) in a cell, it would return #NAME? error. There are few other reasons why this can happen. If you forget to close a text in double quotes or omit the range operator :. All these examples should return #NAME? error. =sum(range1, UNDEFIED_RANGE_NAME), =sum(a1a10)

How to fix #NAME? Error?

Make sure you have mentioned the correct formula name. If you are using excel 2007, when you are typing the formula excel shows all the matched formulas. In earlier versions of excel, if you use correct formulas, they will be automatically capitalized. For eg. if you type =sum(1,2,3) in a cell and press enter, it will be changed to =SUM(1,2,3). You can use this feedback to correct formulas.
Make sure you have defined all the named ranges you are using in the formula.
Make sure any user defined functions you are using are properly installed.
Double check the ranges and string parameters in your formulas.
#N/A Formula Error

This is one of the frequent errors you see while using vlookup formula. The N/A error is shown when some data is missing, or inappropriate arguments are passed to the lookup functions (vlookup, hlookup etc.) of if the list is not sorted and you are trying to lookup using sort option. You can also generate a #N/A error by writing =NA() in a cell.

How to fix #N/A error?

Make sure you wrap the lookup functions with some error handling mechanism. For eg. if you are not sure the value you are looking is available, you can write something like =if(iserror(vlookup(…)),”not found”,vlookup(…)). This will print “not found” whenever the vlookup returns any error (including #N/A)

#NULL! Formula Error

This is rare error. When you use incorrect range operators often you get this error. For eg. the formula =SUM(D30:D32 C31:C33) returns a #NULL! error because there is no seperator between range 1 and range2.

How to fix the #NULL! error?

Make sure you have mentioned the ranges properly.

#NUM! Formula Error

This is number error that you see when your formula returns a value bigger than what excel can represent. You will also get this error if you are using iterative functions like IRR and the function cannot find any result. For eg. the formula =4389^7E+37 returns a #NUM! error.

How to fix #NUM! error?

Simple, make your numbers smaller or provide right starting values to your iterative formulas.

#REF!  Formula Error

This is one of the most common error messages you see when you fiddle with a worksheet full of formulas. You get #REF! error when one of the formula parameters is pointing to an invalid range. This can happen because you deleted the cells. For eg. try to write a sum forumla like =SUM(A1:A10, B1:B10, C1:C10) and then delete the column C. Immediately the sum formula returns #REF! error.

How to fix the #REF! error?

First press ctrl+Z and undo the actions you have performed. And then rethink if there is a better way to write the formula or perform the action (deleting cells).

#VALUE! Formula Error

Value error is shown when you use text parameters to a function that accepts numbers. For eg. the formula =SUM(“ab”,”cd”) returns #VALUE! error.

How to fix the #VALUE! error?

Make sure your formula parameters have correct data types. If you are using functions that work on numbers (like sum, sumproduct etc.) then the parameters should be numbers.

###### Error

You see a cell full of # symbols when the contents cannot fit in the cell. For eg. a long number like 2339432094394 entered in a small cell will show ####s. Also, you see the ###### when you format negative numbers as dates.

How to fix the ###### error?

Simple, adjust the column width. And if the error is due to negative dates, make them positive.

Related Topic:-
How to Program Macros in Microsoft Excel
How to Write a Simple Macro in Microsoft Excel
Adding a Range Name in Excel
Convert a Row to a Column in Excel the Easy Way
Microsoft Excel 2010 Support

Read more »

check what version of PowerPivot for Excel is installed on my machine?

A: There are few ways to check version of PowerPivot for Excel that is installed on your machine.

Option 1:

Start Excel
Go to menu "PowerPivot" and then click on the button "Settings"

• New window "PowerPivot Options and Diagnostics" will pop up with version number at the top of the window.

Option 2:

You can check PowerPivot for Excel version on your machine without starting Excel. To do so go to explorer and navigate to the folder :\Program Files\Microsoft Analysis Services\AS Excel Client\10. Then select file Microsoft.AnalysisServices.Modeler.FieldList.dll and do right mouse click on it, then choose "Properties" item and then go to the tab "Details". There you will see property "File version". Value of this property shows version of your PowerPivot for Excel installation. For PowerPivot RTM file version is 10.50.1600.1.

Also, if you have any problems with PowerPivot, make sure that version of Excel you are running is compatible with PowerPivot version. There are 2 ways to check Excel 2010 version:

Option 1 - Start Excel, Choose menu item "File", then "Help". In the screen you will see message that looks like: "Version: 14.0.4760.1000 (32bit)

Option 2 - to to folder where Excel 2010 is installed. By default this will be "C:\Program Files\Microsoft Office\Office14", then select file "Excel.exe", right mouse click and go to properties and "Details" tab. There you will see property "File version". In my case value was "14.0.4756.1000".

Note: as you can see Excel version numbers are different - if you are reporting issue with PowerPivot, make sure you specify Excel version and which way to got this excel version.

Related Topic:-
How to Program Macros in Microsoft Excel
How to Write a Simple Macro in Microsoft Excel
Adding a Range Name in Excel
Convert a Row to a Column in Excel the Easy Way
Microsoft Excel 2010 Support

Read more »

How to install PowerPivot for Excel and list of know issues

Step by step guide on installing PowerPivot for Excel

If you are running an operation system other than Microsoft Windows 7, install .NET Framework 3.5 SP1 first.
Download and install Microsoft Office 2010. Important note: To work with PowerPivot you need to install just Excel 2010, but it is important that you also install Office Shared tools, otherwise you will not be able to register PowerPivot add-in. Also, PowerPivot is not supported in Click-to-Run versions of Microsoft Office
Download and install PowerPivot add-in from here. Important note: If you are using Excel 32bit, you must download 32bit PowerPivot. If you are using Excel 64bit then you must download 64bit PowerPivot addin.
Start Excel 2010. You might have to confirm that you want to "install this customization". After that you can start using PowerPivot.

Known PowerPivot for Excel installation issues

When installing Office 2010 you must install Office ShareTools. More info here...
PowerPivot for Excel add-in is not coming up in Excel - a set of tips.
On Windows XP SP2 you might get error: "Initialization of the data source failed.". To fix this problem apply Windows XP Service Pack 3. More info here...
If during PowerPivot installation you got error message "This installation package is not supported by this processor type. Contact your product vendor.", that means you are trying to install 64bit PowerPivot application on 32bit Office 2010. Please download 32bit PowerPivot installation and try installing it again.
If during PowerPivot installation you got error message "Setup is missing prerequisites: This add-in requires Excel 2010. If you have 32-bit Excel, you must install the 32-bit version of the add-in. If not, you must install the 64-bit version. Go to http://go.microsoft.com/fwlink/?LinkId=54583", that means that you are trying to install 32bit PowerPivot application on 64bit Office 2010. Please download 64bit PowerPivot installation and try installing it again.
If you installed add-in, but do not see in the Excel 2010, then start Excel, select menu option "File", then "Options" and choose tab "Add-ins". At the bottom of the page from the "Manage" list choose "COM Add-ins" and select "Go". In the list of available add-ins makes sure that checkbox near "Microsoft.AnalysisServices.Modeler.FieldList.Addin.Integration" is checked. (tip source)
You have to make sure that you installed correct Excel 2010 and PowerPivot versions. Here is the link where you can read how you can check your "build numbers" of PowerPivot for Excel and Excel 2010. For v1 RTM PowerPivot for Excel build number is "10.50.1600.1" and Excel 2010 build number is "14.0.4760.1000". Build numbers are the same for 32bit and 64bit software.
PowerPivot is not supported in Click-to-Run versions of Microsoft Office - Error: ""Setup is missing prerequisites: Excel 2010 is required for installing the Analysis Services add-in.  Go to http://go.microsoft.com/fwlink/?LinkId=54583"
You receive an error message when you try to install PowerPivot for Excel 2010: "Setup is missing prerequisites"
Where did my PowerPivot Excel 2010 tab go?
If you have any other issues after installing PowerPivot for Excel, set global environment variable VSTO_SUPPRESSDISPLAYALERTS  to 0 and restart Excel. You should see more detailed message that will give you better idea what your problem is, or you might have to submitt that message to Microsoft support.

Related Topic:-
How to Program Macros in Microsoft Excel
How to Write a Simple Macro in Microsoft Excel
Adding a Range Name in Excel
Convert a Row to a Column in Excel the Easy Way
Microsoft Excel 2010 Support

Read more »

How to Program Macros in Microsoft Excel

Microsoft Excel is a spreadsheet application that offers an extensive set of features to customize your workflow. Macros are one part of the program that considerably extend its functionality. A macro is a small program that runs inside an Excel document to streamline a particular process. You can create macros by programing Visual Basic for Applications (VBA) code directly into the Excel file. This requires considerable programming skill and experience. But for the novice, Excel makes it possible to generate VBA by "recording" ordinary program operations with the mouse or keyboard. In this way, you can create macros without knowing VBA.

1- Open the Excel file in which you wish to create the macro.

2
Click the "View" tab on the Excel ribbon toolbar. Then press the "Macros" button.

3
Click on the "Record Macro" option. A pop-up window will appear requesting the name of the new macro. Type any name you wish, but do not include any spaces in the title.

4
Click in the "Shortcut Key" field and press any button on the keyboard if you wish to assign a keyboard shortcut to the macro. This way you can run the macro quickly later by pressing the "Ctrl" button on the keyboard with your desired key.

5
Type a description to summarize the purpose of the macro, if desired. Then press the "OK" button.

6
Click, type or manipulate the Excel program in any way you wish. Every movement of the mouse is recorded as part of the new macro. For example, if you want a macro that will automatically insert a new blank worksheet, right-click on any worksheet tab at the bottom of the Excel window, choose "Insert," select "Worksheet" and press "OK." The program inserts a new worksheet, and every step is recorded.

7
Click the "Macros" button on the Excel ribbon toolbar when you are finished recording your macro. Then click the "Stop Recording" option.

8
Run the macro by pressing the "Ctrl" button together with the designated shortcut key on your keyboard. Whatever actions you completed during the macro recording are now repeated with a single keyboard shortcut.



Read more: How to Program Macros in Microsoft Excel | eHow.com http://www.ehow.com/how_7190042_program-macros-microsoft-excel.html#ixzz1jrH96XpY

Related Topic:-
vlookup(), match() and offset() in Excel Formula
Excel Formula Errors – Understand and Debug Them
check what version of PowerPivot for Excel is installed on my machine?
How to install PowerPivot for Excel and list of know issues
Microsoft Excel 2010 Support

Read more »

How to Write a Simple Macro in Microsoft Excel

1-Define exactly what is it that you expect your macro to do. While macros are usually short, they're still programs, so you have to have a clear idea of its final behavior.

2-Go to Tools->Macro->Macros... in the menu to bring the Macro dialog. Note: if you are using the Ribbon interface (default word 2007 and above), then the option will be under View -> Macros (on the far right).

3-Enter a name for your macro, select if you want your macro available in all workbooks or just the active one and press "Create". This will open Microsoft Visual Basic Editor and a new module in it. That's where you will create your macro.

4-Type the code of your macro between the lines "Sub TheNameOfYourMacro()" and "End Sub". Those are the lines that mark the points where your macro starts and ends.



If you don't know the exact code you need to make something happen, you can use the "Record New Macro..." option in the Tools->Macro menu. Once you start recording the macro, manually perform the same activities you want your macro to do. Go to the Microsoft Visual Basic Editor to see the code generated by the macro recorder and use it in your own macros.

5-Close the Microsoft Visual Basic Editor and save your workbook.

6-Go to the Tools->Macro->Macros... menu again, select your macro name and run it to see how it works.

7-Optionally, you can go to the Macro dialog again, select your macro and click "Options..." to bring a new dialog that will allow you to assign a Shortcut Key. This will let you run your macros with a couple keys instead of having to go to the dialog every time.

Related Topic:-
vlookup(), match() and offset() in Excel Formula
Excel Formula Errors – Understand and Debug Them
check what version of PowerPivot for Excel is installed on my machine?
How to install PowerPivot for Excel and list of know issues
Microsoft Excel 2010 Support

Read more »

Adding a Range Name in Excel

Excel Range Name Overview

A range in Excel is a group of one or more cells in a worksheet.

A name can be given to a range to make it easier to identify this cell or cells when creating formulas or charts.

Range names are created by selecting the required cells and then typing the name in the Name Box located above column A in the worksheet.

Normally, we use cell references - such as B8 or C5 - when creating formulas in Excel.

Cell references point to the location of the data to be used in the formula rather than entering the data itself in the formula.

One advantage of this approach is that if you later change your data the formula automatically updates to show the new result.

A second advantage is that, in certain instances, it makes it possible to copy formulas from one location to another in a worksheet.

Copying Formulas

If data is laid out in rows, such as the data in rows 8 to 11 in the image above, copying formulas from one row to the next usually works because the cell references in the formula automatically change to match each row number that the formula is copied to.

However, if a formula contains a constant - such as the deduction rate in cell C5 in the image above - copying the formula won't work because we can't allow the cell reference C5 to change when the formula is copied. Our formula must always refer to the deduction rate in cell C5.

A range name then, is an easy way to avoid the problem of changing cell references in formulas that are being copied to other cells. If we assign a range name to cell C5 then the range name is used in the formula rather than the cell reference.

Even when copied to other cells, the range name will always refer to cell C5.

Related Topic:-
vlookup(), match() and offset() in Excel Formula
Excel Formula Errors – Understand and Debug Them
check what version of PowerPivot for Excel is installed on my machine?
How to install PowerPivot for Excel and list of know issues
Microsoft Excel 2010 Support

Read more »