Archive for July 2009

Home / Archive for July 2009

Function Choose()

Choose() This function is used to perform an action from a defined list of actions, based on the index number provided to the function. Synt...

Lookup Functions

Dhakkanz 11:01 AM Add Comment

Here is a list of essential Lookup functions that one should know to be a powerful Excel user: Column() Columns() Address() Row() Rows(...

Function Row()

Dhakkanz 12:08 AM Add Comment

Row() This function is used to determine the row number of a given reference (the cell referred to in the function). Syntax : = Row (Refere...

Function Column()

Dhakkanz 11:37 PM Add Comment

Column() This function is used to determine the column number of a given reference (the cell referred to in the function). Syntax : = Colum...

Function Address()

Dhakkanz 1:25 AM 22 Comments

Address() This function is used to custom build a address range in Excel. It converts a text into actual address based on given parameters. ...

Lookup Functions - New list of functions released

Dhakkanz 1:46 AM Add Comment

Today I am starting to write about Lookup functions, that include VLookUp(), HLookUp(), InDirect(), etc. Lookup functions are one of the mos...

Solve this email query - populating values based on max value

Dhakkanz 12:53 AM 6 Comments

I recently received a query from one our readers - Pedro Costa. Here is the query that I received through email: Email : Hi Mohit, The...

Free e-Book: "Excel Matic - Excel Math Functions"

Dhakkanz 11:31 PM 2 Comments

We had recently published an e-Book which has essential Math functions explained, as on this blog. The e-Book talks about a handful of esse...

A re-visit to Math Functions

Dhakkanz 2:42 PM Add Comment

By now we have discussed a good number of Math functions , Text functions and some advanced concepts . Now let us take some of the math fun...

Inviting questions related to Excel

Dhakkanz 2:40 PM Add Comment

This is for all the readers at Excel Matic – I have been getting a few emails from some Excel Matic subscribers who ask me particular questi...

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

Subscribe to: Posts ( Atom )

Sunday, July 26, 2009

Function Choose()

Choose()

This function is used to perform an action from a defined list of actions, based on the index number provided to the function.

Syntax:

= Choose (Index_number, Option1, Option2, ..., OptionX)

Index_number: This is the numeric value that is passed on to the function to choose from the list of actions provided to the function

Option1,2...X: List of actions / outputs / results from which one should be choosen a result based on the Index number

Example:

Let us take an example where the function in cell 'B1' is written as -

= Choose (A1,"One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten")

Now, let us apply validation to cell A1, so that this cell should accept only values between 1 and 10. Learn here about applying data validation.

This is just to avoid any errors in cell B1 where Choose() function is used, as we have defined only from 1 to 10.

Now you can input any value in cell A1 and you would see the results in cell B1. The cell B1 will give text name of the number entered in cell A1. Such as:

If cell A1 = 1 then the cell B1 will show - One
If cell A1 = 3 then the cell B1 will show - Three
If cell A1 = 7 then the cell B1 will show - Seven
If cell A1 = 5 then the cell B1 will show - Fice

Here is a snapshot of the same example (i have used cell C1 and D1 to optimize the space taken by the screen shot):

Application:

This function is generally used where the limitations of If() function creep in. As we know that If() cannot be used more than 7 times in a cell. Now, if really need to have more than 7 options, then choose is the best option available.

It can also be used to hard-code a list of options within a cell. For example, a worksheet has a drop-down menu from which a selection can be made. The drop-downs, as you would know, gives only the index of option selected and not the selection as such. In such cases, this function can be used to manipulate the index number provided to fetch the text of the option selected.

Lookup Functions

Here is a list of essential Lookup functions that one should know to be a powerful Excel user:

Thursday, July 23, 2009

Function Row()

Row()

This function is used to determine the row number of a given reference (the cell referred to in the function).

Syntax:

= Row (Reference_cell)

Reference_cell: This is the cell for which the row number is to be determined

Examples:

The function simply returns the row number for the cell which is passed on as reference with the function. For example:

Row($A$3) - result will be: 3
Row($C$35) - result will be: 35
Row($AA$45) - result will be: 45
Row($BB$125) - result will be: 125

You can also use this function to determine the row in which the function itself lies. For example, If this function is written in cell $F$5, then the function should return this cell's row number. For this the function can be simply written without the Reference_cell. It will be:

=Row()

...and the function will return the row number of the cell in which it is contained.

Application:

Though this function is very simple to use, but is a very useful functions. This functions is mostly applied with other advanced lookup functions, such as vlookup(), hlookup(), address(), etc. This functions works similar to Column() function.

Wednesday, July 22, 2009

Function Column()

Column()

This function is used to determine the column number of a given reference (the cell referred to in the function).

Syntax:

= Column (Reference_cell)
Reference_cell: This is the cell for which the column number is to be determined

Examples:

The function simply returns the column number for the cell which is passed on as reference with the function. For example:

Column($A$3) - result will be: 1 {as column A is the first column}
Column($C$35) - result will be: 3
Column($AA$3) - result will be: 27
Column($BB$3) - result will be: 54

You can also use this function to determine the column in which the function itself lies. For example, If this function is written in cell $F$5, then the function should return this cell's column number. For this the function can be simply written without the Reference_cell. It will be:

=Column()

...and the function will return the column number of the cell in which it is contained.

Application:

Though this function is very simple to use, but is a very useful functions. This functions is mostly applied with other advanced lookup functions, such as vlookup(), hlookup(), address(), etc. Take a look at this query which I received from one of the readers of the Excel Matic. I have made use of this function. (Note that the example uses a few functions which are not yet described on Excel Matic, but will soon be).

Monday, July 20, 2009

Function Address()

Address()

This function is used to custom build a address range in Excel. It converts a text into actual address based on given parameters. The function can return address for a single cell and a range of cell as well.

Syntax:

= Address (Row_num, Column_num, Reference_type, Address_type, Sheet_name)

Row_num: This value determines the row number that the address should refer to
Column_num: This value determines the column number that should be referred by the address
Reference_type: This single-digit parameter determines which type of reference should the address have. The reference codes are - 1=absolute reference, 2=absolute row/relative column reference, 3=relative row/absolute column reference and 4=relative reference. The default reference is 'Absolute' reference
Address_type: This value determines the style of address that should be returned. There is no effect of this parameter on its functioning. The address type codes are - 1 or TRUE = A1 type reference, 0 or FALSE = R1C1 style reference. In case this parameter is not available, the function adopts the A1 style reference, even if R1C1 is the default style in the sheet
Sheet_name: As the name suggests, this parameter provides the sheet name which should be included in the address as its part. This is used when the address should refer to a range which is external to the given sheet

Note: In the above syntax, Reference type, Address Type and Sheet name are optional parameters. Any of these can be mentioned in the function independent of each other

Examples:

Here are a few examples of using Address() function

Address(1,2) - result will be: $B$1
Address(2,1) - result will be: $A$2
Address(2,1,1) - result will be: $A$2
Address(2,1,2) - result will be: A$2 (note the reference style, the reference to column 'B' is not absolute)
Address(2,1,4) - result will be: A2
Address(2,1,1,1) - result will be: $A$2
Address(2,1,1,TRUE) - result will be: $A$2
Address(2,1,1,0) - result will be: R2C1
Address(2,1,1,FALSE) - result will be: R2C1
Address(2,1,1,1,Sheet1) - result will be: Sheet!$A$2 (note that the address now contains sheet name also)
Address(2,1,1,1,Sheet_new) - result will be: Sheet_new!$A$2
Address(2,1,,1) - result will be: $A$2 (note that the third parameter has been ignore, but its place holder remains there as a blank indicated by 2 commas)

Application:

The address function is generally used where a cell range is used, but the range is dynamic in itself. Mostly, it used with the Indirect() function. The Indirect() function is used to convert the text address into actual reference.

For example, in a worksheet, a column should populate values which are dependent on a certain condition. Now this requires the range of cells to be dynamic in the way that it should dynamically change based on the condition mentioned. Here is an example of the application of Address() function with Indirect() function.

Sunday, July 19, 2009

Lookup Functions - New list of functions released

Today I am starting to write about Lookup functions, that include VLookUp(), HLookUp(), InDirect(), etc. Lookup functions are one of the most useful and in-demand functions.

These functions are used in lot of applications, their demand in today's market is very high, one of which is financial industry. And not just financial research, these functions are very famous in other industries also, at all those places where there is high use of Excel spreadsheets. So, knowledge of these functions and their application will provide you with an edge to clear the interviews and also will make you feel confident in using these functions.

So, I will be writing on one function in every 2 days, so that you guys can get time to catch-up with each function and understand it properly. Also, starting from these functions, going forward I will also talk about the application of each of these functions, together with examples. This will provide you with some live examples of using each of these functions. In case you would like to submit any cases which you think could be suitable examples for these functions, do let me know. I will put them up as sample applications.

To start with, today I have written about Address() function and have also mentioned its application with its explanation.

Friday, July 17, 2009

Solve this email query - populating values based on max value

I recently received a query from one our readers - Pedro Costa. Here is the query that I received through email:

Email:

Hi Mohit,

The purpose is to copy the values from beginning in AA19 till the end of the column to the column painted in yellow (G19 till …). The column to copy is determined by the Maximum value between Cell J2 to cell DD2.

I hope You understand the problem. Thanks in advance for your attention and help.

Pedro

Explanation of the query:

Here is the attachment - PedroCosta_query.xls (I have cleaned up the columns which are not required and have pasted the formulas as values to maintain confidentiality of Pedro's data).

In this spreadsheet Pedro has some data in a table spread across columns 'B' to 'I'. And there is some data through columns - 'K' to 'DE' and 'DG' to 'HA'. There are a few rows above this data which does some calculations on the given table.

What is required - In the sheet attached, Pedro needs to populate the column 'G' with that column for which row '2' gives the highest value. For example, if 'T2' cell has the maximum value, then range 'T19' to 'T166' should be populated in the corresponding range in the column 'G'.

So, guys I leave this query open to all of you. Help Pedro in solving this problem. You can either email me the solution or can also post your replies as comment to this post. For those who will send me an email, I will post them as comment myself.

Monday, July 13, 2009

Free e-Book: "Excel Matic - Excel Math Functions"

We had recently published an e-Book which has essential Math functions explained, as on this blog. The e-Book talks about a handful of essential Math functions in a very easy and descriptive manner. There is a mix of basic to intermediate level functions in the e-Book.

Excel Matic is now giving out this e-Book for free as a token of thanks to its registered users for being a support to me. For those who are still not registered with us, subscribe to Excel Matic and you will receive your free copy of the e-Book.

Here is a link to sample of the e-Book 'Excel Matic - Excel Math Functions'.

You all must be thinking that there must be a catch behind this, and to that my answer would be - there is no catch. The e-Book is really for free for all the subscribers of Excel Matic.

PS: For those who are already subscribed to Excel Matic's feed, you would have already received your copy of the free e-Book.

Saturday, July 11, 2009

A re-visit to Math Functions

By now we have discussed a good number of Math functions, Text functions and some advanced concepts. Now let us take some of the math functions that we have discussed so far.

[Before I move ahead and talk about various functions, I would like to touch upon an issue which we generally face while using Excel. Many a times we tend to forget how a particular function is written, i.e. the syntax of functions, though we know how to use the function well. Microsoft understands this problem well and has provided a very useful, but not so popular, tool for this. You can recall the syntax of any function by pressing CTRL+A while in the middle of writing that particular function and WHOAA..you now know how to write that function. Read more about this topic here.]

In our initial days we talked about some basic, but useful, math functions. The list includes basic functions such as Sum(), Average(), Product(), Count(), Counta() etc.

We also talked about certain advanced level math functions. These functions are like – Sumif(), ABS(), SumSQ(), etc.. The Sumif() functions perform sum with some conditions applied to them.

For example, if I want to add a number of entries in range A1 to A10 (containing only numbers), for only those cells that have ‘3’, then I would need to use Sumif(). The formula for this would be - =Sumif(A1:A10,3).

If you wanted to convert a given number into an even or an odd number, you would need Even() and Odd() functions.

All of the above functions were such which can be used for performing some or the other function. There are two functions which are used to fetch random numbers – Rand() and Randbetween().

Randbetween() functions is available in Excel 2007 (and is not there in Excel 2003). Rand() is used to generate a random number between the range of 0 to1 and is a decimal number. Whereas, Randbetween() returns random integers between the range provided by the user.

So this wraps the list of Math Functions which we have discussed since the beginning of the blog. I will also be summarizing all the text functions that we have discussed here in our earlier posts.

In case you would like me to include any other specific function in this list, please feel free to send e a note, or make a comment on any of the blog entries. I will include that function/topic in my list of publications.

In my next post, I will talk about various Text Functions that we have discussed till date.

Friday, July 10, 2009

Inviting questions related to Excel

This is for all the readers at Excel Matic – I have been getting a few emails from some Excel Matic subscribers who ask me particular questions about some or the other stuff related to Excel. And I do reply them with the solutions, if I can solve them.

But this is boring, don’t you agree? Let us make this a bit interesting – Lets crack these questions in an interactive way. I will post questions which I will receive from the readers on the blog itself, in a different section altogether, and we all would share with each other the best possible solution.

So what is holding you, get started and send us all the questions that you have related to Excel. You can post questions, situations you faced while working with Excel and how you solved them, or whatever you would like to share with all on Excel Matic

PS: Do not hesitate to send your questions, even if they are they are very basic. We will not disclose your name if you would not like to.

Mohit

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.