How to use COLUMN and COLUMNS functions.

Lets look at the two functions which sound similar but give different results. COLUMN gives the position of the column and COLUMNS counts the number of columns in a range.

Let's see the syntax.

COLUMN([reference])

COLUMNS(array)

Examples
COLUMN(F2) = 6 
COLUMN() = will give the column position of the cell where the function is.

COLUMNS(F10:I14) = 4






One use of the COLUMN function is with VLOOKUP. 
Consider the example below, A table of outputs of different machines in different days.

Now I want to pull only machine 3 data to another place using VLOOKUP. First I key in Machine 3 in A6 for this example.
I need the values in column I to L to appear in column B to E.

I can key in =VLOOKUP($A6,$H:$L,2,FALSE) in B6, =VLOOKUP($A6,$H:$L,3,FALSE) in C5 and so on. 
But I need to keep changing the col_index_num  for every column. If you are working with a large table it can be tedious work.

Here we can use column function to make it automatic when we drag the function. The formula looks like this,

in B6 =VLOOKUP($A6,$H:$L,COLUMN(I:I)-7,FALSE) 

COLUMN(I:I)-7 = 2

When you drag the function,
in C6 =VLOOKUP($A6,$H:$L,COLUMN(J:J)-7,FALSE)
in D6 =VLOOKUP($A6,$H:$L,COLUMN(K:K)-7,FALSE)
in E6 =VLOOKUP($A6,$H:$L,COLUMN(L:L)-7,FALSE)

You can use COLUMNS(A:G) instead of 7
=VLOOKUP($A6,$H:$L,COLUMN(I:I)-COLUMNS(A:G),FALSE)

Depending on the table location and where you want to pull the data the function for col_index_num will change. 


Please let me know comments/feedback.

Excel Tips - How to use CHOOSE function

Another simple function which seems nothing much of a use at the first look. But it can be used choose from a predetermined list.

Syntax

CHOOSE(index_num, value1, [value2], ...)

index_num and value1 and necessary. value2 to value255 (excel 2010) is optional. If index_num is 1 then value1 is the result, if index_num is 2 then value2 is the result and so on.

For example, let's say we need to know the "DAY" of a certain date. To do that we need to use another excel function WEEKDAY.

The week is from Sunday to Monday,

DAY	WEEKDAY
Sunday	1
Monday	2
Tuesday	3
Wednesday	4
Thursday	5
Friday	6
Saturday	7

So, CHOOSE(1,"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") will give Sunday. It is possible to have the days in cells. But for this example I will keep within the function.

Now, instead of index_num = 1, we will use =WEEKDAY(4th July 2012). Cell A14 contain the date.

=CHOOSE(WEEKDAY(A14),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Just to illustrate I made a table.

4 July, 1999	Sunday
4 July, 2000	Tuesday
4 July, 2001	Wednesday
4 July, 2002	Thursday
4 July, 2003	Friday
4 July, 2004	Sunday
4 July, 2005	Monday
4 July, 2006	Tuesday
4 July, 2007	Wednesday
4 July, 2008	Friday
4 July, 2009	Saturday
4 July, 2010	Sunday
4 July, 2011	Monday
4 July, 2012	Wednesday

CHOOSE can be used in conjunction with other excel commands like SUM.

As always, love to have your comments.

How to use AREAS function

Let's look at the syntax.

AREAS(reference)

Well, not a complicated function. An area is a range of contiguous cells or a single cell. For example AREAS(B1) = 1 and AREAS(B1:B10) =1. Please note whether the cell contains a value or not will not change the outcome of the results. 

If we use multiple reference then the result will count of the references, for example AREAS((B2,B3,B11)) = 3.

There is another use of this function, which actually uses the " contiguous" part.

There are two lists, list1 and list2 in the worksheet. In this case AREAS will give the number of overlapping cells.

AREAS(list1 list2) = 6

As always love to have your comments.

Using ADDRESS function

Lets look at the syntax first.

Syntax

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

1. row_num can be specified by numerical or by row() function. 

2. column_num can be specified by numerical or  by column() function.

3. abs_num has 4 choices,

abs_num	Returns this type of reference
1 or omitted	Absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.)
2	Absolute row; relative column
3	Relative row; absolute column
4	Relative

4. a1 has two options, TRUE, 1 or omitted and FALSE or 0. TRUE, 1 or omitted will give A1 style and FALSE will give R1C1 style. 

5. sheet_text specifies the sheet name in which the cell is located. Please note sheet names are case sensitive.

Lets look at the different possibilities.

Formula	Results
=ADDRESS(1,1)	$A$1
=ADDRESS(1,1,1)	$A$1
=ADDRESS(1,1,1,1)	$A$1
=ADDRESS(1,1,1,0)	R1C1
=ADDRESS(1,1,1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(ROW(A1),COLUMN(A1),1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(1,1,1,1,"Sheet1")	Sheet1!$A$1
=ADDRESS(1,1,2,1,"Sheet1")	Sheet1!A$1
=ADDRESS(1,1,3,1,"Sheet1")	Sheet1!$A1
=ADDRESS(1,1,4,1,"Sheet1")	Sheet1!A1

Simply put this function will give the cell number. So whats the big deal about a cell number. Well it can be used in labeling.

Example

Look at the example below,

It works fine till a row or column added or removed. 

Now the label become meaningless as name should be entered in E3. 

The solution is to use ADDRESS function.

="Please enter the name in cell "&ADDRESS(ROW(E2),COLUMN(E2),4,)

It works fine even a row or column added or removed. By this way the label auto adjusts.

Add a row...

Add a column...

The label adjusts automatically.

In the same way a range can be specified in a label. Using formula as shown below,

="Please enter the name in cell "&ADDRESS(ROW(F3),COLUMN(F3),4,)&":"&ADDRESS(ROW(F20),COLUMN(F20),4,) 

Love to have your comments.

Using ABS function

ABS will give absolute value of the number or formula.

Let say there are three values -5,-10 and - 8 in A1, B1 and C1 respectively. 

=ABS(A1) will give 5
=ABS(A1*B1*C1) will give 400

Using SUMPRODUCT to count

Let's look at the example below,

In column C dates and column D the error happened on the day with possible multiple entries.

The requirement is to calculate the number of times error5 occurred after 5/5/2012 and before 5/15/2012. (answer is 5).

Lets put the two dates in A1 and A2. The formula will be 

=SUMPRODUCT((C1:C22>A1)*(C1:C22<A2)*(D1:D22="error5"))

If you do not want enter dates in cells the formula will be 

=SUMPRODUCT(($C$1:$C$22>DATE(2012,5,5))*($C$1:$C$22<DATE(2012,5,15))*($D$1:$D$22="error5"))

Love to have your comments....