EXCEL - Basic to Advanced

ADDRESS

2012-10-21T12:11:00.001+05:30

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:

ACOSH (Arccosine Hyperbolic inverse)

2012-10-14T20:12:00.004+05:30

Purpose:
Returns the inverse hyperbolic cosine of a number.

Command:
=ACOSH(number)

Example:
Enter value of 2 in cell A1. In cell A2, enter the following formula:
=ACOSH(A1)

This will return the value of 1.32

Please see image below:

ACOS (Arccosine)

2012-10-14T19:49:00.002+05:30

Purpose:
Returns the arccosine of a number, in radians in the range 0 to Pi. The arccosine is the angle whose cosine is number.

Command:
=ACOS(number)

Example:
Enter value of 0.5 in cell A1. In cell A2, enter the following formula:
=ACOS(A1)

This will return the value of 1.05

Please see image below:

ACCRINTM (Accrued Interest at Maturity)

2012-10-14T19:37:00.000+05:30

Purpose:
Calculates the Accrued Interest at maturity.

Command:
=ACCRINTM(issue,settlement,rate,par,[basis])

Here:

issue	- Date of issue issue of the investment
settlement	- Date the investment is traded to the buyer
rate	- Annual rate of interest
par [basis]	- This is the investment value - Provides the method to use, when counting days between dates

Example:
Enter following data in Excel (A1,A2.... refers to cell location):
A1: 1-1-12          (Cell should be formatted to 'Date' in mm-dd-yy format)
A2: 2-15-12        (Cell should be formatted to 'Date' in mm-dd-yy format)
A3: 4.75%           (Cell should be formatted to 'Percentage')
A4: Rs. 5,000      (Cell should be formatted to 'Currency')

When prompted, kindly select the following value:

[basis] - select it as Actual/365 (3)

Final formula will look like this:
=ACCRINTM(F1,F3,F4,F5,3) & will return to value 29.28%

Hint: If ACCRINTM returns the #NAME? error value then, perform following steps:

Install Analysis ToolPak
From Tools menu select Add-Ins
Mark the checkbox next to Analysis ToolPak
Click OK (this will install the Add-In)

ACCRINT (Accrued Interest)

2012-10-14T17:12:00.004+05:30

Purpose:
Calculates the Accrued Interest.

Command:
=ACCRINT(issue,first_interest,settlement,rate,par,frequency,[basis],[calc_method])

Here:

issue first_interest	- Date of issue issue of the investment - Date of payment for first interest
settlement	- Date the investment is traded to the buyer
rate	- Annual rate of interest
par	- This is the investment value
frequency	- Number of times payment is done per year
[basis]	- Provides the method to use, when counting days between dates
[calc_method]	- It's the Calculation method

Example:
Enter following data in Excel (A1,A2.... refers to cell location):
A1: 1-1-12          (Cell should be formatted to 'Date' in mm-dd-yy format)
A2: 4-30-12        (Cell should be formatted to 'Date' in mm-dd-yy format)
A3: 2-15-12        (Cell should be formatted to 'Date' in mm-dd-yy format)
A4: 4.75%           (Cell should be formatted to 'Percentage')
A5: Rs. 5,000       (Cell should be formatted to 'Currency')

When prompted, kindly select the following value:
frequency - select it as quarterly (4)
[basis] - select it as Actual/365 (3)

Enter following formula as shown below:
=ACCRINT(A1,A2,A3,A4,A5,4,3) & will return to value 29.28%

Hint: If ACCRINT returns the #NAME? error value then, perform following steps:

Install Analysis ToolPak
From Tools menu select Add-Ins
Mark the checkbox next to Analysis ToolPak
Click OK (this will install the Add-In)

Please see below image:

ABS (Absolute)

2012-10-14T16:06:00.000+05:30

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

2012-10-14T13:22:00.001+05:30

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: