CHOOSE() function

This Workbook demonstrates how to use the CHOOSE Function in Excel.

The Choose function returns a value from a list of values based on a given position. The syntax for the Choose function is:
Choose( position, value1, value2, ... value29 )
position is position number in the list of values to return. It must be a number between 1 and 29.
value1, value2, ... value 29 is a list of up to 29 values. A value can be any one of the following: a number, a cell reference, a defined name, a formula/function, or a text value.

Points to Note:

If position is less than 1 or If position is greater than the number of the total number of values in the list, then the  Choose function will return #VALUE! error.

If position is a fraction (not an integer value), it will be converted to an integer by dropping the fractional component of the number.

A example of using Choose would be

=Choose(1,"Cars","Boats","Planes") - would return Cars

=Choose(2,"Cars","Boats","Planes") - would return Boats

=Choose(3,"Cars","Boats","Planes") - would return Planes

The choice can be extended to 29 options.

The Choose function can also be used in VBA code. For example:

Option Explicit

Sub x()
 Dim sMsg As String
 Dim i As Integer
     sMsg = "Enter your selection" & vbNewLine & vbNewLine
     sMsg = sMsg & "1 for Jack, 2 for Roy, 3 for Andy & 4 for Dave"
    i = Application.InputBox(sMsg)
                Select Case i
                  Case 1, 2, 3, 4
                          sChoice = Choose(i, "Jack", "Roy", "Andy", "Dave")
                         MsgBox sChoice
                 Case Else
                        Exit Sub
              End Select
End Sub
 

The example Workbook demonstrates how to use a value in another cell to determine the choice, note the use of Data Validation to restrict the user's choice. There is also a couple of examples of the use of Choose in VBA, the one listed above and another to open a Worksheet. There is also an example of Error Handling
 

Click here to download the example

VLOOKUP() function

In Microsoft Excel, the VLookup function searches for a value in the left-most column of a table of data and returns the value in the same row based on the specified column within that table..

The syntax for the VLookup function is:

VLookup( value, table_array, index_number, not_exact_match )

value is the value to search for in the first column of the table_array.

table_array is two or more columns of data that is sorted in ascending order.

index_number is the column number in table_array from which the matching value must be returned. The first column is 1.

not_exact_match determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLookup function will look for the next largest value that is less than value.

Tip: as with many formula in Microsoft Excel it is easier to create a Named Range ( highlight your list range and give it a name with Insert Menu - Names - Define) for the table and refer to that in the Formula.

Points to note:

If index_number is less than 1, the VLookup function will return #VALUE!.

If index_number is greater than the number of columns in table_array, the VLookup function will return #REF!.

If you enter FALSE for the not_exact_match parameter and no exact match is found, then the VLookup function will return #N/A.

The example shows how to use the VLOOKUP() function to populate a form with information from a database, which can be on a different Worksheet within the same Workbook. 

Click here to download the example

Concatenation function     

There are two ways to concatenate (combine) the contents of two or more calls in Microsoft Excel. The use of & the CONCATENATE Function

The syntax for the Concatenate function is:

Concatenate( text1, text2, ... text30 )

There can be up to 30 strings that are joined together.

=CONCATENATE("Roy"," ","Cox") would return Roy Cox, note the " " to create the proper layout

Alternatively,

= A1 & B1 & C1

The Workbook example shows different ways to use these functions, note how a space has to be entered in the second cell when joining two cells contents to create a full name.

Click here to download the example

Nesting Functions  

When you are working with EXCEL functions, it is often necessary to put one function inside another one. You are often capable of doing these functions without using the function wizard on the cell bar. However, when the nesting becomes complicated, it is often MUCH easier to use this.

For example, to make a nested IF statement, just click the "=" sign and then choose the IF function. If you are going to have another IF statement for the "true" or "false" condition, simply fill in the other lines of the wizard and then go to the box. Instead of typing, click the "=" sign again and choose the IF function again. You can next a large number of IF statements this way without worrying about all the parentheses being correct.

Nested IF functions are a simple way to do a complex conditional formula. Nested IF functions allow you to use up to seven IF functions to impose conditions.

Essentially, the way a nested if statement would work is like this:

"if the first condition is true", then enter "this", otherwise if the second condition is true", then enter "this" otherwise enter "this"

It looks quite long and off-putting, but its really not too difficult once you understand the concept. A good example would be l a spreadsheet that you use to calculate discounts based on volume purchased. sales force.

From 50 to 99 units earns 10% discount
From 100 to 199 earns 15% discount
Anything over 200 earns 20% discount

Assuming the amount of items purchased is in column A, this is what the nested IF function would look like:

=IF(A2<50,0,IF(A2<=99,10,IF(A2<=199,15,IF(A2>=200,20))))

Perhaps you want to give some discount based on last years Sales. So you need to add a column that displays the Total Purchases for the previous period. Then by using AND, you can check this value. For example, this formula checks whether sales in the previous period exceeded 5000, if so it applies a 5% discount:

=IF((AND(B2>=5000,A2>1)),5,0)

To now to combine the discount criteria to ensure that customers who purchased over 5000 units in the previous period get a minimum discount of 5% the two formulas can be nested.

=IF((AND(B9>=5000,A9<50)),5,IF(A9<50,0,IF(A9<=99,10,IF(A9<=199,15,IF(A9>=200,20)))))

This looks extremely complicated but by comparing the previous formulas you can see that it simply checks the previous period's purchases and if they exceed 5000 apply 5% discount if not 0%. However, after that the previous discount rules apply.

This example demonstrates the use of multiple functions in one formula, i.e. "nesting". In this example we use the IF() function which can be nested up to seven instances. The second example shows how to create a SUM() function dependent on the contents of another cell.

Try amending the values in the relevant cells, note the formulas are in the shaded cells.

Click here to download the example

Subtotal Function  

One of the built-in functions provided with Excel is SUBTOTAL. This function is used automatically by Excel if you use the Subtotals option from the Data menu (Data | Subtotal). If you learn to use the SUBTOTAL function by itself, however, you will find that it is very versatile and flexible. You can be used to obtain more than just a simple subtotal, as the function name implies.

he Subtotal function sections off a part of the worksheet so that calculations can be performed upon it. There are different calculations which you can have performed on the subset of the worksheet, as shown in the Function Numbers table. You must select one of these calculations to be initially performed on the subset. You may, however, change the selection at any time by modifying the existing function if you need to perform other calculations on the data. This article gives you information on: