MS Excel: Advanced Functions

1>> Round Function
=ROUND(cell,number of digits to round)
i.e. cell D2 = 45.256
=round(d2,1) would return 45.3
=round(d2,2) would return 45.26
=round(d2,-1) would return 50
=round(d2,-2) would return 0
2>> Payment Function
=PMT(interest rate,total number of payments,principal,future value,type)
Type is a 0 or 1 indicting when the payment is due.
0 or omitted = At the end of the period
1 = At the beginning of the period
Future value and type are optional arguments.
Note: be sure interest for year is divided by 12
Note: PMT function should be preceded by a minus to display a positive number.
3>> Countif Function
The countif function will count how many cells within a range meet the criteria or test.
COUNTIF(range,criteria)
Range = range to check
Criteria = test
4>> If Function
A simple If function will allow a cell to change depending on a conditional test.
=IF(test, what to do if it is true, what to do if it is false)
5>> Embedded If Function
An embedded If function will allow a cell to change depending on multiple conditional tests.
=IF(test, what to do if it is true, IF(test, what to do if it is true, what to do if it is false))

6>>IS Functions
IS functions test the value of a cell.
Function Returns TRUE if
ISBLANK(value) Value refers to an empty cell.
ISERR(value) Value refers to any error value except #N/A.
ISERROR(value)
Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!,
#NAME?, or #NULL!).
ISLOGICAL(value) Value refers to a logical value.
ISNA(value) Value refers to the #N/A (value not available) error value.
ISNONTEXT(value)
Value refers to any item that is not text. (Note that this function returns TRUE
if value refers to a blank cell.)
ISNUMBER(value) Value refers to a number.
ISREF(value) Value refers to a reference.
ISTEXT(value) Value refers to text.
7>>Pivot Table Function
GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)
8>>Database Functions
MS Excel has functions built in for specific use with a database.
All of the database functions have the same arguments.(database,field,criteria)
Database: the range of your data including the field names (column headings)
Field: the field (column) that holds the values to be averaged. Use the field name in quotes
or the field number (count from left to right ie column D would be field number 4)
Criteria: the cells that hold the condition on which the values should be averaged
Examples from Microsoft Excel Help

Results Formula Description (Result)
1 DCOUNT(A4:E10,"Age",A1:F2)
This function looks at the records of apple trees between a height of 10 and 16 and
counts how many of the Age fields in those records contain numbers. (1)
1 DCOUNTA(A4:E10,"Profit",A1:F2)
This function looks at the records of apple trees between a height of 10 and 16 and
counts how many of the Profit fields in those records are not blank. (1)
105 DMAX(A4:E10,"Profit",A1:A3) The maximum profit of apple and pear trees. (105)
75 DMIN(A4:E10,"Profit",A1:B2) The minimum profit of apple trees over 10 in height. (75)
225 DSUM(A4:E10,"Profit",A1:A2) The total profit from apple trees. (225)
75 DSUM(A4:E10,"Profit",A1:F2) The total profit from apple trees with a height between 10 and 16. (75)
140 DPRODUCT(A4:E10,"Yield",A1:B2) The product of the yields from apple trees with a height greater than 10. (140)
12 DAVERAGE(A4:E10,"Yield",A1:B2) The average yield of apple trees over 10 feet in height. (12)
13 DAVERAGE(A4:E10,3,A4:E10) The average age of all trees in the database. (13)
2.966479 DSTDEV(A4:E10,"Yield",A1:A3)
The estimated standard deviation in the yield of apple and pear trees if the data in
the database is only a sample of the total orchard population. (2.97)
2.6533 DSTDEVP(A4:E10,"Yield",A1:A3)
The true standard deviation in the yield of apple and pear trees if the data in the
database is the entire population. (2.65)
8.8 DVAR(A4:E10,"Yield",A1:A3)
The estimated variance in the yield of apple and pear trees if the data in the
database is only a sample of the total orchard population. (8.8)
7.04 DVARP(A4:E10,"Yield",A1:A3)
The true variance in the yield of apple and pear trees if the data in the database is
the entire orchard population. ( 7.04)


Goal Seek
Goal Seek allows the user to dictate an answer to a formula. This works by allowing the
system to change a cell containing a constant used in the formula.
To use Goal Seek, click on a formula, then from the menu choose
Tools, Goal Seek.