MICROSOFT EXCEL TUTORIAL - STATISTICAL FUNCTIONS

 STATISTICAL FUNCTIONS

1. AVEDEV:- In Microsoft Excel “AVEDEV” function returns the average of absolute value of deviations from the mean for a given set of data.

Syntax of AVEDEV function

=AVEDEV(number1,[number2],….)

Arguments:-

Number1:- The first value or reference.

Number2:- The second value or reference [optional part].

Example of AVEDEV function:-

The following picture shows the example of the AVEDEV function.

2. AVERAGE:- In Microsoft Excel “AVERAGE” function calculates the average of given group of numbers. Average can handle up to 255 arguments. It can be numbers, range, arrays, cell references and constants.

Syntax of AVERAGE function

=AVERAGE(number1,[number2],……)

Arguments:-

Number1:- A number or cell reference (numeric values).

Number2:- A number or cell reference (numeric values) and it is an optional part.

Example of AVERAGE function:-

The following picture shows the example of the AVERAGE function.

3. AVERAGEA:- In Microsoft Excel “AVERAGEA” function returns  the average of its arguments, evaluating text and FALSE in arguments as 0; TRUE evaluates as 1. The arguments can be numbers, names, arrays or references.

Syntax of AVERAGEA function

=AVERAGEA(value1,[value2],…..)

Arguments:-

Value1:- A value or cell reference (evaluated as number).

Value2:- A value or cell reference (evaluated as number) and it is an optional part.

Example of AVERAGEA function:-

The following picture shows the example of the AVERAGEA function.

4. AVERAGEIF:- In Microsoft Excel “AVERAGEIF” function finds average for the cells specified by a given condition or criteria. The criteria used for AVERAGEIF can include logical operators (>, <, <>, =).

Syntax of AVERAGEIF function

=AVERAGEIF(range,criteria,[average_range])

Arguments:-

Range:- The array of values to be tested.

Criteria:- The condition to be tested in the supplied range against each of the values.

Average_range:- The actual set of cells to average and it is an optional part if omitted range is used.

Example of AVERAGEIF function:-

The following picture shows the example of the AVERAGEIF function.

Read More

MICROSOFT EXCEL TUTORIAL - INFORMATION FUNCTIONS

 INFORMATION FUNCTIONS

MICROSOFT EXCEL TUTORIAL-INFORMATION FUNCTIONS

CELL	ISNONTEXT
ERROR.TYPE	ISNUMBER
INFO	ISODD
ISBLANK	ISREF
ISERR	ISTEXT
ISERROR	N
ISEVEN	NA
ISLOGICAL	TYPE
ISNA

1. CELL:- In Microsoft Excel “CELL” function returns the information about a given cell. This gives information related to the contents, formatting or location of the cell.

Syntax of CELL function:-

=CELL(info_type,[reference])

Arguments:-

Info_type:- The type of information to return about the cell.

Reference:- The reference from which to get information and it is an optional part.

Example of CELL function:-

The following pictures show the example of the CELL function.

The following info_type can be classified with the cell function,

Info_type	Description
address	returns the address of the first cell in the reference.
col	returns the column number of the first cell.
color	returns the value of the first cell (1 = formatted color and 0 = not formatted color)
contents	returns the value of the cell (formulas aren’t returned)
file name	returns the file name and full path as text.
format	returns a code that corresponds to the number format of the cell. (See the list below)
parentheses	1 = formatted with parentheses and 0 = not formatted
prefix	returns a text value that corresponds to the level prefix of the cell (see the below list).
protect	returns 1 = cell reference is locked and 0 = not locked.
Row	returns the row number of the first cell
Type	returns a text value that corresponds to the type of data in the cell (“b” = when cell is blank, “i” = contains text, “v” = cell contains anything else).
Width	returns the column width of the cell

 

Prefix	Description
‘	The cell text is left alignment.
“	The cell text is right alignment.
^	If the cell text is centered.
()	If the cell text is fill alignment.

 

FORMAT CODE RETURNED	FORMAT CODE MEANING	FORMAT CODE RETURNED	FORMAT CODE MEANING
G	General	P2	0.00%
F0	Zero	S2	0.00E+00
,0	#,##0	D1	d-mmm-yy or dd-mmm-yy
F2	0	D2	d-mmm or dd-mmm
,2	#,##0.00	D3	mmm-yy
C0	$#,##0_);($#,##0)	D4	m/d/yy or m/d/yy h:mm or mm/dd/yy
C0-	$#,##0_);[red]($#,##0)	D5	mm/dd
C2	$#,##0.00_);($#,##0.00)	D6	h:mm:ss AM/PM
C2-	$#,##0.00_);[red]($#,##0.00)	D7	h:mm AM/PM
P0	0%	D8	h:mm:ss

 

2. ERROR.TYPE:- In Microsoft Excel “ERROR.TYPE” function returns an integer value by receiving an error value.

Syntax of ERROR.TYPE function:-

=ERROR.TYPE(error_val)

Argument:-

Error_val:- The error value which to get an error code as integer.

Example of ERROR.TYPE function:-

The following pictures show the example of the ERROR.TYPE function.

3. INFO:- In Microsoft Excel “INFO” function returns information about the current environment like directory, OS version, system, etc.

Syntax of INFO function:-

=INFO(type_text)

Argument:-

Type_text:- The information type to return as text. See the type text table below.

Type_text	Description
num file	Number of active worksheet in active workbooks.
Release	Excel version
System	Operating system name
Osversion	Operating system version
Origin	First visible cell at top left corner of sheet (e.g. A:A1)
Recalc	Recalculation mode
Directory	Current directory or folder path

 

Example of INFO function:-

The following pictures show the example of the INFO function.

4. ISBLANK:- When a cell contains is blank then in Microsoft Excel “ISBLANK” function returns TRUE otherwise FALSE.

Syntax of ISBLANK function:-

=ISBLANK(value)

Argument:-

value:- The value to verify.

Example of ISBLANK function:-

The following pictures show the example of the ISBLANK function.

5. ISERR:- In Microsoft Excel “ISERR” function returns TRUE for any error type except the #N/A error.

Syntax of ISERR function:-

=ISERR(value)

Argument:-

value:- The value to verify any error except #N/A.

Example of ISERR function:-

The following pictures show the example of the ISERR function.

6. ISERROR:- In Microsoft Excel “ISERROR” function returns TRUE for any excel generated error including #N/A .

Syntax of ISERROR function:-

=ISERROR(value)

Argument:-

value:- The value to verify any error.

Example of ISERROR function:-

The following pictures show the example of the ISERROR function.

7. ISEVEN:- In Microsoft Excel “ISEVEN” function returns TRUE if the cell contains even number otherwise returns FALSE for odd number.

Syntax of ISEVEN function:-

=ISEVEN(number)

Argument:-

number:- The number to be tested.

Example of ISEVEN function:-

The following pictures show the example of the ISEVEN function.

8. ISLOGICAL:- In Microsoft Excel “ISLOGICAL” function returns TRUE if the given cell contains logical value (TRUE or FALSE) otherwise returns TRUE for any value.

Syntax of ISLOGICAL function:-

=ISLOGICAL(value)

Argument:-

value:- The value to be tested.

Example of ISLOGICAL function:-

The following pictures show the example of the ISLOGICAL function.

9. ISNA:- In Microsoft Excel “ISNA” function returns TRUE for #N/A error otherwise gives FALSE.

Syntax of ISNA function:-

=ISNA(value)

Argument:-

value:- The value to be tested.

Example of ISNA function:-

The following pictures show the example of the ISNA function.

10. ISNONTEXT:- In Microsoft Excel “ISNONTEXT” function returns TRUE for non-text value otherwise gives FALSE.

Syntax of ISNONTEXT function:-

=ISNONTEXT(value)

Argument:-

value:- The value to be tested.

Example of ISNONTEXT function:-

The following pictures show the example of the ISNONTEXT function.

11. ISNUMBER:- In Microsoft Excel “ISNUMBER” function returns TRUE if the supplied value is a number otherwise FALSE.

Syntax of ISNUMBER function:-

=ISNUMBER(value)

Argument:-

Value:- The value to be tested.

Example of ISNUMBER function:-

The following picture shows the example of the ISNUMBER function.

12. ISODD:- In Microsoft Excel “ISODD” function returns TRUE if the number is odd otherwise FALSE.

Syntax of ISODD function:-

=ISODD(number)

Argument:-

number:- The number to be tested.

Example of ISODD function:-

The following picture shows the example of the ISODD function.

13. ISREF:- When the cell contains valid reference then Microsoft Excel “ISREF” function returns TRUE otherwise FALSE.

Syntax of ISREF function:-

=ISREF(value)

Argument:-

Value:- The value to be tested.

Example of ISREF function:-

The following picture shows the example of the ISREF function.

14. ISTEXT:- When the supplied cell contains text then Microsoft Excel “ISTEXT” function returns TRUE otherwise returns FALSE.

Syntax of ISTEXT function:-

=ISTEXT(value)

Argument:-

Value:- The value to be tested.

Example of ISTEXT function:-

The following picture shows the example of the ISTEXT function.

15. N:- In Microsoft Excel “N” function transforms a non number value to number, dates to serial numbers, TRUE to 1, anything else to “zero(0)”.

Syntax of N function:-

=N(value)

Argument:-

Value:- The value to convert to number.

Example of N function:-

The following picture shows the example of the N function.

16. NA:- In Microsoft Excel “NA” function returns the error value #N/A (means value not available).

Syntax of NA function:-

=NA()

Example of NA function:-

The following picture shows the example of the NA function.

17. TYPE:- In Microsoft Excel “TYPE” function returns an integer representing the data type of a value (1 = number, 2 = text, 4 = logical value, 16 = error value, 64 = array).

Syntax of TYPE function:-

=TYPE(value)

Argument:-

Value:- The value to know its type.

Example of TYPE function:-

The following picture shows the example of the TYPE function.

Read More

MICROSOFT EXCEL TUTORIAL - FINANCIAL FUNCTIONS (PART - 3)

 FINANCIAL FUNCTIONS

PART 1:- FINANCIAL FUNCTIONS I

PART 2:- FINANCIAL FUNCTIONS II

PART 3:- FINANCIAL FUNCTIONS III

FINANCIAL FUNCTIONS III

MICROSOFT EXCEL TUTORIAL-FINANCIAL FUNCTIONS

Categories of FINANCIAL functions.

RATE	VDB
RECEIVED	XIRR
SLN	XNPV
SYD	YIELD
TBILLEQ	YIELDDISC
TBILLPRICE	YIELDMAT
TBILLYIELD

41. RATE:- In Microsoft Excel “RATE” function returns the interest rate per period of a loan or an investment.

Syntax of RATE function

=RATE(nper,pmt,pv,[fv],[type],[guess])

Arguments:-

nper:- The total number of payment periods.

pmt:- The payment made each period.

pv:- The present value.

fv:- The cash balance is required after the last payment is made. It is an optional part and default is 0.

type:- When payments are due (0=end of period and 1= beginning of period). It is an optional part and default is 0.

guess:- Your guess on the rate. It is an optional part and default is 10%.

Example of RATE function:-

The following pictures show the example of the RATE function.

42. RECEIVED:- In Microsoft Excel “RECEIVED” function returns the amount received at the time of maturity.

Syntax of RECEIVED function

=RECEIVED(settlement,maturity,investment,discount,[basis])

Arguments:-

settlement:- The security’s settlement date.

maturity:- The security’s maturity date.

investment:- The amount investment as security.

discount:- The security’s discount rate.

basis:- It specifies the day count basis to be used in the calculation. It is an optional part and if omitted the default is 0.

BASIS	Day Count Basis
0 (omitted)	US (NASD) 30/360
1	actual/actual
2	actual/360
3	actual/365
4	EUROPEAN 30/360

 

Example of RECEIVED function:-

The following pictures show the example of the RECEIVED function.

43. SLN:- In Microsoft Excel “SLN” function returns the depreciation of an asset for one period (calculated in straight line method).

Syntax of SLN function

= SLN(cost,salvage,life)

Arguments:-

cost:- Cost of the asset.

salvage:- The value at the end of the depreciation.

life:- Periods over which asset is depreciated.

Example of SLN function:-

The following pictures show the example of the SLN function.

44. SYD:- In Microsoft Excel “SYD” function returns the “some-of-years” depreciation for an asset for a given period.

Syntax of SYD function

=SYD(cost,salvage,life,per)

Arguments:-

cost:- Cost of the asset.

salvage:- The value at the end of the depreciation.

life:- Periods over which asset is depreciated.

per:- The period to calculate depreciation.

Example of SYD function:-

The following pictures show the example of the SYD function.

45. TBILLEQ:- In Microsoft Excel “TBILLEQ” function returns the bond equivalent yield for a treasury bill.

Syntax of TBILLEQ function

=TBILLEQ(settlement,maturity,discount)

Arguments:-

settlement:- The security’s settlement date.

maturity:- The security’s maturity date.

discount:- The security’s discount rate.

Example of TBILLEQ function:-

The following pictures show the example of the TBILLEQ function.

46. TBILLPRICE:- In Microsoft Excel “TBILLPRICE” function returns the price per $100 face value of a Treasury Bill.

Syntax of TBILLPRICE function

=TBILLPRICE(settlement,maturity,discount)

Arguments:-

settlement:- The settlement date of the Treasury Bill (the date that the bill is purchased).

maturity:- The maturity date of the Treasury Bill (the date that the bill period is end).

discount:- The discount rate of Treasury Bill.

Example of TBILLPRICE function:-

The following pictures show the example of the TBILLPRICE function.

47. TBILLYIELD:- In Microsoft Excel “TBILLYIELD” function returns the yield for a Treasury Bill

Syntax of TBILLYIELD function

=TBILLYIELD(settlement,maturity,pr)

Arguments:-

settlement:- The settlement date of the Treasury Bill (the date that the bill is purchased).

maturity:- The maturity date of the Treasury Bill (the date that the bill period is end).

pr:- Price per $100.

Example of TBILLYIELD function:-

The following pictures show the example of the TBILLYIELD function.

48. VDB:- In Microsoft Excel “VDB” function returns the depreciation of an asset for a given period using the double – declining balance method. VDB stands for Variable Declining Balance.

Syntax of VDB function

=VDB(cost,salvage,life,start_period,end_period,[factor],[no_switch])

Arguments:-

cost:- Cost of asset.

salvage:- The end of the assets depreciation value.

life:- Asset depreciated periods.

start_period:- The starting period.

end_period:- The end period.

factor:- A rate at which the balance declines. It is an optional part and if omitted the default is 2.

no_switch:- Don’t switch to straight line. It is an optional part and if omitted default is FALSE.

Example of VDB function:-

The following pictures show the example of the VDB function.

49. XIRR:- In Microsoft Excel “XIRR” function returns the Internal Rate of Return (IRR) for a series of cash flows that occurs irregular intervals.

Syntax of XIRR function

=XIRR(values,dates,[guess])

Arguments:-

values:-  Contains cash flow series (array).

dates:- The dates reference that correspond to cash flows.

guess:- An estimate for expected IRR. It is an optional part and if omitted then default is 1 (10%).

Example of XIRR function:-

The following pictures show the example of the XIRR function.

50. XNPV:- In Microsoft Excel “XNPV” function calculates the Net Present Value (NPV) of an investment by using discount rate and a series of cash flows with dates.

Syntax of XNPV function

=XNPV(rate,values,dates)

Arguments:-

rate:- The discount rate for cash flows.

values:- The values representing cash flows.

dates:- Dates that indicates to cash flows.

Example of XNPV function:-

The following pictures show the example of the XNPV function.

51. YIELD:- In Microsoft Excel “YIELD” function calculates the yield of a security that pays periodic interest.

Syntax of YIELD function

=YIELD(settlement,maturity,rate,pr,redemption,frequency,[basis])

Arguments:-

settlement:- The security’s settlement date.

maturity:- The security’s maturity date.

rate:- The security’s annual coupon rate.

pr:- The security’s price  per $100 face value.

redemption:- Redemption value per $100 face value.

frequency:- Number of coupon payments per year (1=annual, 2=semiannual, 4=quarterly)

basis:- It specifies the day count basis to be used in the calculation. It is an optional part and if omitted the default is 0.

BASIS	Day Count Basis
0 (omitted)	US (NASD) 30/360
1	actual/actual
2	actual/360
3	actual/365
4	EUROPEAN 30/360

 

Example of YIELD function:-

The following pictures show the example of the YIELD function.

52. YIELDDISC:- In Microsoft Excel “YIELDDISC” function returns the annual yield for a discounted security.

Syntax of YIELDDISC function

= YIELDDISC(settlement,maturity,pr,redemption,[basis])

Arguments:-

settlement:- The security’s settlement date.

maturity:- The security’s maturity date.

pr:- The security’s price  per $100 face value.

redemption:- Redemption value per $100 face value.

basis:- It specifies the day count basis to be used in the calculation. It is an optional part and if omitted the default is 0.

BASIS	Day Count Basis
0 (omitted)	US (NASD) 30/360
1	actual/actual
2	actual/360
3	actual/365
4	EUROPEAN 30/360

Example of YIELDDISC function:-

The following pictures show the example of the YIELDDISC function.

53. YIELDMAT:- In Microsoft Excel “YIELDMAT” function returns the annual yield of a security that pays interest at maturity.

Syntax of YIELDMAT function

=YIELDMAT(settlement,maturity,issue,rate,pr,[basis])

Arguments:-

settlement:- The security’s settlement date.

maturity:- The security’s maturity date.

issue:- The security’s issue date.

rate:- The security’s annual coupon rate.

pr:- The security’s price  per $100 face value.

basis:- It specifies the day count basis to be used in the calculation. It is an optional part and if omitted the default is 0.

BASIS	Day Count Basis
0 (omitted)	US (NASD) 30/360
1	actual/actual
2	actual/360
3	actual/365
4	EUROPEAN 30/360

 Example of YIELDMAT function:-

The following pictures show the example of the YIELDMAT function.

Read More