Posts Tagged Worksheet function

Use array in a costomized worksheet function

We know there are a lot of worksheet functions that use an array as an argument. How can we use an array as an argument in our costomized worksheet functions? I’d like to address this question step by step.

  1. The function declaration. In the function declaration part, you should declare an array parameter as an object. For example,
    Function Test(Range1 as Object)
  2. To access the elements in an input array. You can use traditional For-Next loop to access the elements in an array as we do for arrays declared in the function by DIM command.
  3. To get the dimension size of an input array. Use UBOUND function to achieve the purpose. For example,
    nSize = UBound(Range1(), 1)

    This command will get the size of the first dimension of the input array. It is worthy to mention that the input array in the UBound function must be written like array name followed a pair of parentheses.

To see an example of using an input array, please refer to an post called “Enhanced Chi-Square Test Function in Excel”.

Share

Tags: , , , , , ,

Dynamic graphing in Excel

Problem:

Data are in two columns: A and B.  I am plotting a graph which takes data from columns  A and B as its source data. I want to generate an Excel template that can use many times. That is, I can copy data to the data sheet and view graph directly. The number of rows in both the columns changes with time. For example, one data set only has 50 rows and another one has 150 rows. If I create a graph by directly use the data in columns A and B, I have to change the number of rows to exclude blank cells while plotting a graph.

Solution:

The key of the solution depends on Excel worksheet function – OFFSET and COUNTA. OFFSET function takes a cell as reference  and number of columns and rows away from the reference cell and the final number of rows and columns of returned range. The result of the OFFSET function is a reference to a range that is specified number of rows and columns from a cell or range of cells. COUNTA counts the number of cells that are not empty and the values within the list of arguments. By combining these two worksheet functions, we can defined a named variable in Excel, which is dynamic in size. Here is a step by step instruction.
a) Create a chart as you would like to see it based on the values in column A and B. Let’s say it has 100 rows of data, with headers in A1 and B1.
b) Click Insert -> Name -> Define…
c) Type a new name, such as CatVar and for the formula, use =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
d) Click Insert -> Name -> Define… again
e) Type a new name, such as ValueVar and for the formula, use =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
f) On your chart, click on one of the data points in curve or bar. The formula for the series should show up in the Formula bar, similar to: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$100,Sheet1!$B$2:$B$100,1)
g) Change that formula in the Formula bar to: =SERIES(Sheet1!$B$1,Book1!CatVar,Book1!ValueVar,1)

Note: If your defined names are at the sheet level, not the workbook level, change ‘Book1’ to ‘Sheet1’ or your sheet name. Once you press Enter you should be all set. Remove data rows, add them.. your chart should update automatically and dynamically. Enjoying the dynamic graphing in Excel.

Download a demo spreadsheet – dynamic graph.

Share

Tags: , , ,

A smart way to use Excel INDEX and MATCH functions

Function review:

INDEX is used to find value in an array in a given location (column and row).

MATCH is used to find the position in an array by searching a given value in
the array.

We can use these two functions simultaneously to look up value in a two way table.

Suppose the table is in range A1:B10, and the value to be looked up is in A15,
use can use the following formula to get value if Column B corresponding value
in A15 (in Column A).

= INDEX($B$1:$B$10,MATCH(A15,$A$1:$A$10,0))

See a simple sample in the sampel spreadsheet.

Share

Tags: , ,