OFFSET, COUNT, CHOOSE & INDIRECT

In this article, we are going to quickly revise some of the Excel functions we have used and will use to create interactive and dynamic charts. Charting in Excel is not only about creating stunning visualizations but having advanced formulas to build the model. It is important, if you are not already familiar with these functions, that you make yourself familiar with them.

OFFSET

We can use OFFSET to refer to the location of specific data in an Excel worksheet. When used in this way It returns the value of a cell that is a specified number of rows and columns away from the active cell. By using the optional fields, we can also use the OFFSET function to return an array or series of cells.

OFFSET is a reference function. The Syntax is:

=OFFSET (Reference, Rows, Columns, [height], [width])

To return a value that is a specified number of rows away, the OFFSET function first looks for a Reference. This is the starting point. Then you specify how many rows and columns away you want to return the value from.

To return a range, the OFFSET function first looks for a Reference or a starting point. In this case, we do not need to move any rows or columns. Instead, we want to have a height of 3 rows and a width of 2 columns.

The COUNT Functions

The COUNT functions can be used with the OFFSET function to make the OFFSET function Dynamic. Depending on the nature of the data you are working with, you can select between the following COUNT functions

COUNT – Will count cells from a selected range that contains a number. COUNTA – Will count cells from a selected range that contains a number. COUNTIF – Will count cells from a selected range that meets the criteria. COUNTIFS – Will count cells from a selected range that meet one or more criteria.

Suppose you wanted to return a range containing all the cells in column A with a value. However, the number of rows with dates might change. This can be achieved using a combination of the OFFSET function with COUNT as shown.

Where reference A1 is the starting point. From here we are not going to move any rows or columns. Instead, we are going to grab the height of the column using the COUNTA function.

The CHOOSE function

The CHOOSE function will allow us to return a value from a list of values based on a selected index number.

The syntax for the CHOOSE function in Excel is:

=CHOOSE (index_number, value1, [value2],…)

Where Index_number will be decided which value argument is selected. This can be a number (between 1 and 254), a cell reference, or even a formula.

Value1, [value2}, are the values to be returned based on the index number selected.

CHOOSE will allow us to return a value based on an index number. Let us say we had a requirement to return the day based on user input. By giving each day its own number, we can use the CHOOSE function. For example, Monday will be index_num 1, Tuesday will be index_num 2

=CHOOSE (1, “Monday”,” Tuesday”,” Wednesday”, “Thursday”,” Friday”,” Saturday”,” Sunday”)

Where 1 is the selected index_num therefore this formula will return Monday.

The formula can also be written by referencing the Values to cells. For Example

=CHOOSE (2, A2,A3,A4,A5,A6,A7,A8)

Will return Tuesday as we have selected Index_num 2 and this has been defined as the contents of cell A3.

INDIRECT

Excel’s INDIRECT function will allow you to take a text value and convert it into an Excel Reference. This reference can be a cell reference, a sheet reference, or a range. Let us say you have the number 25 in cell B2 and in cell B4 you have the text B2. In any other cell, if we enter the formula =Indirect(B4), we will get 25.

So, what has happened is that the Indirect function has taken the text in cell B4 and converted that to a cell reference, B2. B2 contains the value 25, so the INDIRECT function returns 25.