Home / Archive for June 2009
Subscribe to: Posts ( Atom )

Wednesday, June 24, 2009

Usage of Array Functions

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

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

Here are a few examples:
  1. Calculating average of a series based on multiple conditions, where the conditions are applicable on mupltiple data columns
  2. 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.

Read more...
Usage of Array FunctionsSocialTwist Tell-a-Friend
Posted by Dhakkanz at 12:45 AM , 2 comments
Labels:

A minor mistake in my previous post

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.

Read more...
A minor mistake in my previous postSocialTwist Tell-a-Friend
Posted by Dhakkanz at 12:32 AM , 0 comments

Sunday, June 21, 2009

What are Array Functions or Array Formulas

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

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

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

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

A simple example:

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

Now do this -
  1. In the cells A1 to A10 write the numbers from 1 to 10
  2. 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:
  1. In cell A11 type this: =Sum(A1:A10*B1:B10)
  2. 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:
  1. In cell A11 type this, but do not hit enter): =Sum(A1:A10*B1:B10)
  2. 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

Read more...
What are Array Functions or Array FormulasSocialTwist Tell-a-Friend
Posted by Dhakkanz at 11:15 PM , 2 comments
Labels:

Function SumSQ()

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

Read more...
Function SumSQ()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 9:25 PM , 0 comments
Labels:

Function RandBetween()

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

Read more...
Function RandBetween()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 9:21 PM , 0 comments
Labels:

Function Rand()

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

Read more...
Function Rand()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 2:05 PM , 0 comments
Labels:

Function Odd()

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

Read more...
Function Odd()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 2:04 PM , 0 comments
Labels:

Function Sign()

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

Read more...
Function Sign()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 2:04 PM , 0 comments
Labels:

Function SQRT()

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

Read more...
Function SQRT()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 2:04 PM , 0 comments
Labels:

Function Value()

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

Read more...
Function Value()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 2:04 PM , 0 comments
Labels:

Function Trim()

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.

Read more...
Function Trim()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 2:04 PM , 0 comments
Labels:

Function Substitute()

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'

Read more...
Function Substitute()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 1:29 PM , 0 comments
Labels:

Function Search()

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)

Read more...
Function Search()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 1:12 PM , 0 comments
Labels:

Function Mid()

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.

Read more...
Function Mid()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 12:59 PM , 0 comments
Labels:

Function Len()

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)

Read more...
Function Len()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 12:49 PM , 0 comments
Labels:

Saturday, June 13, 2009

Function Find()

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!

Read more...
Function Find()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 1:03 AM , 0 comments
Labels:

Function Exact()

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

Read more...
Function Exact()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 12:51 AM , 0 comments
Labels:

Function Concatenate()

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

Read more...
Function Concatenate()SocialTwist Tell-a-Friend
Posted by Dhakkanz at 12:42 AM , 0 comments
Labels:
Subscribe to: Posts (Atom)