Archive for May 2009

Home / Archive for May 2009

Function Proper()

Proper() This function is used to convert the text in title case (title case means every word has first letter as capital, e.g. - Excel Mati...

Function Lower()

Dhakkanz 2:08 PM Add Comment

Lower() This function is used to convert the text in lower case (lower case means small letters) Syntax : = Lower (Text) Text = is the text,...

Function Upper()

Dhakkanz 1:59 PM Add Comment

Upper() This function is used to convert the text in UPPER case (upper case means capital letters) Syntax : = Upper (Text) Text = is the tex...

Function T()

Dhakkanz 1:16 PM Add Comment

T() This function is used to check whether a cell contains any text or not, and if it contains text that text should be pulled out to cell i...

Functions Right() and Left()

Dhakkanz 10:16 PM Add Comment

Right() This function is used to extract from a string (a text), an x number of characters starting from the right of the string. Syntax : =...

Functions Count() and Counta()

Dhakkanz 7:19 PM Add Comment

Count() This function is used to count the number of cells which contain numeric values. It does not count the cells that are empty or conta...

List of Excel Functions

Dhakkanz 11:41 AM

Here are the categories under which I have explained a few Excel functions, which according to me are essential for everyone. Here are the c...

Function Ceiling()

Dhakkanz 9:49 PM Add Comment

Ceiling () This functions is used to convert a number with decimal points to an ineger greater than the number. When a number is input in th...

Function Text()

Dhakkanz 9:47 PM Add Comment

Text() This function is used to convert a numeric value into text format, which can be specified by the user in the function itself. The val...

Subscribe to: Posts ( Atom )

Saturday, May 30, 2009

Function Proper()

Proper()

This function is used to convert the text in title case (title case means every word has first letter as capital, e.g. - Excel Matic)

Syntax:

= Proper (Text)

Text = is the text, or reference to a cell that contains the text, that is required to be converted to title case

Example:

Below are some examples to illustrate the usage of Proper() function. For these illustrations let us assume that cell A1 contains text - ExCeL mAtIc

= Proper("hELLO World") - result will be - Hello World
= Proper("HeLlO WoRlD") - result will be - Hello World
= Proper(A1) - result will be - Excel Matic

Note: this function can take text in any case to convert to title case

Function Lower()

Lower()

This function is used to convert the text in lower case (lower case means small letters)

Syntax:

= Lower (Text)

Text = is the text, or reference to a cell that contains the text, that is required to be converted to lower case

Example:

Below are some examples to illustrate the usage of Lower() function. For these illustrations let us assume that cell A1 contains text - EXCEL MATIC

= Lower("HELLO WORLD") - result will be - hello world
= Lower("HeLlO WoRlD") - result will be - hello world
= Lower(A1) - result will be - excel matic

Note: this function can take text in any case to convert to lower case

Function Upper()

Upper()

This function is used to convert the text in UPPER case (upper case means capital letters)

Syntax:

= Upper (Text)

Text = is the text, or reference to a cell that contains the text, that is required to be converted to UPPER case

Example:

Below are some examples to illustrate the usage of Upper() function. For these illustrations let us assume that cell A1 contains text - Excel Matic

= Upper("hello world") - result will be - HELLO WORLD
= Upper("HeLlO WoRlD") - result will be - HELLO WORLD
= Upper(A1) - result will be - EXCEL MATIC

Note: this function can take text in any case to convert to upper case

Function T()

T()

This function is used to check whether a cell contains any text or not, and if it contains text that text should be pulled out to cell in which this function is used.

Though this function is very rarely used, it has good utility in its application

Syntax:

= T (Cell)

Cell = cell that is to be checked for containing text

Example:

Below are some examples to illustrate the usage of Proper() function. For these illustrations let us assume that cell A1 contains text - Excel Matic, cell A2 contains 25.65 and cell A3 contains Force5.

= T(A1) - result will be - Excel Matic
= T(A2) - result will be -
= T(A3) - result will be - Force5
= T("Excel Matic") - result will be - Excel Matic
= T(30.77) - result will be -
= T("30.77") - result will be - 30.77
= T("Force5") - result will be - Force5

Note: In the above example #6, the result will be 30.77 because in the function the input itself is a text. Whenver a number is input in double quotes ( "text" ), the numeric value becomes text.

Wednesday, May 27, 2009

Functions Right() and Left()

Right()

This function is used to extract from a string (a text), an x number of characters starting from the right of the string.

Syntax:

= Right (String, Number of characters)

Example:

For the followig example function statements, let us assume that cell A1 contins the text - Excel Matic.

=Right(A1,1) - Result will be - 'E'
=Right(A1,2) - Result will be - 'Ex'
=Right(A1,5) - Result will be - 'Excel'
=Right(A1,6) - Result will be - 'Excel '
=Right(A1,8) - Result will be - 'Excel Ma'
=Right(A1,11) - Result will be - 'Excel Matic'

Note: In the above results I have used single quote marks ( ' ) to show how many characters have been extracted from the string. If you notice the result 4 has an extra space after l and before the ending single quote. This space has also been extracted from the string.

Left()

This function is used to extract from a string (a text), an x number of characters starting from the left of the string.

Syntax:

= Left (String, Number of characters)

Example:

For the followig example function statements, let us assume that cell A1 contins the text - Excel Matic.

=Left(A1,1) - Result will be - 'c'
=Left(A1,2) - Result will be - 'ic'
=Left(A1,5) - Result will be - 'Matic'
=Left(A1,6) - Result will be - ' Matic'
=Left(A1,8) - Result will be - 'el Matic'
=Left(A1,11) - Result will be - 'Excel Matic'

Note: In the above results I have used single quote marks ( ' ) to show how many characters have been extracted from the string. If you notice the result 4 has an extra space before M and after the starting single quote. This space has also been extracted from the string.

Functions Count() and Counta()

Count()

This function is used to count the number of cells which contain numeric values. It does not count the cells that are empty or contain any alpha-numeric data.

Syntax:

= Count (Cell1, Cell2, Range1,....)

Cell1, Cell2, Range1 = Cells/Ranges for which the count is to be calculated

Example:

For the above image, various usages of Count() function will give the following results:

=Count(B1:B6) - result will be - 5 {cell B1 is not counted}
=Count(B2:B6) - result will be - 5 {all cells in the range are numeric}
=Count(A1:B6) - result will be - 5 {cells with text are ignored}
=Count(A1:B8) - result will be - 6 {blank cells are also ignored}
=Count(B1,B2) - result will be - 1

Counta()

Similar to Count(), the function Counta() is ised to count the number of cells, but with this function you can count the number of cells that contain alpha-numeric data, i.e. - cells that contain numbers and alphabets (any text).

Syntax:

= Count (Cell1, Cell2, Range1,....)

Cell1, Cell2, Range1 = Cells/Ranges for which the count is to be calculated

Example:

For the above image, various usages of Count() function will give the following results:

=Counta(B1:B6) - result will be - 6
=Counta(B2:B6) - result will be - 5
=Counta(A1:B6) - result will be - 12
=Counta(A1:B8) - result will be - 14
=Counta(B1,B2) - result will be - 2

The counta() function also ignores the blank cells from the total count.

List of Excel Functions

Here are the categories under which I have explained a few Excel functions, which according to me are essential for everyone. Here are the categories which I have covered so far:

Cuurently, I have explained functions under the above given heads. But this is not all as I will be regularly updating this list of functions.

In case you want me to cover any function which is not already on the list, feel free to send me a quick note. I will be happy to oblige. Link to my email address is mentioned on the menu bar at the top of this page.

Tuesday, May 26, 2009

Function Ceiling()

Ceiling ()

This functions is used to convert a number with decimal points to an ineger greater than the number. When a number is input in this function, it adds the decimal points enough to convert the number to an integer more than the number itself

Syntax:

= Ceiling (Number, Significance)

Number = the number that is to be rounded off

Significance = multiple to which the number is to be rounded off

Example:

=Ceiling (26.2315 , 1) result will be - 27
=Ceiling (26.2315 , 2) result will be - 28
=Ceiling (26.2315 , 5) result will be - 30
=Ceiling (27.2315 , 1) result will be - 28
=Ceiling (27.2315 , 1) result will be - 28
=Ceiling (27.2315 , 1) result will be - 30

The ceiling function rounds off the number up to the nearest integer which is divisible by the significance value input in the function. Example - if I have to round up 26.33 to an integer, th result will be 27. But, if I want to round up the number, but to that integer which should also be divisible by 2, then my function wil be -

=Ceiling(26.33,)

The result of this function will be - 28

In the examples above I have used 26.2315 and 27.2315 to show the use of 'Significance' value.

Function Text()

Text()

This function is used to convert a numeric value into text format, which can be specified by the user in the function itself. The value once converted into text format, cannot be used for any mathematical calculations.

Syntax:

= Text (Value, Number Format)

Value = the number (or cell containing numeric value) which is to be converted into text

Number Format = specific text format in which the number should be converted

Example:

For the following examples function statements, let us assume that cell 'A1' contains the number - 25.

=Text(A1,"0") - The result will be - 25
=Text(A1,"0.0") - The result will be - 25.0
=Text(A1,"0.00") - The result will be - 25.00
=Text(A1,"0%) - The result will be - 2500% {as each unit of 25 is 100%}
=Text(A1,"0.0%") - The result will be - 2500.0%
=Text(A1,"$0.0") - The result will be - $25.0

Note: 1) You cannot use any text prefix with this number. For example =Text(A1,"Rs.0.00") will result in error; 2) The above results look numeric - percentages, decimals, etc. but have been converted into text and they cannot be used for any calculations.