Archive for September 2009

Home / Archive for September 2009

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

Function Rows

Dhakkanz 2:18 AM Add Comment

Rows() This function is used to determine the number of rows in a given reference (the range of cells referred to in the function). ...

What is an alternative to multiple-nested IF-Then-Else functions?

Dhakkanz 2:00 AM Add Comment

IF-Then-Else - one of the functions that Excel has taken from the programming languages. And it is one of those things that one learns as ba...

How to locate formulas in a worksheet

Dhakkanz 12:34 AM 8 Comments

In one of my earlier works, I was supposed to crack an already-built spreadsheet. The workbook was basically a complex model that contained ...

Excel Matic on Mobile

Dhakkanz 12:22 PM Add Comment

Hello readers! Excel Matic is now on Mobile. Yes, you can now read all your favorite posts on your mobile phone. No, you do not have to subs...

Subscribe to: Posts ( Atom )

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.

Sunday, September 6, 2009

Function Rows

Rows()

This function is used to determine the number of rows in a given reference (the range of cells referred to in the function).

Syntax:

= Rows (Reference_range)

Reference_range: This is the range of cells for which the number of rows is to be determined

Examples:

The function simply returns the number of rows within the range which is passed on as reference with the function. For example:

Rows($A$3:$A$3) - result will be: 1 {as there is only 1 row 'A' within the given range}
Rows($A$35:$A$38) - result will be: 4
Rows($A$3:$A$5) - result will be: 3
Rows($A$3:$F$8) - result will be: 6

Application:

Many a times, you have to write a range of formulas (a similar formula over a range of adjacent columns or rows), which should have a number which is dynamic and changes as the row changes. For example - you have to write a formula in cell A5 and the next formula goes in A6, A7, and so on. But in this range of formula, you want one of the parameter to increment by itself. In such a case you can use Rows() function for the dynamic part.

Here is the way the Rows() function would be used in the above example :

= Original formula (Parameter1, Rows($A$5:A5))

Now, when the above formula will be copied to next cell, i.e. A6, it will change to:

= Original formula (Parameter1, Rows($A$5:A6))

Note that $A$5:A5 changes to $A$5:A6 and this will result in 2 which will be passed on as a parameter to the original formula}

What is an alternative to multiple-nested IF-Then-Else functions?

IF-Then-Else - one of the functions that Excel has taken from the programming languages. And it is one of those things that one learns as basics of programming.

If-Then-Else (or simply IF function) is used to make a choice based on condition(s) provided by the user. For example:

In one of the cells, I want to show "True" IF cell A1 contains 1, otherwise it should show "False".

To achieve the above, one can write a simple formula in Excel, i.e. - =If (A1=1,"TRUE","FALSE")

Great! Now let us change the above task a bit, something as - the cell B1 should show the following text based on the number input:

If cell A1 = 1, then the cell should read "One"
If cell A1 = 2, then the cell should read "Two"
If cell A1 = 3, then the cell should read "Three"
If cell A1 = 4, then the cell should read "Four"
Otherwise should say - "Above 4"

In this case, one can write a multiple IF formula as this:

= IF (A1=1,"One", IF(A1=2,"Two", IF(A1=3,"Three", IF(A1=4,"Four","Above 4") ) ) )

Cool! It works. Now let us make things more complex. Lets keep 7 conditions instead of 4. You can still write the IF function on above lines and it will work fine.

But what if there are more than 7 conditions? And what about the processing time?

Excel does provide you with a useful tool such as IF function, but it comes with a limitation. The limitation is that a cell in Excel can contain only 7 nested IF functions, not more than that.

The other issue with using multiple IF functions is that it makes processing a bit slow, as there are 7 IF functions to deal with. You will not be able to notice the speed if you have only one cell with multiple IF functions, but in case you had a sheet with many such cells, the calculations will take time.

The alternative to IF functions is to use Choose function. This functions allows you to provide several options within the function, which will be processed on the basis of number provided to it as input. I have provided more text on this function in a separate post.

Thursday, September 3, 2009

How to locate formulas in a worksheet

In one of my earlier works, I was supposed to crack an already-built spreadsheet. The workbook was basically a complex model that contained a lot of data and has almost 40 worksheets and each worksheet had lots of numbers.

Now, to understand the functioning of each of the sheets, I had to find out which cells on each sheet contained functions / formulas! But with each sheet (MS Excel 2003) having 256 columns and 65536 rows (which means 16777216 cells!!) it was really impossible for me to dig down each cell.

But MS people are very intelligent. They did not provide you only a tool to create spreadsheets, but also a tool which one can use to decipher a the tool.

To view which cells in a worksheet contains functions, one can use this tool. This tool basically toggles the worksheets view from normal to a view function view*. The function view can be activated by pressing the following key combinations: CTRL and ~

The key above the TAB key is used in the above key combination. The same combination is also used to bring back the view to normal view.

Notes: *The functions view is not a standard terminology. I have used this terminology to be explain the context.

Wednesday, September 2, 2009

Excel Matic on Mobile

Hello readers!

Excel Matic is now on Mobile. Yes, you can now read all your favorite posts on your mobile phone. No, you do not have to subscribe to any mobile service. We have created a mobile website for you.

Here goes the link to the mobile version of Excel Matic - excelmatic.mofuse.mobi.

You can access this website on immediate basis and you will be able to see the latest posts on Excel Matic.