Excel Matic

Function Indirect()

2009-09-19T19:58:00.001+05:30

Indirect()

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.

Function Index()

2009-09-16T01:13:00.000+05:30

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.

Series on important functions - Lookup Funtions

2009-09-13T21:43:00.000+05:30

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.

Function Rows

2009-09-06T02:18:00.000+05:30

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?

2009-09-06T02:00:00.001+05:30

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.

How to locate formulas in a worksheet

2009-09-03T00:34:00.002+05:30

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.

Excel Matic on Mobile

2009-09-02T12:22:00.002+05:30

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.

How to define Range Names?

2009-08-31T00:04:00.005+05:30

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.

What are Range Names?

2009-08-30T20:08:00.005+05:30

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?

Autofilters in Excel

2009-08-19T13:05:00.006+05:30

This is a recent question that I received from one of our readers, Peter.

Question: Can we apply filters on more than one table on the same worksheet?

Answer: No, we cannot have more than one Auto-filters on a same sheet. Even if the tables are separated by blank row(s), one cannot have auto filters applied on 2 tables at a same time. This issue has not been covered in Office 2007 also.

The only way, that I think, can make this possible is through VBA coding. Though I can do some basic coding, but have never tried it.

Does anyone interested in trying out this code? Or does anyone know any other way around this issue?

Working out with Data Validation list

2009-08-06T22:07:00.004+05:30

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.

Function Columns

2009-08-04T16:35:00.005+05:30

Columns()

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

Syntax:

= Columns (Reference_range)

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

Examples:

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

Columns($A$3:$A$3) - result will be: 1 {as there is only 1 column 'A' within the given range}
Columns($A$35:$D$35) - result will be: 4
Columns($A$3:$A$5) - result will be: 1
Columns($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), which should have a number which is dynamic and changes as the column changes. For example - you have to write a formula in cell A5 and the next formula goes in B5, C5, 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 Columns() function. Let us say that

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

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

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

= Original formula (Parameter1, Columns($A$5:B5))

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

Function Choose()

2009-07-26T11:08:00.005+05:30

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

2009-07-26T11:01:00.004+05:30

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

Function Row()

2009-07-23T00:08:00.003+05:30

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.

Function Column()

2009-07-22T23:37:00.009+05:30

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

Function Address()

2009-07-20T01:25:00.000+05:30

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.

Lookup Functions - New list of functions released

2009-07-19T01:46:00.001+05:30

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.

Solve this email query - populating values based on max value

2009-07-17T00:53:00.000+05:30

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.

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

2009-07-13T23:31:00.003+05:30

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.

A re-visit to Math Functions

2009-07-11T14:42:00.001+05:30

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.

Inviting questions related to Excel

2009-07-10T14:40:00.005+05:30

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

Concept of Data Validation

2009-07-04T23:58:00.023+05:30

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.

How to calculate subtotals using Array functions

2009-07-01T23:41:00.012+05:30

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.

Usage of Array Functions

2009-06-24T00:45:00.004+05:30

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.

A minor mistake in my previous post

2009-06-24T00:32:00.005+05:30

Hi everyone,

In one of my earlier posts titled - "What are Array Functions or Array Formulas", there was one small mistake in the example.

In the example given in that post I have mentioned the function to be used as:

=Sum(A1:A10,B1:B10)

The error lies in this example. The function actually should be:

=Sum(A1:A10*B1:B10)

Appologies for the mistake.

PS: I have corrected this mistake in the original post also.

What are Array Functions or Array Formulas

2009-06-21T23:15:00.002+05:30

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

Function SumSQ()

2009-06-21T21:25:00.001+05:30

SumSQ()

This function calculates the sum of squares of the numbers provided as arguments to the function. The arguments provided to this function can be numbers, range of numbers, arrays, etc.

Syntax:

= SumSQ (Number1, Number2, Number…)

Number1, Number2, Number: These are numbers for which sum of squares is to be calculated

Example:

= SumSQ(1, 2, 3) - result will be - 14

This function does calculation as – ((1*1) + (2*2) + (3*3))

Function RandBetween()

2009-06-21T21:21:00.003+05:30

RandBetween()

This function returns a random number between a given range of integer numbers

Syntax:

= Randbetween (Bottom_number, Top_number)

Bottom_number: The smallest number that the function will return
Top_number: The largest number that the function will return

Example:

= Randbetween (3,100)

Note: This function is a volatile function, i.e. this function is re-calculated whenever the worksheet gets updated. A heavy use of this function in a workbook can make your workbook calculations slow

Function Rand()

2009-06-21T14:05:00.007+05:30

Rand()

This function returns an evenly distributed random number between 0 and 1

Syntax:

= Rand ()

Note: This function is a volatile function, i.e. this function is re-calculated whenever the worksheet gets updated. A heavy use of this function in a workbook can make your workbook calculations slow.

In Excel 2007, there is also an advanced version of this function: Randbetween()

Function Odd()

2009-06-21T14:04:00.022+05:30

Odd()

This function returns the nearest ODD number which is - greater than the original number if the original number is positive and lesser than the original number if original number is negative

Syntax:

= Odd (Number)

Number = is the number which is required to be rounded off to nearest odd number

Example:

= Odd (2) - result will be: 3
= Odd (3) - result will be: 3
= Odd (5) - result will be: 5
= Odd (8) - result will be: 9
= Odd (-2) - result will be: -3
= Odd (-3) - result will be: -3
= Odd (-5) - result will be: -5
= Odd (-8) - result will be: -9

Function Sign()

2009-06-21T14:04:00.021+05:30

Sign()

This function is used determine the sign (negative or positive) of a given number. It returns -1 for negative number, 1 for a positive number and, 0 if the number provided is 0

Syntax:

= Sign (Number)

Number: Number for which sign is to be determined

Example:

= Sign (-21) – result will be: -1
= Sign (-1) – result will be: -1
= Sign (21) – result will be: 1
= Sign (0) – result will be: 0

Function SQRT()

2009-06-21T14:04:00.019+05:30

SQRT()

This is a simple function which is used to convert find a square root of a number

Syntax:

= SQRT (Number)

Number_text: Number for which the square root is to be returned

Example:

= SQRT (4) - result willl be - 2
= SQRT (49) - result willl be - 7
= SQRT (11) - result willl be - 121

Function Value()

2009-06-21T14:04:00.016+05:30

Value()

This function is used to convert a number, stored as a text, into value

Syntax:
= Value (Number _text)

Number_text: Number stored as a string/text

Example:

In the above example, I have used two functions deliberately to show the actual application of this function. In the above example, the Mid() function extracts the numbers as text, from the string – ‘B99’.

And the function Value() converts the number, extracted as text back into a proper number. The difference between the two is that a number stored as text cannot be used for any calculations, whereas a proper number can be used for calculations

Note: I have used two functions in two different cells in the above example. This, instead can be written as:

= Value( Mid( B3,2,2) )

Function Trim()

2009-06-21T14:04:00.011+05:30

Trim()

This function removes extra blank spaces from the given text. It removes text from text from – the start of the text string, at the end of the text string, and any extra spaces between the words within a text string

Syntax:

= Trim (String)

String: a text string that may contain any extra spaces which needs to cleaned from the string

Example:

Note: In the above example, the extra spaces (represented by ‘|’ symbol) were removed using the Trim() function.

Function Substitute()

2009-06-21T13:29:00.007+05:30

Substitute()

This function replaces existing text within an old text, with a new text string. Note that this function is case sensitive, i.e. it will treat 'A' and 'a' as different

Syntax:

= Substitute (Base_Text, Replace_Text, New_Text, Instance_num)

Base_text = Is the main text within which a part of the text is to be replaced

Replace_text = Is that part of the Base Text which is to be replaced

New_text = New text string that will replace the old text within the Base Text

Instance_num = The instance of the Replace_text which is to be replaced. This is optional - if it is mentioned, then that instance of the Replace Text gets replaced, else all instances of the Replace Text gets replaced

Example:

For the below examples, let us assume that cell A1 contains text - My name is Excel Matic

= Substitute (A1, "Excel Matic", "Mohit Khurana") - result will be - My name is Mohit Khurana
= Substitute (A1, "e", "u") - result will be - My namu is Excul Matic
= Substitute (A1, "e", "u", 2) - result will be - My name is Excul Matic

Note: In the above examples, note the highlighted text. In example 3 - only the second instance of the letter 'e' has been replaced with 'u'

Function Search()

2009-06-21T13:12:00.005+05:30

Search()

This function returns the position at which a specified substring (character or a set of characters) is first found. Note that it returns position of the first occurence of the substring. This function is a non-case sensitive function, i.e. it considers 'E' and 'e' as same

Syntax:

= Search (Substring, Base_string, Start_pos)

Substrin = Is the string that is to be found within the Base String

Base_string = The string from which the substring is to bea searched / located

Start_pos = Position within the Base String from where the search should start finding

Example:

= Search ("E", "Excel Matic", 1) - result will be - 1
= Search ("m", "Excel Matic", 1) - result will be - 7
= Search ("E", "Excel Matic", 2) - result will be - 4 (though we started finding 'e' from second position, but the functions returns the position relative to original string, without ignoring the initials characters)
= Search (" ", "Excel Matic", 1) - result will be - 6 (Spaces, special characters can also be found using this function)
= Search ("E", "Excel Matic", 20) - result will be - #VALUE! (This will return an error as it is not able to find 'E' beyond the original length of the text)
= Search ("G", "Excel Matic", 1) - result will be - #VALUE! (This will return an error as it is not able to find 'G' which is not present in the Base String)

Function Mid()

2009-06-21T12:59:00.007+05:30

Mid()

This function is used to extract a set number of characters (a substring) from the within a string

Syntax:

= Mid (Base_string, Start_pos, Num_chars)

Base_string = The stringfrom which the characters are to be extracted

Start_pos = Position within the Base String from where the substring is to be extracted

Num_chars = Is the length of the substring that is to be extracted from the Base String

Example:

= Mid ("Excel Matic",1 , 1) - result will be - E
= Mid ("Excel Matic",1 , 2) - result will be - Ex
= Mid ("Excel Matic",1 ,31) - result will be - Exc
= Mid ("Excel Matic",1 , 4) - result will be - Exce
= Mid ("Excel Matic",1 , 5) - result will be - Excel
= Mid ("Excel Matic",7 , 1) - result will be - M
= Mid ("Excel Matic",7 , 2) - result will be - Ma
= Mid ("Excel Matic",7 , 3) - result will be - Mat
= Mid ("Excel Matic",7 , 4) - result will be - Mati
= Mid ("Excel Matic",7 , 5) - result will be - Matic

Note: In case you specify a position which is out of the range of the length (i.e., is larger than the length of the string, this function will return a null value. And, in case you return the length of substring which exceeds the number of characters post the start-position, the function will only return a substring till the end of Base String.

Function Len()

2009-06-21T12:49:00.007+05:30

Len()

This function returns the length of the string, in terms of number of characters in it, including spaces, speacial characters, line feeds, etc.

Syntax:

= Len (String)

String = The string for which the length is to be determined

Example:

= Len ("Excel") - result will be - 5
= Len ("Matic") - result will be - 5
= Len ("ExcelMatic") - result will be - 10
= Len ("Excel Matic") - result will be - 11
= Len ("## @@ $$") - result will be - 8 (The string here contains 6 special characters and 2 spaces)

Function Find()

2009-06-13T01:03:00.007+05:30

Find()

This function searches for a text string within a text string, but this function is case sensitive. This function considers 'Apple' and 'apple' as different text strings. This function returns the position at which the text string is located. And, if the string is not found, it retuns the error (#Value!)

Syntax:

= Find (Search_text, Base_text, Start_pos)

Search_text = Text string which is to be found in the Base Text

Base_text = Text string within which the Search Text is to be found

Start_pos = position in the Base Text from which the function should start looking for the Search Text. This is input is optional

Examples:

= Find ("e", "Excel Matic") - result will be - 4
= Find ("E", "Excel Matic") - result will be - 1
= Find ("c", "Excel Matic") - result will be - 3
= Find ("c", "Excel Matic",5) - result will be - 11
= Find ("S", "Excel Matic") - result will be - #Value!

Function Exact()

2009-06-13T00:51:00.009+05:30

Exact()

This function checks whether the 2 text strings are exactly same or not. It returns TRUE or FALSE as per the case. This function is case-sensitive and treats 'A' different from 'a'

Syntax:

= Exact (Text1, Text2)

Text1, Text2 = are 2 text strings to be compared

Example:

Following Examples will make the usage of Exact() function clear

= Exact ("Excel, "Matic") - result will be - FALSE
= Exact ("Excel, "excel") - result will be - FALSE
= Exact ("matic, "Matic") - result will be - FALSE
= Exact ("Excel, "Excel") - result will be - TRUE
= Exact ("Matic, "Matic") - result will be - TRUE

Function Concatenate()

2009-06-13T00:42:00.004+05:30

Concatenate()

This function concatenate (joins) two or more text strings, including numbers and other symbols in text format

Syntax:

= Concatenate (Text1, Text2, Text...)

Text1, Text2, Text.. = are the text strings which are required to be contactenated

Example:

= Concatenate ("Excel", "Matic") - result will be -ExcelMatic
= Concatenate ("Excel", " ", "Matic") - result will be -Excel Matic (note the space between words)
= Concatenate ("Excel", " ", "Matic", " 5.2") - result will be -Excel Matic 5.2

Note: The results of Concatenate() function can also be achieved by using '&' symbol. It also performs the function of joining the text strings and numbers. Similar to Concatenate, '&' also converts the numbers into text format

Example:

= "Excel" & "Matic" - result will be -ExcelMatic
= "Excel" & " " & "Matic" - result will be -Excel Matic (note the space between words)
= "Excel" & " " & "Matic" & " 5.2" - result will be -Excel Matic 5.2

Function Fact()

2009-06-13T00:37:00.005+05:30

Fact()

This function returns the factorial of a number

Syntax:

= Fact (Number)

Number = The number for which the factorial is required

Example:

= Fact (0) - result will be - 1
= Fact (1) - result will be - 1
= Fact (2) - result will be - 2
= Fact (3) - result will be - 6
= Fact (4) - result will be - 24
= Fact (5) - result will be - 120

Function Int()

2009-06-13T00:34:00.004+05:30

Int()

This function rounds off the numnber to nearest integer less than the original number

Syntax:

= Int (Number)

Number = The number which is to be rounded off to nearest integer

Example:

= Int (5.5) - result will be - 5.0
= Int (5.0) - result will be - 5.0
= Int (5.1) - result will be - 5.0
= Int (5.9) - result will be - 5.0

Function Exp()

2009-06-13T00:28:00.006+05:30

Exp()

This function returns the result of 'e' raised to the power a given number

Syntax:

= Exp (Number)

Number = is the number which is used as x in the equation e^x

Example:

= Exp (0) - result will be: 1.0000
= Exp (1) - result will be: 2.7183
= Exp (2) - result will be: 7.3891
= Exp (3) - result will be: 20.0855

Function Even()

2009-06-13T00:20:00.005+05:30

Even()

This function returns the nearest EVEN number which is - greater than the original number if the original number is positive and lesser than the original number if original number is negative

Syntax:

= Even (Number)

Number = is the number which is required to be rounded off to nearest even number

Example:

= Even (2) - result will be: 2
= Even (3) - result will be: 4
= Even (5) - result will be: 6
= Even (8) - result will be: 8
= Even (-2) - result will be: -2
= Even (-3) - result will be: -4
= Even (-5) - result will be: -6
= Even (-8) - result will be: -8

Function ABS()

2009-06-13T00:05:00.005+05:30

ABS()

This function returns the absolute value of the number

Syntax:

= ABS (Number)

Number = The number for which the absolute value is required

Example:

= ABS (20) - result will be - 20
= ABS (-20) - result will be - 20

Function Rept()

2009-06-08T14:06:00.011+05:30

Rept()

This function fills a cell with the given string repeated 'n' number of times

Syntax:

= Rept (Text , Number of Times)

Text = is the text or characters to be repeated

Number of Times = is the number of times the given text is to be repeated

Example:

= Rept ("M", 7) - result will be - MMMMMMM

= Rept ("E M", 3) - result will be - E ME ME M

Note: note the highglighted characters in the above examples. The number of characters to be removed and the number of new characters to be inserted may not be same. You can insert as many characters as you want in place of any number of characters from the string

Excel Matic Tip:

The Rept() function repeates a given character 'n' number of times and this number provided in the function can be used dynamically for other purposes, such as:

Make Graphic presentation of numeric data without using graphs:

In cell A1 to cell A10 write any numbers (try not to go beyond 50)
Now in cell B1 type this function:

= Rept("l", a1)
The character I have used is present on your keyboards above the Return key. You would need to press SHIFT and then the key above return key
Now copy this function till range B10

The output you will get will be something like this:

The image above uses Rept() to create a bar graph with "l" symbol which represents the frequency given in column A

Function Replace()

2009-06-08T13:52:00.007+05:30

Replace()

This function is used to replace a part of string, from within a Text string, with a new Text string

Syntax:

= Replace (Old Text, Start Number, Number of Characters, New Text)

Old Text = is the original text string

Start Number = is the position within the text from where the old text is to be replaced

Number of Characters = is the number of characters in the old string which are to be removed and replaced

New Text = is the new text string that will be placed at the position referred by Start Number

Example:

= Replace ("Mohit Khurana", 1, 1, "R") - result will be - Rohit Khurana
= Replace ("Rajeev", 5, 2, "sh") - result will be - Rajesh
= Replace ("Alpha", 1, 5, "Beta") - result will be - Beta
= Replace ("Alpha Beta Gamma", 10, 1, "ter") - result will be - Alpha Better Gamma