USING FORMULAS AND FUNCTIONS
FORMULA BASICS
Formulas and functions are among the fundamental building blocks of a spreadsheet. It is important to first understand formulas before going on to using the more complex functions, as formulas are very often combined with functions to achieve your particular desired result.
Formulas always begin with an = sign followed by one or more values or cell references. If more than one cell is referenced, an operator such as + or - is used between the references to define the type of calculation to be performed. Absolute or relative cell references may be used.
Any of the following are valid formulas:
=4 + 5 Adds 4 and 5 and places the result in the cell containing the formula
=A2 + C9 Adds the contents of cell A2 and C9 and returns the result.
=A2 * 2 Returns the value of the contents of cell A2 multiplied by 2.
=A3+D4+C9 Adds the value of these three cells and returns the result.
=$A$2 + $B$2 Adds the value of cell A2 and B2 and returns the result. This formula remains the same if copied to a new cell since it contains absolute cell references.
Several different operations are included in a formula by enclosing each within parenthesis. The operations within the parenthesis are computed first.
Example: =(A2+A4) /6 Adds the contents of cell A2 and A4 and divides the result by 6.
=(A2/3)+(F7-4) Divides the value in A2 by 3 and adds the value obtained by subtracting 4 from the contents of cell F7.
=((A2+4)/(B2-7)*2) Adds 4 to the value in A2 and divides the result by the value in B2 less 7. The result of that calculation is multiplied by 2.
Formula information is also combined with functions to obtain a particular result. For example, any of the formulas above combined with the round function result is the answer rounded off to an integer. If the result of =(A2+A4) /6 is 7.9782 and you want a whole number, use the round function. Your new cell entry is =Round((A2+A4)/6) and the result is returned as 8.
Formula Operators are used in formulas to specify what action is to take place between two or more numeric references. The following are used in formulas.
Arithmetic Operators
Operator Description Example
+ Addition =A1+A2
- Subtraction =A1-A2
* Multiplication =A1*A2
/ Division =A1/A2
Mod Remainder =A1ModA2
Div Integer division =A1DivA2
(Mod and Div are discussed in greater detail under General Math in the Funtion Reference Section below.)
These may be combined with functions to produce a specific desired result.
Example: =SumAbove *2 in cell A11 adds the values in A1 to A10 and multiplies the result by 2.
Comparison Operators
Operator Description
< Less than
<= Less than or equal to
> Greater than
=> Greater than or equal to
<> Not equal
Text Operators
Operator Description
& Concatenates strings or numbers and strings
&& Concatenates strings or numbers and strings with one space between them
(& and && are discussed in greater detail under String Functions in the Funtion Reference Section below.)
ABSOLUTE AND RELATIVE CELL REFERENCES
Most formulas and functions require a cell reference. Cells are referenced by their column and row number. For example, the cell reference C7 is in the third row and the seventh column.
There are two types of cell references, relative and absolute. Relative cell references adjust to their new location when copied and pasted or when the fill option is used. Absolute cell references refer to a particular cell and do not change when the formula is copied or moved to a new cell.
Unless you specify otherwise, AvailWorks uses a relative cell reference. Most often when copying, cutting, pasting and using the fill option, you want the formulas and functions to adjust to their new location.
Absolute cell references are created by including the dollar sign ($) before the column letter and the cell number. An absolute reference to cell C7 is written as $C$7. (The $ sign is found on the A-M pop-up menu.)
If this formula, =A1+B1+C1, which appears in cell D1 is copied to D2, the resulting formula in D2 is = A2+B2+C2. The cell references adjust to reflect the new row and the resulting number is a sum of the values in row 2. You must use Cut and Paste or Fill for relative cell references to be updated. Dragging the cell contents to the Newton clipboard and back to the Cell Entry Area for another cell only places an exact copy of the original text without updating the formulas.
The same formula written with absolute cell references becomes =$A$1+$B$1+$C$1. If this formula is copied to cell D2, the formula remains the same and the resulting number is still the sum of the values in row 1.
On some occasions you may want absolute and relative cell references to be mixed. You can create a formula where the row changes but not the column, or the column changes but the row remains fixed. The following chart shows you the possible combinations.
Sample Description
A1 Both row and column are relative
$A1 Column is absolute, row is relative
A$1 Row is absolute, column is relative
$A$1 Both row and column are absolute
WHAT IS A FUNCTION?
A function is a formula shortcut. Functions are prebuilt to perform math, text, or logical calculations and are found on the function keypad of the Table Editor. Whenever possible, use functions rather than formulas to speed up your work.
For example, to add the numbers in cell A1 to A5 you could type =A1+A2+A3+A4+A5 in cell A6. Or you could select cell A6, tap the General Math pop-up menu and select SumAbove to get a total of the cells above A6. Obviously, the second process is much faster and avoids typographical errors.
Some functions can stand alone and require no parameters. An example of a stand-alone function is pi in the trigonometry menu. It is used to enter the value of pi in a cell or to include it in a formula, such as =A2 * Pi.
On the statistics menu are the options Above, Left, Below and Right. These designators are added to the statistical functions when appropriate to quickly designate the cells involved in the function. For example, you have a column of numbers from cell B6 to B20 and you want the average of those numbers in cell B21.
Tap in cell B21 to make it active. Pop up the statistics menu and select Avg. The entry =Avg( appears in the cell entry area. Tap on statistics again and select Above. The text in the cell entry area now contains the complete formula, =Avg (Above). You could also enter this as =Avg(B6:B20), but it takes quite a few more strokes than using Above.
SYNTAX OF FUNCTIONS
Functions require that information be entered in a certain order. Most functions start with an equal sign followed by the function name and one or more arguments contained within parentheses. The left parenthesis is included for you when you select the function. Be sure to include a right parenthesis at the end of your function.
IMPORTANT!!! -- Functions containing strings must have quotation marks around each string reference even if the reference refers to a cell.
Some functions contain two or more arguments which are separated by colons or commas. To identify a range of cells within the same column or row, the colon is used between the beginning cell and the ending cell.
For example, to add cells B5 to B10 you could enter =B5+B6+B7+B8+B9+B10. A simpler way is to use the Sum function and enter the range. The formula now reads =Sum(B6:B10).
Many of the money functions contain two or more arguments separated by commas and entered in a specific order. An example of this is the function for figuring depreciation based on the straight-line method.
The three arguments that must be entered are the original cost, salvage value and the life of the asset. The function entry for an item costing $10,000 with a salvage value of $1000 at the end of 5 years is written as =SLN (10000,1000,5).
Complete descriptions of AvailWorks functions are contained in the Function Reference Section below.
Please note that some Newton hardware models have limited memory. On these models, you may receive Òout of memoryÓ messages when working with complex AvailWorks pages, particularly when using large spreadsheets. The maximum size spreadsheet is about 200 to 300 cells, depending on the model of Newton and the number of other objects on the page. To minimize memory usage:
1) Create only as many rows and columns as you actually need. DonÕt leave unnecessary blank rows and columns.
2) Use the simplest formula or function to accomplish your purpose.
3) Keep all text entries and titles as short as possible.
FUNCTION ON-LINE HELP
The six function keys pop up menus for strings, general math, statistics, time & money, conditionals, and trigonometry. Each pop-up menu has the category name of the function as a header. To view brief, on-line descriptions of each function within the category, tap on the header name.
All of the function help screens have dotted lines separating the subcategories. The subcategories group together functions that work in a similar fashion.
For example, in the General Math screen the functions that use one parameter are grouped together. Other groups consist of those using two parameters, specific parameters that are illustrated and special operators. Each subcategory or grouping is followed by an example.
USING NEWTONSCRIPT IN AVAILWORKS
This advanced feature is intended only for AvailWorks users who are also NewtonScript programmers. If you are unfamiliar with NewtonScript, you should skip this section.
NewtonScript is the native programming language used by Newton developers to create applications for the Newton. A complete discussion of NewtonScript is well beyond the scope of this documentation, but is covered in great depth in the references cited below.
In relation to AvailWorks, it is also possible to include valid NewtonScript commands in the syntax of table cell formulas. The commands which are valid for inclusion are subject to the same conditions and limitations as the NewtonScript Compile function.
NewtonScript programming is a separate area of expertise, and is in no way required to make full use of AvailWorks. NewtonScript programming can be quite complex at times, and Avail Technology cannot provide technical support on the use of NewtonScript. Interested users should see the following references for further information:
Newton Programmer's Guide
Copyright 1993,1994 by Apple Computer, Inc.
The NewtonScript Programming Language
Copyright 1993,1994 by Apple Computer, Inc.
Programming For The Newton
by Julie McKeehan and Neil Rhodes
Copyright 1994
Published by AP Professional, Cambridge, MA.
FUNCTION REFERENCE
HOW TO USE THE FUNCTION REFERENCE
The following section provides you with descriptions of the functions in AvailWorks. They are grouped as they appear on the pop-up menus in the Table Editor.
There are six pop-up menus containing the functions. At the top of each pop-up is a heading describing the category of functions on that menu. Tapping on this heading produces a help screen for that function group.
The menu itself may be divided into several subsections separated by lines. The functions within each subsection operate in a similar manner.
In this reference section, each function, where appropriate, is followed by words within parentheses. These words indicate the type of information (called parameters or arguments) necessary to complete the function. Descriptions of arguments that are unique to a function are contained in the description of the function itself.
The argument descriptions and their meanings as used in this manual are as follows:
Argument Type
string Text or a cell reference enclosed in quotes.
number A number, reference to a cell containing a number or a formula
whose result is a number.
startcell Reference to the first cell in a series.
endcell Reference to the last cell in a series.
selector Requires a specific entry from a described list.
STRING FUNCTIONS
These functions operate on and manipulate strings and text. Each string must be enclosed in quotation marks. If you do not include the quotation marks around a cell reference, you get an error message, Òexpected a stringÓ or Òundefined variable.Ó
String functions may not contain formulas, but may reference a cell containing a formula or another function.
BeginsWith(string,substr)
Returns TRUE if string begins with substr, or returns NIL otherwise. This function is not case sensitive. This is used most often in an ÒIfÓ statement.
Example: If BeginsWith(ÒA4Ó,ÒYÓ) Then ÒYesÓ Else ÒNoÓ returns Yes if the value in A4 begins with Y, otherwise it returns No.
Capitalize(string)
Capitalizes the first character in the string.
Example: =Capitalize(Òpayment is dueÓ) returns Payment is due
=Capitalize(ÒA2Ó) returns Hello if cell A2 contains hello
CapitalizeWords(string)
Capitalizes the first character of each word in the string and returns the result.
Example: =CapitalizeWords(Òpayment is dueÓ) returns Payment Is Due
=CapitalizeWords(ÒA2Ó) returns Hello if cell A2 contains hello
DownCase(string)
Returns all the characters of each word in the string in lower case.
Example: =DownCase(ÒPayment Is DueÓ) returns payment is due
=DownCase(ÒA2Ó) returns hello if cell A2 contains Hello
EndsWith(string,substr)
Returns TRUE if string ends with substr, or returns NIL otherwise. This function is not case sensitive. This is used most often in an ÒIfÓ statement.
Example: If EndsWith(ÒA4Ó,Ò4Ó) Then ÒYesÓ Else ÒNoÓ returns Yes if the value in A4 ends with 4, otherwise it returns No.
NumberStr(number)
Returns a string representation of the number passed in. For example, if you pass in the value of 1234.56 youÕll get back the string Ò1234.56Ó. If you pass in an integer, the string will contain an integer, and if you pass in a real number, the string will contain a real number.
StrCompare(string1,string2)
Returns a negative number if string1 is less than string2. Returns zero if string1 and string2 are equal. Returns a positive number if string1 a is greater than string2. Case is not significant. This is used most often in an ÒIfÓ statement.
Example: =If(StrCompare(ÒA3Ó, ÒA4Ó))=0 Then ÒTrueÓ Else ÒFalseÓ returns True if the contents of cell A3 and A4 are equal, otherwise it returns False.
StrConcat(string1,string2)
Concatenates string1 onto string2 and returns the result as a new string.
Example: =StrConcat(ÒToday is Ó, ÒMondayÓ) returns Today is Monday
=StrConcat(ÒToday is Ó, ÒA1Ó) returns Today is Monday if cell A1 contains the word Monday
StrEqual(string1,string2)
Returns true if string1 and string2 are equal. Case is not significant. This is used most often in an ÒIfÓ statement.
Example: =If StrEqual(ÒA4Ó, ÒMondayÓ) Then 1 Else 0 returns a 1 if cell A4 is equal to Monday, otherwise it returns a 0.
StringToNumber(string)
Parses a string representing a number and returns the real number value (never an integer). Strings with the following kinds of numbers can be parsed:
1
1.2
-12,345
(12,345.67)
StrLen(string)
Returns the number of characters in a string, excluding the null terminator.
Example: =StrLen(ÒhelloÓ) returns 5
=StrLen(ÒA4Ó) returns 6 if cell A4 contains 427.56
SubStr(string,start,count)
Returns a new string containing count characters from string, starting at position start. Character positions begin with zero for the first character.
Example: =SubStr(ÒA2Ó,6,4) returns Work if cell A2 contains Go To Work
=SubStr(ÒA2Ó,0,5) returns Go To if cell A2 contains Go To Work
UpCase(string)
Capitalizes all the characters of each word in the string and returns the result.
Example: =UpCase(Òpayment is dueÓ) returns Payment Is Due
=UpCase(ÒA2Ó) returns Hello if cell A2 contains hello
Special String Operators
These are operators that may be used between strings or between strings and numbers. Be sure to begin the expression with an equals sign.
&
Concatenate strings and/or numbers.
Example: =A2 & Ò£Ó returns 42£ when cell A2 contains 42
&&
Concatenate strings with one space between.
Example: =A1 && ÒcentsÓ returns 42 cents if cell A1 contains the number 42
GENERAL MATH
The General Math menu contains functions for performing basic math operations. The first ten functions are followed by one or more arguments. The arguments are always numbers, or references to cells containing numeric values. The numeric value in the cell referenced may be the result of a formula or function.
The second group of functions on this menu are stand alone functions. They require no parameters or arguments but perform the function specified on cells above or to the left of the cell containing the function.
The last two on the General Math menu are special operators. The function appears between the two arguments.
Abs(number)
Calculates the absolute (positive) value of a number. Enter any number, cell reference or formula for the number argument.
Example: =ABS(-7) returns 7
=ABS(7) returns 7
Ceiling(number)
Returns the smallest integer not less than the number. Enter any number, cell reference or formula for the number argument.
Example: =Ceiling(4.2) returns 5
Floor(number)
Returns the largest integer not greater than the number. Enter any number, cell reference or formula for the number argument.
Example: =Floor(4.2) returns 4
Max(number1,number2)
Returns the maximum number contained in two cells. Number1 represents one numeric cell and number2 is another numeric cell. Enter any number, cell reference or formula for the number arguments.
Example: =Max(A2,C4) returns 5 if A2 contains 5 and C4 contains 2.
Min(number1,number2)
Returns the minimum number contained in two cells. Number1 represents one numeric cell and number2 is another numeric cell. Enter any number, cell reference or formula for the number arguments.
Example: =Min(A2,C4) returns 2 if A2 contains 5 and C4 contains 2.
Pow(number,exponent)
Returns the power of the number based on the exponent. Enter any number, cell reference or formula for the number argument.
Example: =Pow(25,2) returns 625
=Pow(25,-2) returns 0.0016
Round(number)
Rounds the number to the number to a whole number. Enter any number, cell reference or formula for the number argument.
Example: =Round(4.8957) returns 5
=Round(4.2) returns 4
Signum(number)
Produces 1 when the number is positive, 0 when it is zero and -1 when it is negative. Enter any number, cell reference or formula for the number argument.
Example: =Signum(A3) returns 1 when cell A3 is 42
=Signum(-765) returns -1
=Signum(C4) returns 0 when cell C4 is 0
Sqrt(number)
Calculates the square root of a number. The number must be positive. Enter any number, cell reference or formula for the number argument.
Example: =Sqrt(9) produces 3
=Sqrt(A1) produces 5 if cell A1 contains 25
Trunc(number)
Changes the number to an integer by removing the decimal places.
Example: =Trunc(4.79) returns 4
=Trunc(5.2) returns 5
AvgAbove
Returns the average of all the numbers above the cell containing this function. For example, an entry of AvgAbove in cell D4 averages the numbers in cells D1, D2, and D3.
Any blank cells directly above the cell containing the function are ignored. Once a cell containing a number is reached, it keeps moving up the column until it reaches a blank cell, a text label or the edge of the spreadsheet.
AvgLeft
Returns the average of all the numbers to the left of the cell containing this function. For example, an entry of AvgLeft in cell E4 averages the numbers in cells A4, B4, C4 and D4.
Any blank cells directly to the left of the cell containing the function are ignored. Once a cell containing a number is reached, this function keeps moving along the row until it reaches a blank cell, a text label or the edge of the spreadsheet.
SumAbove
Returns the sum of all the numbers above the cell containing this function. For example, an entry of SumAbove in cell D4 averages the numbers in cells D1, D2, and D3. Any blank cells directly above the cell containing the function are ignored. Once a cell containing a number is reached, it keeps moving up the column until it reaches a blank cell, a text label or the edge of the spreadsheet.
SumLeft
Returns the sum of all the numbers to the left of the cell containing this function. For example, an entry of SumLeft in cell D4 sums the numbers in cells A4, B4, and C4. Any blank cells directly to the left of the cell containing the function are ignored. Once a cell containing a number is reached, it keeps moving along the row until it reaches a blank cell, a text label or the edge of the spreadsheet.
Special Math Operators
Div
Performs divisions on integers and returns an integer as a result. The number appears before Div and the divisor is after Div.
Examples: =10 Div 5 produces 2
=7 Div 3 produces 2
=3 Div -2 produces -1
Mod
Calculates the remainder (modulus) of the number divided by the divisor. The divisor cannot be 0. Enter any number, cell reference or formula for the number argument. The number appears before Mod and the divisor is after Mod.
Examples: =10 Mod 5 produces 0 since it has no remainder
=7 Mod 3 produces 1
=3 Mod -2 produces -1
STATISTICS
These functions give you the most commonly used statistical calculations. The functions are performed on either a vertical column or horizontal row of numbers identified by the beginning and ending cell address separated by a colon.
The last four items on the menu are used in conjunction with the statistics functions to specify a group of cells above, below, left or right of the cell containing the function. These are used instead of specifying a range of cells.
Avg(startcell:endcell)
Returns the average of all the numbers within the range defined by startcell and endcell. If one of these cells is blank or contains text, it calculates the average of the remaining cells. The cell ranges must start and end within the same column or row.
Example: =Avg(A3:A18) returns the average of all valid numbers within that range.
Count(startcell:endcell)
Produces a count of all the numbers within the range defined by startcell and endcell. If one of these cells is blank or contains text, it counts the remaining cells. The cell ranges must start and end within the same column or row.
Example: =Count (A3:A6) returns 4 if all four cells contain numbers.
=Count (A3:A6) returns 2 if two cells contain numbers, one cell contains text and one cell is blank.
Median(startcell:endcell)
Returns the median of all the numbers within the range defined by startcell and endcell. If one of these cells is blank or contains text, it calculates the median of the remaining cells. The cell ranges must start and end within the same column or row.
The median is the middle value in a designated set of numbers. Half of the numbers are above the median and half are below. In the case of an even number of values, the median is the average of the two middle numbers.
Example: =Median(A3:18) returns the median value of the numbers within that cell range.
MaxRange(startcell:endcell)
Returns the maximum number in a range of cells. Startcell represents the first cell in the range and endcell is the last cell. The range of cells may be either horizontal or vertical.
Example: =MaxRange(C4:C8) returns 9 if these cells contain the values 4, 3, 2, 5, and 9.
MinRange(startcell:endcell)
Returns the minimum number in a range of cells. Startcell represents the first cell in the range and endcell is the last cell. The range of cells may be either horizontal or vertical.
Example: =MinRange(C4:C8) returns 2 if these cells contain the values 4, 3, 2, 5, and 9.
StdDev(startcell:endcell)
Calculates an estimate of the standard deviation of the population based on the sample of the population contained in the range of cells between startcell and endcell.
Example: =StdDev(C4:G4) produces the estimated standard deviation using the values in cells C4 to G4 as representative of the total group.
StdDevp(startcell:endcell)
Calculates the standard deviation of a population where the entire population is contained in the range of cells between startcell and endcell.
Example: =StdDevp(C4:G4) produces a standard deviation using the values in cells C4 to G4 as equaling the total group.
Sum(startcell:endcell)
Returns the sum of all the numbers within the range defined by startcell and endcell. If one of these cells is blank or contains text, it calculates the sum of the remaining cells. The cell ranges must start and end within the same column or row.
Example: =Sum(A3:A18) returns the sum of the numbers within that range.
Var(startcell:endcell)
Calculates an estimate of the variance of the population based on the sample of the population contained in the range of cells between startcell and endcell.
Example: =Var(C4:G4) produces the estimated variance using the values in cells C4 to G4 as representative of the total group.
Varp(startcell:endcell)
Calculates the variance of a population where the entire population is contained in the range of cells between startcell and endcell.
Example: =Varp(C4:G4) produces a variance using the values in cells C4 to G4 as equaling the total group.
Statistics Special Operators
Above
Performs the function on the cells immediately above the cell containing the function. Any blank cells directly above the cell containing the function are ignored. Once a cell with a number is reached, it keeps moving up the column until it reaches a blank cell, a text label or the edge of the spreadsheet.
Left
Performs the function on the cells immediately to the left of the cell containing the function. Any blank cells directly to the left of the cell containing the function are ignored. Once a cell with a number is reached, it keeps moving across the row until it reaches a blank cell, a text label or the edge of the spreadsheet.
Below
Performs the function on the cells immediately below the cell containing the function. Any blank cells directly below the cell containing the function are ignored. Once a cell with a number is reached, it keeps moving down the column until it reaches a blank cell, a text label or the edge of the spreadsheet.
Right
Performs the function on the cells immediately to the right of the cell containing the function. Any blank cells directly to the right of the cell containing the function are ignored. Once a cell with a number is reached, it keeps moving across the row until it reaches a blank cell, a text label or the edge of the spreadsheet.
TIME & MONEY
The Time functions enable you to quickly enter dates and times in various formats. The resulting entries remain static on the table displayed on the page until you double tap on the table to access the Table Editor. Any dates and times are updated to the current values when the table is recomputed.
The Money functions help you to quickly analyze cash flows, compute a payment over a specified time and figure depreciation. Arguments in the Time & Money functions may not contain formulas but may reference cells with formulas.
Date
Returns the current date as MM/DD/YY.
Example: =Date returns 4/22/94
DateAndTime
Returns the current date and time of day as MM/DD/YY HH:MM followed by am or pm.
Example: =DateAndTime returns 3/3/94 3:11 pm
DateInfo(selector)
Returns date information based on the following:
Selector for date Example of value
year 1994
month 4
date 24
dayofweek 0 (Sunday=0, Saturday=6)
hour 15
minute 38
Example: =DateInfo(month) returns 7 if the current month is July
=DateInfo(dayofweek) returns 2 if it is Tuesday
DayAndDate
Returns the day and date as DAY MM/DD/YY.
Example: =DayAndDate returns FRI 4/1/94
TimeOfDay
Returns the time of day as HH:MM identified as am or pm.
Example: =TimeOfDay returns 3:11 pm
Year
Returns the current year.
Example: =Year returns 1994
DDB(cost, salvage, life, period, factor)
Figures the depreciation for the period you specify, using the double-declining balance depreciation method.
Argument Description
cost The initial cost of an asset
salvage The value of the asset at the end of the depreciation period.
life The number of periods over which the asset is depreciated.
period The period for which you are calculating the depreciation. The unit for period must be the same as used for life.
factor The rate at which the balance of the asset declines (most often 2).
Example: A piece of equipment was purchased for $10,000 and has an estimated useful life of 5 years. The residual value is $1000. How much is the depreciation in the first year?
=DDB(10000,1000,5,1,2) results in $4000 as the amount of depreciation.
FV(rate, nYears, nPmtYear, pmt, PV, type)
Calculates the future value of an investment which has a constant payment amount over time and a fixed interest rate.
Argument Description
rate The interest rate per period
nYears Total years in the life of the annuity.
nPmtYear Total number of payments per year.
pmt The payment made each period. This amount does not change for the life of the annuity.
pv The present value of the annuity, usually a lump sum deposited at the beginning of the term.
type A special argument that takes one of two values: 0 means payment at the end of the period, and 1 means payment at the beginning of the period.
Example: What will be the value in 5 years of an investment of $5000 per year for 5 years at 5% interest with the payments made at the beginning of each year?
=FV(.05, 5, 1, 5000, 0,1) returns $29,009.56 as the value at the end of 5 years.
IPmt(rate, per, nYears, nPmtYear, principal)
Calculates how much of a given payment for a specified period is interest.
Argument Description
rate The interest rate per year.
per The period for which you want to find the interest.
nYears Total years in the life of the loan.
nPmtYear Total number of payments per year.
principal The principal amount of the loan.
Example: How much of the payment made in the 12th month on a 3 year loan of $25000 at 10% is interest?
=IPmt(.1, 12, 3, 12, 25000) returns $151.14 as the interest portion of the 12th payment.
NPer(rate, pmt, nPmtYear, principal)
Calculates the number of periods to pay off a loan given the amount of the loan, the interest rate and the monthly payment.
Argument Description
rate The interest rate per period.
pmt The amount of the payment.
nPmtYear Total number of payments per year.
principal The principal amount of the loan.
Example: How many months to pay off a $1000 loan at 7% interest with $75 monthly payments?
=NPer(.07, 75, 12, 1000) returns 13.9 as the total number of payments.
NPV(rate, startcell:endcell)
Calculates the net present value of an investment based on a series of cash flows and a discount rate. The value returned reflects the value of an investment today based on the cash flows at the indicated discount rate. It assumes the rate you specify is what you might have received had you invested elsewhere, or the rate of inflation. It is not the same as an interest rate.
Amounts of the payments in are expressed as negative values and the payments received out are positive values.
Argument Description
rate Discount rate per period.
startcell The cell which contains the amount of the first payment in or out.
endcell The cell which contains the amount of the last payment either in or out.
Example: What is the net present value of a $10,000 investment which in subsequent years pays back $3000, $4200, and $6800, and the assumed discount rate is 10%?
Cell A1 = -10000
Cell A2 = 3000
Cell A3 = 4200
Cell A4 = 6800
=NPV(.1, A1:A4) returns $1188.44 as the net present value.
NYrs(rate, pmt, nPmtPerYear, principal)
Calculates the years to pay off a loan given the amount of the loan, the interest rate and the monthly payment.
Argument Description
rate The interest rate per period.
pmt The amount of the payment.
nPmtYear Total number of payments per year.
principal The principal amount of the loan.
Example: How many years to pay off a $1000 loan at 7% interest with $75 monthly payments?
=NYrs(.07, 75, 12, 1000) returns 1.16 as the total number of years.
PMT(rate , nYears, nPmtPerYear, principal)
Calculates the periodic payments required to achieve a certain return based on a known interest.
Argument Description
rate The interest rate per period.
nYears Total years in the life of the annuity.
nPmtYear Total number of payments per year.
principal The principal amount of the annuity.
Example: How much are the monthly payments on a $25000 loan over a 3 year period at 10% interest?
=PMT(.1, 3, 12, 25000) returns $806.68 as the monthly payment.
PPMT(rate, per, nYears, nPmtYear, principal)
Calculates how much of a given payment for a specified period is principal.
Argument Description
rate The interest rate per year.
per The period for which you want to find the interest.
nYears Total years in the life of the loan.
nPmtYear Total number of payments per year.
principal The principal amount of the loan.
Example: How much of the payment made in the 12th month on a 3 year loan of $25000 at 10% is principal?
=PPmt(.1, 12, 3, 12, 25000) returns $655.54 as the principal part of the 12th payment.
PV(rate, nYears, nPmtYear, pmt, FV, type)
Calculates the present value of an investment which has a constant payment amount over time and a fixed interest rate.
Argument Description
rate The interest rate per period
nYears Total years in the life of the annuity.
nPmtYear Total number of payments per year.
pmt The payment made each period. This amount does not change for the life of the annuity.
fv The future value of the annuity which is the value of the cash balance that you want to attain after the last payment is made.
type A special argument that takes one of two values: 0 means payment at the end of the period, and 1 means payment at the beginning of the period.
Example: You want to take out a loan to purchase a car. You know you can afford $275 a month for four years and the interest rate is 9%. How big a loan can you afford?
=PV(.09,4,12, 275,0,0) returns $11050.81
SLN(cost, salvage, life)
Calculates the depreciation of an asset using the straight-line method of depreciation.
Argument Description
cost The initial cost of an asset.
salvage The amount that represents the value of an asset at the end of the depreciation period.
life The number of periods over which an asset is depreciated.
Example: A piece of equipment was purchased for $10,000 and has an estimated useful life of 5 years. The residual value is $1000. How much is the depreciation per year?
=SLN(10000, 1000, 5) returns $1800 annual depreciation.
SYD(cost, salvage, life, per)
Calculates the depreciation of an asset using the sum-of-yearsÕ-digits method of depreciation.
Argument Description
cost The initial cost of an asset.
salvage The amount that represents the value of an asset at the end of the depreciation period.
life The number of periods over which an asset is depreciated.
per The period for which you want to find the depreciation. The unit of measurement must be the same as life.
Example: A piece of equipment was purchased for $10,000, has an estimated useful life of 5 years and a residual value of $1000. How much is the depreciation for the first year?
=SYD(10000, 1000, 5, 1) returns $3000 as the amount of depreciation for the first year.
CONDITIONALS
The conditional functions allow you to add decision-making and logical preferences to your spreadsheet.
If
Returns a specific result when the if condition is met. It is usually used in conjunction with then.
Example: =If A3 = 2400 Then ÒtrueÓ returns the string ÒtrueÓ if the contents of A3 is equal to 2400. If not, nothing is returned.
Then
Used in conjunction with if to produce a specific result when conditions are met. See example above.
Begin
Used to identify the beginning of a group of statements that are to be considered one expression. Can be used when you want to execute more than one action in the then part of a conditional statement.
End
Used to identify the end of a group of statements that are to be considered one expression. (See Begin)
Else
Sets up an alternate result to a condition when used in conjunction with if and then.
Example: =If A3 = 2400 Then ÒtrueÓ Else ÒfalseÓ returns the string ÒtrueÓ if the contents of A3 is equal to 2400. If not, ÒfalseÓ is returned.
;
Used to separate the statements to be considered one expression when used with the Begin and End commands. (See Begin)
<
A comparative operator meaning less than.
Example: =If A3 < 2400 Then ÒtrueÓ returns the string ÒtrueÓ if A3 is less than 2400. If it is 2400 or greater, nothing is returned.
<=
A comparative operator meaning less than or equal to.
Example: =If A3 <= 2400 Then ÒtrueÓ returns the string ÒtrueÓ if the contents of A3 is less than or equal to 2400. If it is greater than 2400, nothing is returned.
>
A comparative operator meaning greater than.
Example: =If A3 > 2400 Then ÒtrueÓ returns the string ÒtrueÓ if A3 is greater than 2400. If it is 2400 or less, nothing is returned.
=>
A comparative operator meaning greater than or equal to.
Example: =If A3 >= 2400 Then ÒtrueÓ returns the string ÒtrueÓ if A3 is greater than or equal to 2400. If it is less than 2400, nothing is returned.
<>
A comparative operator meaning not equal to.
Example: =If A3 <> 2400 Then ÒtrueÓ returns the string ÒtrueÓ if A3 is not equal to 2400. If it is equal to 2400, nothing is returned.
Not
Reverses the result of a statement.
Example: =If Not(A3 = 2400) Then ÒtrueÓ returns the string ÒtrueÓ if the contents of A3 is not equal to 2400. If it is, nothing is returned.
And
Links two or more arguments together and produces the result only when all conditions are met.
Or
Links two or more arguments together and produces the result only when any, but not necessarily all, conditions are met.
TRIGONOMETRY
This menu contains basic trigonometry functions. The value entered for number may be an integer, a real number, a cell reference, a formula or a function.
The angles specified in the number argument of the trigonometry function must be expressed in radians.
The last option, Pi is a stand alone function and has no number reference.
ACos(number)
Returns the arccosine of number when number is an integer or real number.
ACosh(number)
Returns the arc-hyperbolic-cosine of number when number is an integer or real number.
ASin(number)
Returns the arcsine of number when number is an integer or real number.
ATan(number)
Returns the arctangent of number when number is an integer or real number.
ATan2(number1,number2)
Returns the arctangent of number1/number2 in [-pi, pi] using the sign of both parameters to determine the quadrant of the computed value when number1 and number2 are an integers or real numbers.
ATanh(number)
Returns the arc-hyperbolic-tangent of number when number is an integer or real number.
Cos(number)
Returns the cosine of number when number is an integer or real number.
Degrees(angle in radians)
Converts radians into degrees.
Erf(number)
Returns the error function of number when number is an integer or real number.
Erfc(number)
Returns the complementary error function of number