ADDRESS

Purpose:
ADDRESS function helps in obtaining the address of a cell in a worksheet based on the row and column numbers specified.

Command:
=ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
(Please note: Syntax specified in bracket [ ] are optional requirements)

Here:
row_num        - Refers to row number required in the cell for reference
column_num   - Refers to column number required in the cell for reference
abs_num        - Type of reference to be returned. This is specified in a numerical form as shown below:

• 1 or None - Absolute (both Row & Column)
• 2               - Absolute row, Relative Column
• 3               - Absolute Column, Relative Row
• 4               -  Relative (both Row & Column)

[a1]               - Refers to a logical value that specifies either A1 or R1C1 reference style.

• In A1 style, the column is specified alphabetically, while the row is specified in numbers
• In R1C1 style, both row & column are specified in numbers

[sheet_text]   - This is specified by a text value & specifies the name of the worksheet

Example:

Enter following values in excel:

Command	Value Returned	What does it mean?
=ADDRESS(5,3)	$C$5	Both Row & Column with Absolute Reference
=ADDRESS(5,3,3)	$C5	Absolute Column & Relative Row
=ADDRESS(5,3,3,0)	R[5]C3	Absolute Column & Relative Row in R1C1 reference style
=ADDRESS(5,3,1,0,"[Report]Daily Report")	'[Report]Daily Report'!R5C3	Absolute Reference to Workbook named "Report" & Worksheet named "Daily Report"
=ADDRESS(5,3,1,0,"Weekly Report")	'Weekly Report'!R5C3	Absolute Reference to a Worksheet named "Weekly Report"

Please see Image below:

ABS (Absolute)

Purpose:
Suppose for counting purpose, you want only the absolute value i.e. without any sign ( + / - ), then this is the function that you must be looking at.

Command:
=ABS(number)

Example:
Enter following data into Excel:

A1: -23

A2: 9

A3: -2

Now select cell A4 & add following formula:
=A1+A2+A3
This will return you a value of -16

Now check what happens when you use ABS function:
=ABS(A1)+A2+ABS(A3)
Check the value now, it is 34

I haven't come across any particular application for this function, but never know when this might be required

See below image:

OFFSET

Purpose:
This function is very useful when you want to return the value of a cell in terms of specified number rows and columns from a cell or range of cells that are reference in a range adjacent to it.

Command:
=OFFSET(reference,rows,cols,[height],[width])

Example:
Enter the following values in Excel:

A1: Customer ID	B1: Name	C1: DVD Title Purchased	D1: Cost
A2: CD-123	B2: Jeniffer	C2: Dawn of the Dead	D2: $6.99
A3: CD-456	B3: Tim	C3: Titanic	D3: $18.99
A4: CD-789	B4: Clark	C4: The Mechanic	D4: $14.99

Then select cell A6 & paste the following command:
=OFFSET(C1,3,-2,1,1)

Above function will return value as 'CD-789'.
Here,

C1 - Is the cell referenced (reference,rows,col,[height],[width])

3    - Is the number by which rows move. To move Down, umber has to be Positive & to move Up, number has to be Negative (reference,rows,col,[height],[width])

-1  - It is the number by which columns move. To move right, number has to be Positive & to move left, number has to be negative (reference,rows,col,[height],[width])


Optional Parameters:
1   - Indicates how many rows of data to return. This number must be a positive number (reference,rows,col,[height],[width])

1    - Indicates how many columns of data to return. This number must be a positive number (reference,rows,col,[height],[width])

See image below: