ExcelMatic

Home / Advanced Topics

Showing posts with label Advanced Topics. Show all posts

Function Indirect()

Indirect() This function is used to return the reference to text reference provided. This function converts the text reference to actual...

Function Index()

Dhakkanz 1:13 AM Add Comment

Index() Index function can be used in 2 forms - Array form and the Reference form. I will take each of these here. Array form : In the...

Series on important functions - Lookup Funtions

Dhakkanz 9:43 PM 1 Comment

From today onwards I will be starting a new series of Functions under the Lookup Functions. These functions will deal with functions such as...

How to define Range Names?

Dhakkanz 12:04 AM Add Comment

Excel generally provides more than one ways to perform an action. For defining range names also, it has provided 2 ways: U sing the Insert ...

What are Range Names?

Dhakkanz 8:08 PM Add Comment

Range Names is another of Excel's feature that makes life of its users a lot easier. A Range Name, as the name explains itself, is a Nam...

Working out with Data Validation list

Dhakkanz 10:07 PM Add Comment

Recently one of the readers sent me an email with a question on data validation issue that most of us would have faced. I'll call this ...

Concept of Data Validation

Dhakkanz 11:58 PM 5 Comments

Concept of Data Validation? Data Validation is a tool provided by MS Excel that allows you to validate data that is entered by a user. The...

How to calculate subtotals using Array functions

Dhakkanz 11:41 PM 1 Comment

I have discussed basic concept of array functions, its advantages and disadvantages in my previous post . Now, let us now talk about an exa...

Usage of Array Functions

Dhakkanz 12:45 AM 2 Comments

In my previous post I explained the concept of Array functions (or formulas - please note that I will be using these two words interchangeab...

What are Array Functions or Array Formulas

Dhakkanz 11:15 PM 2 Comments

Array functions are one of the powerful tools that Excel has provided. They are able to achieve many things that one cannot achieve by work...

Subscribe to: Posts ( Atom )

Showing posts with label Advanced Topics. Show all posts

Saturday, September 19, 2009

This function is used to return the reference to text reference provided. This function converts the text reference to actual reference and fetches the value available in the address referenced. (It will be much clear when we will take a look at an example)

Syntax:

= Indirect (Text_reference, Reference_format)

Text_reference: This is the text form of cell address or reference from where the value is to be fetched

Reference_format: Reference format can either be TRUE or False. True indicates that text reference is in A1 style and False indicates text reference is in R1C1 format

Examples:

Let us take an example here. In cell A1 write, B1. And, in cell B1 put any value (for example, 10). Now in cell A5 write this function:

= A1

This function will return 'B1' as a result as we have referred to the cell A1's value.

There could be another case - if we want to get the value of cell which is mentioned in A1. In such case we make use of Indirect() function. This function will the reference (the address of the cell from which we need to fetch the value) from the cell specified in the function. Now, change the function in cell A5 to this:

= Indirect (A1)

This function will now return the value stored in the cell B1 (the cell which is mentioned in the cell A1)

These functions are used mostly in the case where the address or the cell to be referred to are dynamically created.

Wednesday, September 16, 2009

Function Index()

Index()

Index function can be used in 2 forms - Array form and the Reference form. I will take each of these here.

Array form: In the first type, it is used to fetch a value from a table / array by making an intersection point using the row number and the column number. In this form, you can specify only a single array from which the values are to be fetched.

Reference form: This form also works similar to the first one, with the only exception being that in this case, you can mention more than 1 arrays / tables from which the value will be looked up and in the function you also mention which reference (i.e. the table / array) should actually the should be fetched from.

Syntax:

Array form: = Index (Array, Row_number, Column_Number)

Array: This argument is the array from which the value should be picked. The array can be a table or a range of cells with single column or row
Row_number: This argument specifies the row number from which the value will be picked
Column_number: This argument completes the function by providing the column number which will be used to create the intersection with row number to fetch the number

Reference form: = Index (Reference, Row_number, Column_number, Reference_number)

Reference: This argument specifies the ranges / tables from where the the value should be fetched using the index number. In case there are more than 1 references mentioned, this argument itself should be enclosed within the parentheses

Row_number: This argument specifies the row number from which the value will be picked

Column_number: This argument completes the function by providing the column number which will be used to create the intersection with row number to fetch the number

Reference_number: This argument informs the function which reference should be considered among multiple references provided

Examples:

Array form:

Let us create an example worksheet. In the column A fill cells A1 to A10 with the number from 10 to 100 with an increment of 10. And for the cells B1 to B10, fill them with alphabets A till J.

Now let us suppose you want to fetch the item that is in the 2nd column and the 3rd row of the table that we just created. For this, the formula should be:

= Index (A1:B10, 3, 2)

The result will be: C. Similarly if we want to fetch the item in 4th row of the column 2, the formula will be

= Index (A1:B10, 4, 2)

Reference form:

Now let us take another example here. In the same that we just created let us create another table. From the cells A15 to A20 fill the range with values 1 to 6. And in the cells B15 to B20 fill the range with alphabets U to Z. Now let us use one of the previous examples. Let us assume that we want to fetch the item in 3rd row and the 2nd column. The modified formula would be:

= Index ( (A1:B10, A15:B20), 3, 2, ....)

In the above function there are 2 things that I will explain here. One - I have mentioned 2 arrays in this function, from either of which I can pick up the value as referred by the row and the column number.

Second - In place of reference number, I have used three dots (...) instead of using any value. I have kept it like that only for explanation, else it will give an error. This argument will tell the function from which array out of the 2 provided to the function, the value should be picked up from. The 2 arrays are - (A1:B10, A15:B20).

If the reference number is left blank, the function be default takes the first array and returns the value. Otherwise the results would be as follows;

= Index ( (A1:B10, A15:B20), 3, 2, 1) – the result would be C
= Index ( (A1:B10, A15:B20), 3, 2, 2) – the result would be W

So, the reference_number argument is used to identify which table should be used to pick the value from.

Sunday, September 13, 2009

Series on important functions - Lookup Funtions

From today onwards I will be starting a new series of Functions under the Lookup Functions. These functions will deal with functions such as VLookUp, HLookUp, etc. and also the functions which are generally used with these functions.

These functions are the most used functions where there are huge tables and there is a need of extracting data from these tables.

So keep an eye open from today as I will be discussing all of these important functions here.

Monday, August 31, 2009

How to define Range Names?

Excel generally provides more than one ways to perform an action. For defining range names also, it has provided 2 ways:

Using the Insert –> Name –> Define command in Menu Bar
Using the Name box (adjacent to formula bar) directly

Here are the steps to define Range names by both the above methods:

Using the Menu Bar:

Select the cell / range of cells which you would like to name
Go to Insert –> Name –> Define command
Now you would see a dialogue box (shown below)
On the top text area (where the cursor would be blinking by default) type the name that you would like to give to the range selected
Press Add and then OK

...and you are done!

Using the Name Box:

Select the cell / range of cells which you would like to name
Go to Name Box (as shown below)
Type the name for the range in the Name box and then press Enter

...and you are done!

Now you have learned the usage of and the steps to define range names. But, there are some important points that one should keep in mind while defining the range names.

Sunday, August 30, 2009

What are Range Names?

Range Names is another of Excel's feature that makes life of its users a lot easier.

A Range Name, as the name explains itself, is a Name that is given to a cell or a range of cells. This Name then can be used at other locations to refer to the defined range.

Name ranges can be defined in 2 ways:

Using the Insert –> Name –> Define command in Menu bar
Using the Name box (adjacent to formula bar) directly

Advantages of using Name Ranges:

Range names are used in soft-coding of big and complex models. Not just complex, range names can also further simplify simpler models
They make formulas easier to understand and read. They can add meaning to boring formulas by providing them with names
They are very useful in situtations where VBA coding is used. If a VBA code uses a cell reference and that cell's value need to some other place, the range names can come very handy in such cases

How to define Range Names?

Thursday, August 6, 2009

Working out with Data Validation list

Recently one of the readers sent me an email with a question on data validation issue that most of us would have faced. I'll call this person as Tom as this person has asked for anonymity of his identity.

As you remember, a cell can be validated for numerous things, text entries, numerical ranges, pre-defined lists. etc. [Refresh your data validation concepts here]. Tom has asked me a question related to data validation for a pre-defined list. He asks - when defining the validation rule for a list, we have to put a range where the list has been defined. Now when you are defining the rule, it asks for the list range and you have to either manually enter the range or you can select the range with the help pf mouse.

At this stage, there is one thing you cannot do, i.e. input the range which falls out of that sheet. What it means is you cannot select any range which is not on the same sheet where the validation is being put.

Tom is correct partially. He is correct when he said that users cannot select the range which is on the other sheet, but it is not true that you cannot enter the range that is not on the same sheet. One can very well enter the range manually and have the list located on any sheet other than where it is being used for validation.

One simply has to mention the full range of the list (the address where the list is located) together with the sheet name and it will work normally. Just remember to put a '=' sign so that the data validation works fine. For an example - I will refer to a list which is on 'sheet2' and I am applying this validation on a cell in 'sheet1'. My formula for this would be:

= 'Sheet1'!A4:A10

There is another way you can do this (and in my opinion this is the best way) - using Range Names.

A Range Name is a name given to a cell or range of cells. One can use this name anywhere in the workbook for any kind of processing without having to worry about the sheet names and the cell ranges. In our case, while using range name, the validation rule should simply refer to the range name given to the list. The list address should instead show this formula (assuming the range name given is 'ListName'):

=ListName

...and that is it!

Range Names are one of the coolest tool that Excel has provided. I have not yet written about it, but will soon do it. Range Names can actually make your life very simple when writing complex formulas and making heavy and dynamic Excel models.

Saturday, July 4, 2009

Concept of Data Validation

Concept of Data Validation?
Data Validation is a tool provided by MS Excel that allows you to validate data that is entered by a user. The validation happens once the user enters data and hits the return key (enter key).

Why should I use data validation?

Data validation, though a simple tool, can be of lots of help. It’s very core function (of validating data) can be applied to many other tasks. Here are a few things that can be done using data validation tool:

The very basic function of data validation is to validate the data. This tool can be used to get correct data from the users. It can also be used to get desired data, by the way of limiting the cell input using custom input
In large shared spreadsheets, which are updated real-time by more than one user, certain columns can have restricted data entry. This can limit the incorrect entries to the cells
Keeping a check on inconsistencies. This can be achieved by creating a list of certain words, which go in a certain range of cells and these can be suggested to users by giving them an option to select from drop-down menus. These in-cell drop down menus can be created using data validation
Custom error / alert / input messages can be displayed to users after or before a cell entry is made. Error messages are shown when an incorrect entry is made. The input message is shown when a certain cell is selected, where the input is required. On selection a message can be displayed which can show instructions or warnings etc.
In-cell drop down menus. This is one of the most useful and my favorite uses of data validation

What validations can be applied to the cells?
As I told earlier the cells entries can checked for many types of validations. For example, I can restrict a certain range of cells to accept only numeric value; I can also limit the range within which the cell values can be input. I can also restrict the cells to accept only date values or time values, etc.

Here is a detailed explanation of what exactly you can do with this tool:
Validations: The first tab of the data validation tool is where you can define you validation rules. These are the following rules that can be applied on the cell or range of cells –

Whole numbers: You can use this option in case you want the cell to accept only whole numbers as input from the users. You can also define certain limits to the whole numbers also, such as minimum and maximum limits of the range, which is acceptable, other rules such as equal to, more than, less than, not equal to, etc.
Decimal numbers: This option allows you to restrict the cells to accept numeric values, which can also have decimal values. The difference between this option and the previous option is that, in previous option only whole numbers could be input and with this option you can allow decimal numbers also. You can apply same rules on the decimal values as you can apply on whole numbers.
Date / Time: These are 2 different options with which you can restrict the cell entries to be either date or time value. Again, on these options also you can apply equal to, not equal to, more than, etc. rules
Text Length: This option restricts the cells to contain only a limited length of text. The text here includes all valid characters in Excel.
List: This option allows you to validate the cell entry with a pre-defined list. You can define your list, with words or phrases, which you wish should only be entered in the cells. This option allows you to provide the user as in-cell drop down menu with which one can select a particular item from the list. This way you can restrict the data input in the cell together with giving the user a drop down list of required items.

Message Alerts / Error Alerts: This are alerts which displayed to users once either the user clicks on the cell, which has data validation active, or enters invalid data (as per the validation rules). Through error alerts you can either give a ‘Stop’ message or ‘Warning’ message or simply an ‘Information’ message to alert the user about the type of message input.

How to apply data validation?
The following steps will guide how one can apply data validation to a range of cells. The following steps can be applied in Excel 2003 and Excel 2007 as well:

Select the cell or range of cells to which data validation is to be applied
Go to 'Data' menu and then click on 'Validation'. The Data validation wizard will open up
On the validation tab, click on the drop down menu, where currently 'Any Value' is shown. Currently, the cell can accept any value
You can choose from the various validations that are available in the drop down list
Once you select the validation type, another drop down list will show up (depends on which option you selected on previous step) which will list the various rules that can be applied on the validations
In case you would like to activate Alert or Error messages, those can be defined in the next tabs

In the below image, I have applied validation for whole numbers, between the range of 0 to 100.

Note: Though data validation is one of the very useful tools in Excel, it also could not keep itself from some limitations. One of the major limitation (or call it a bug) is that it does not work when some data is pasted using CTRL+C and CTRL+V. When some data is pasted on the cells, which have validation, the cell entries are not validated.

If you are familiar with the concept of Data Validation or have worked with this concept after reading this post, I would like to know about it. Share your experience using data validation tool with us, issues you faced and how did you solve it.

Wednesday, July 1, 2009

How to calculate subtotals using Array functions

I have discussed basic concept of array functions, its advantages and disadvantages in my previous post. Now, let us now talk about an example where we generally get to use Array Functions.

Example:
In our example, we have some data related to sales of 3 products - Product A, Product B and Product C. The sales force is active in all 4 geographic regions - East, West, North and South. Now we need create a small report (just 4 cells of data) that will summarize the whole data. This report will have subtotals by Region and Product. Follow the below steps for this:

Create an Excel spreadsheet with some sample data (alternatively you can download this spreadsheet) which has these columns - Region in column A, Product in column B and Sales in column C. Also, fill some sample data for these 3 columns
Create a small matrix table with columns titled as each product and rows titled as each region. The matrix should look something like this:

Let us now try to formulate one single formula for subtotals that can be used with all the cells in the matrix. Here is the formula that I used for calculating the totals for each cell in the above matrix:

=SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))

..and i entered this function as an array function, i.e. using CTRL+Shift+Enter

Let us understand what does each part of the formula does.

=SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))

This portion of function gets executed first. This part checks if the Product column has 'Product A' in it. If yes, it lets the first cell go for next level of validation (next step of calculation), If the cell does not have 'Product A', then 0 value is passed on to next level
Note: I hope you remember I discussed in my previous post that array functions process each cell at a time. To refresh your concept read more about Array Functions here and here.
=SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))
Similar to previous part of the formula, this part check the cell for Region. If the cell has region as 'East'. If true, then the cell is passed on to next level of formula, i.e. Sum() function. If the cell does not have 'East', then 0 value is passed on to next level
=SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))

Here, the value passed from previous 2 steps gets accumulated and is added up. (Learn more about Sum() Function and other Math Functions)

Now, you copy and paste this formula to other cells of the matrix and you will get the required results.

Note: The formula will take required cells from the column and row heads for validation by itself. This has been done through using 'Relative Reference' technique of writing formula in Excel. I will take this up in my later posts.

Wednesday, June 24, 2009

Usage of Array Functions

In my previous post I explained the concept of Array functions (or formulas - please note that I will be using these two words interchangeably). Now, I will take up different ways in which Array functions can be used.

As, we know an array is a collection of data points, not one single data point. Now you can do any processs an array in two ways - either to get a single data point result or a group of data points as result after processing the array (processing array means = doing some calculations, or any kind of processing).

Here are a few examples:

Calculating average of a series based on multiple conditions, where the conditions are applicable on mupltiple data columns
Calculating count of a column with one or more conditions applicable on column other than the one on which count is to be performed

Though above examples can be solved using functions such as - SumIF(), AverageIF(), CountIF(), etc., but if the above examples get a little complex, then array functions are the best to solve.

Before you can start writing array functions, I would like to list down some advantages of using them. Here are a few advantages that array functions have over other functions:

The most importance advantage of array functions, especially over functions like SumIF(), CountIF() etc., is that they can be used to perform calculations based on complex conditions
Array functions when used over a range (and in multiple cells) would prevent any changes which happen by mistake, as a set of functions if input as an array, cannot be deleted/edited unless all cells within the array range are deleted
These functions also ensure consistency. As array function requires the ranges in the function to be of same length, it ensures that the arguments provided with the function are correct and are not of unequal lengths

Though array functions can solve most of your complex calculations which otherwise MAY not be possible using other functions, they also come with certain restrictions/limitations that make them not so very helpful in certain situations. Here are some disadvantages associated with array functionsl:

Array functions are processed with each cell (or cell combination) processed individually. This requires system to perform same calculation over again for the all the cells mentioned in the range. This leads the system to take that extra time to calculate all array ranges again.

In case there are many array functions used in a worksheet, it can lead to re-calculations taking lot of time.
Array functions cannot be worked on whole columns or whole rows. I.e., you cannot use range names such as - A:A or B:B or C:C, etc.
These functions are a bit difficult to understand and implement. If they are not properly worked with, they can result in results which are not accurate, and it would be difficult for one to understand what is leading to inaccurate results

Additionally, whatever that can be performed through array functions, most of it can be performed using normal functions. But certainly, the ones which can be solved using only array functions, they are best performed using array functions only.

In my next post, I will take a few more examples, simple and complex, to explain how can one use array functions.

Sunday, June 21, 2009

What are Array Functions or Array Formulas

Array functions are one of the powerful tools that Excel has provided. They are able to achieve many things that one cannot achieve by working with functions normally. Here is a detailed discussion about what are Array Functions.

Array: is basically a set of values. It is a popular concept in computer programming.

Usage of Array functions: In MS Excel, there aren't separate array functions as such, as it might seem from reading the term - 'Array Functions'. Rather, there are a few functions which can be used as array functions.

As we know that arrays are 'a set of values', the excel functions which are used on ranges, can also be used as array functions. These functions, when used as array functions, treat the range as arrays and process them accordingly.

A simple example:

Open up a worksheet and work with this example to understand the concept.

Now do this -

In the cells A1 to A10 write the numbers from 1 to 10
Similarly in cells B1 to B10, write the same numbers

Now suppose, you want to multiply each cell in column A with the corresponding cell in column B and then sum it. One of the ways is to use the Sumproduct() function (I will discuss this function in later posts). And the other way is to first use column C to calculate the products of the cells and then sum them in column C.

We will not discuss here why not to use Sumproduct() as it is a better option for such kind of problems. But array functions can achieve what Sumproduct() cannot. Just to explain the concept of array functions, let us try to achieve sum of products using array functions.

In the second approach note that each cell of the range (cells 1 to 10) are processed individually. This is the essence based on which the array functions are based, i.e., whenever you want to process each cell in range individually but in a batch, then array functions are used.

Now do this:

In cell A11 type this: =Sum(A1:A10*B1:B10)
Now hit enter/return key

This will give you an error as though we have used the range in this function, but it has not been used as an array function.

Let us repeat the above steps, but with a simple change:

In cell A11 type this, but do not hit enter): =Sum(A1:A10*B1:B10)
Now hold Ctrl+Shift keys and while holding them, hit enter/return key

A function is used as an array function by inputting the function using Ctrl+Shift keys. These two keys will display curly brackets '{}' around the function, but these actually are not there in the cell. They just show up in the cell.

Now cell A11 will show the result of the function as 385. This function worked something like this - it multiplies each cell from range 1 and range 2, and then adds up the products to get the sum.

Next: Ways in which Array Functions can be used