@N inspects Block and returns the numeric value of the upper left cell. If that cell contains a label or is blank, it returns a 0.
This @function is used by other spreadsheet programs to avoid unnecessary ERR values resulting from labels included in calculations. This is unnecessary with Quattro Pro, however, because labels are already considered zero values in calculations. @N is included in Quattro Pro only for compatibility with other products.
@NA - NA Value (Not Available)
Syntax
@NA
@NA returns the special value NA (not available). Formulas that depend on a value entered as @NA return the value NA, unless there is an error, in which case they return ERR. NA is a unique number, not to be confused with the label NA.
@NA is used to indicate values not yet available (it will not work with labels). It ensures that formulas relying on information that is not provided do not display inaccurate data.
Examples
A B C D
1 QTR North South West
2 1 $187,681 $151,136 $131,123
3 2 $170,072 NA $149,181
4
5 YTD $357,753 NA $280,304
6 AVG $178,877 NA $140,152
@NA has been entered for the South's Qtr 2 results. As you can see, the NA cascades through to the totals. When the @NA is replaced with a valid value, the totals will immediately reflect the correct figures.
@NA = NA
@IF(B3=0,@NA,B3) = NA if B3 = 0; otherwise, the value of B3
@NBDAY - Next Business Day
Syntax
@NBDAY(Date, <Holidays>, <Saturday>, <Sunday>)
Date Number representing a date. See "Using dates and times in Quattro Pro."
Holidays Cells containing dates that are holidays or the date of a single holiday or 0 to indicate no holidays (the default is 0).
Saturday 0 to specify that Saturday is not a business day; 1 to specify that Saturday is a business day (the default is 0).
Sunday 0 to specify that Sunday is not a business day; 1 to specify that Sunday is a business day (the default is 0).
@NBDAY returns the serial date number of the next business day after Date.
Examples
@NBDAY(@DATE(93,2,26)) = 34029 (March 1, 1993)
@NBDAY(@DATE(93,12,24),A7..C9,0,1) = 34329 (December 26, 1993), assuming that Saturdays and the dates in cells A7..C9 are holidays.
@NEGBINOMDIST - Negative Binomial Distribution
Syntax
@NEGBINOMDIST(Failures, Successes, Prob)
Failures Number of failures.
Successes Threshold of successes.
Prob Probability of a success; 0 ú Prob ú 1.
@NEGBINOMDIST returns the negative binomial distribution. Use @NEGBINOMDIST to determine the distribution of the number of failures you experience before achieving a specified number of successes.
Example
A polling organization asks a sampling of voters if they favor Candidate A for reelection. Given that 55% of the city's voters favor Candidate A, this formula calculates the probability that the polling organization will contact 10 voters who do not favor her for reelection before contacting 1 voter who does favor her:
@NEGBINOMDIST(10,1,0.55) = 0.000187
@NENGO
Syntax
@NENGO(Date)
Date The date, comprised of the elements Imperial Year, Month, and Day.
@NENGO converts a date to its kanji representation.
Discrate Discount rate or cells containing discount rates corresponding to cells of cash flows.
Flows Cells containing cash flows.
Initial Initial cash flow (the default is 0).
Odd|Periods Delay between initial and first cash flow in number of periods (the default is 1) or cells containing lengths of periods between cash flows (the default is 1).
Simp Flag specifying how to discount:
0 = compounded discounting (default)
1 = mixed compounded and simple discounting
2 = simple discounting
Pathdep Flag specifying whether to apply path-dependent compounding to each flow; 0 = no path (default); 1 = path.
Start A starting cash flow amount to compare against individual flows.
End An ending cash flow amount to compare against individual flows.
@NETPV computes the net present value of a stream of cash flows.
Example
A firm is considering the purchase of two machines. Machine A requires an initial outlay of $40,000 and produces a cash flow of $23,000 for three years. Machine B requires an outlay of $50,000 and produces a cash flow of $22,000 for four years. The following formulas calculate the net present values of both machines, using the data shown in the next figure and a discount rate of 12%. The results are useful for determining which machine is a better purchase:
Machine A: @NETPV(0.12,A13..B14,B12) = $15,242.12
Machine B: @NETPV(0.12,C13..D14,D12) = $16,821.69
A B C D
11 Machine A Income Machine B Income
12 Initial ($40,000) Initial ($50,000)
13 3 $23,000 4 $22,000
The net present value of the flows associated with Machine B is greater, so it should be purchased.
StartDate Date number representing start date. See "Using dates and times in Quattro Pro."
EndDate Date number representing end date.
Holidays Optional cell name or reference containing serial date numbers of holidays to exclude from the calculation.
Weekends Optional argument, in quotation marks, to tell @NETWORKDAYS which days are weekend days. Use 0 through 6 (Monday through Sunday); for example, "45" means Friday and Saturday. The default, if you omit Weekends, is Saturday and Sunday. To specify no weekends, use "7".
@NETWORKDAYS returns the number of working days between StartDate and EndDate, including EndDate in the total, and excluding weekends and holidays. If StartDate and EndDate are two consecutive working days, the result is 1.
You cannot use any optional argument without using all the ones preceding it. To specify weekends but not holidays, refer to a blank cell for holidays.
Example
You want to know how many working days there will be between mid-November, 1996, and the project due date early the next January. Your company holidays are stored in a cell named Holidays, and your weekend days are Saturday and Sunday.
NperY Number of compounding periods per year, truncated to an integer.
@NOMINAL calculates the nominal annual interest rate for a specified effective rate and number of compounding periods a year.
@NOMINAL is related to @EFFECT in the following way:
where
Rn nominal rate
Re effective rate
Nper number of compounding periods per year
@NOMINAL returns ERR if either argument is non-numeric, if EffectRate <= 0, or if NperY < 1.
Example
@NOMINAL(7.3756%,4) = 0.0718 or 7.18%
@NORMDIST - Normal Distribution
Syntax
@NORMDIST(X, Mean, SDev, Cum)
X Value at which to evaluate function.
Mean Mean of the normal distribution.
SDev Standard deviation of the normal distribution; must be > 0.
Cum 1 to return the cumulative normal distribution function; 0 (the default) to return the probability density function.
@NORMDIST computes the normal distribution function. A normal distribution is one that is perfectly symmetrical about its mean, and its spread is determined by the value of the standard deviation. The normal distribution describes many statistical phenomena, including the distribution of population means.
@NORMDIST uses this formula to calculate the cumulative normal distribution function:
To calculate the probability mass function for a normal distribution, @NORMDIST uses this formula:
Examples
@NORMDIST(50,48,1.2,1) = 0.95221
@NORMDIST(50,48,1.2,0) = 0.082898
@NORMINV - Inverse of Normal Distribution
Syntax
@NORMINV(Prob, Mean, SDev)
Prob Probability corresponding to the normal distribution; 0 < Prob < 1.
Mean Mean of the normal distribution.
SDev Standard deviation of the normal distribution; must be > 0.
@NORMINV returns the inverse of the cumulative normal distribution function.
Example
@NORMINV(0.95221,48,1.2) = 50
@NORMSDIST - Standard Normal Distribution
Syntax
@NORMSDIST(X)
X Value at which to evaluate the function.
@NORMSDIST returns the standard normal cumulative distribution function. The standard normal cumulative distribution function has a mean of 0 and a standard deviation of 1.
@NORMSDIST uses this formula:
Example
@NORMSDIST(1.66667) = 0.95221
@NORMSINV - Inverse of Standard Normal Distribution
Syntax
@NORMSINV(Prob)
Prob Probability corresponding to the normal distribution; must be > 0 and < 1.
@NORMSINV returns the inverse of the standard normal cumulative distribution function. The standard normal cumulative distribution function has a mean of 0 and a standard deviation of 1.
Example
@NORMSINV(0.95221) = 1.66667
@NOT - Logical Not
Syntax
@NOT(List)
List Logical value or expression that can be evaluated to TRUE or FALSE.
@NOT reverses the value of its argument. If Logical is FALSE, @NOT returns TRUE; if Logical is TRUE, @NOT returns FALSE.
Use @NOT when you need to make sure a value is not equal to a certain value.
Example
Enter the following formula in Cell B2 and copy it into Cells B3 through B6. The relative cell address will change as you copy the function, to refer to the cell next to it.
@IF(@NOT(A2=100%),"Keep trying","HOORAY!!")
A B
1 Grades
2 87% Keep trying
3 92% Keep trying
4 75% Keep trying
5 98% Keep trying
6 100% HOORAY!!
@NOW - Current Date and Time
Syntax
@NOW
@NOW returns the serial number corresponding to the current date and time. To display the number as a date or time, right-click the cell, choose Cell Properties, then click Numeric Format.
The value generated by @NOW is updated to the current date and time each time you press the Calc key (F9), or perform any operation that recalculates the notebook.
The integer part of a date/time serial number pertains to the date; the decimal portion pertains to time. To extract just the date portion, use @INT(@NOW) or @TODAY. To extract just the time portion, use @MOD(@NOW,1).
Examples
@NOW = 31905.572338 (5/8/87, 1:45 PM)
@INT(@NOW) = 31905 (5/8/87)
@MOD(@NOW,1) = 0.572338 (1:45 PM)
@INT(@MOD(@NOW,7)) = 6 (the number of the day of the week)
@NPER - Number of Periods
Syntax
@NPER(Rate, Pmt, Pv, <Fv>, <Type>)
Rate A numeric value > -1, representing the periodic interest rate (the fixed interest rate per compounding period).
Pmt A numeric value representing the amount of the periodic payment.
Pv A numeric value representing the current value of an investment (the present value).
Fv A numeric value representing the future value of an investment (the value the investment will reach at some point).
Type An optional numeric value that indicates whether payments or cash flows occur at the beginning (1) or the end (0) of the period; default = 0.
@NPER calculates the number of time periods required for an investment, using an optional argument, Type, to indicate whether the investment is an ordinary annuity or an annuity due. Like @CTERM and @TERM, @NPER computes the number of payments needed to reach Fv, given Pv, Pmt, and Rate. The last two arguments of @NPER, Fv and Type, are optional. If you omit one or both of them, Quattro Pro assumes their values are zero.
Be sure to enter a negative number for money that is out of your pocket and a positive number for money that is coming in to you.
This @function is not compatible with 1-2-3. If your file must be compatible, use @CTERM or @TERM instead.
Examples
Assume you have an IRA account that earns 11.5% interest paid annually at the start of the year, and you deposit $2000 into the account at the end of each year. The present account balance is $633. To determine how many payment periods it will take to reach a nest egg of $50,000, use @NPER:
@NPER(11.5%,-2000,-633,50000,0) = 12.12
The fractional part of the answer is not very meaningful; you cannot be sure of having your nest egg until the end of the 13th year.
@NPV - Present Value of Future Cash Flow
Syntax
@NPV(Rate, Block, <Type>)
Rate A numeric value > -1, representing the periodic interest rate (the fixed interest rate per compounding period).
Block Cells (reference or name) containing cash flow information for the investment.
Type An optional numeric value that indicates whether payments or cash flows occur at the beginning (1) or the end (0) of the period; default = 0.
@NPV calculates the current value of estimated cash flow values (Block), discounted at the given interest rate (Rate). It is helpful in determining how much an investment is currently worth, based on expected earnings, although its accuracy is entirely dependent on the accuracy of the cash flow table.
The optional third argument, Type, can be 0 or 1, depending on whether the cash flows are at the beginning or the end of the period. The default value is 0, end of the period.
The formula for @NPV(Rate,Block,Type)--if Block consists of --is given by
If Type = 0
If Type = 1
The cash flow table in Block should show expected income and debits over a period of time. If Type is 0, Quattro Pro assumes that the amounts are received at the end of regular intervals. If Type is 1, it assumes the amounts are received at the beginning of regular intervals. Quattro Pro also assumes that the length of this interval is the same as the period on which interest is compounded. In other words, if monthly cash flow is estimated, Rate needs to show monthly interest. To convert annual interest to monthly interest, simply divide by 12.
Examples
A B C D E
1 1992 1993 1994 1995 1996
2 -5000 +2000 +2000 +2000 +2000
Suppose you are considering investing $5000 this year, and you expect a return of $2000 in each of the next four years. Put the values -5000,+2000,+2000,+2000,+2000 in the cells A2..E2. The net present value, using a discount rate of 10%, is @NPV(.1,A2..E2,1) which equals $1,340. Or, combine the initial investment with the present value of the four returns with +A2+@NPV(.1,B2..E2,0). The result is the same.
A B C D
1 Jan 8000 200 3500
2 Feb 9000 350 4000
3 Mar 8500 -300 3000
4 Apr 9500 600 5000
@NPV(1.25%,B1..B4) = $33,908.92
@NPV(15%/12,C1..C4) = $820.83
@NPV(15%/12,D1..D4) = $15,006.51
-2000+@NPV(15%/12,D1..D4) = $13,006.51 (assumes an initial cash outflow of $2,000)
@NUMTOBIN - Decimal to Binary
Syntax
@NUMTOBIN(Decimal)
Decimal Decimal number to convert.
@NUMTOBIN returns the binary string equivalent of a decimal number. To convert a negative number, precede Decimal with a minus sign.
Examples
@NUMTOBIN(10) = 1010
@NUMTOBIN(16) = 10000
@NUMTOBIN(30) = 11110
@NUMTOBIN64 - Decimal to Binary
Syntax
@NUMTOBIN64(Decimal, <Places>)
Decimal Decimal number to convert.
Places Number of characters to return; must be ú 64.
@NUMTOBIN64 returns the binary string equivalent of a decimal number (up to 64 bits).
Examples
@NUMTOBIN64(10) = 1010
@NUMTOBIN64(10,5) = 01010
@NUMTOBIN64(123000) = 11110000001111000
@NUMTOBIN64(123000,7) = 1111000
@NUMTOHEX - Decimal to Hexadecimal
Syntax
@NUMTOHEX(Decimal)
Decimal Decimal number to convert.
@NUMTOHEX converts the decimal number Decimal to its corresponding hexadecimal string value. @HEXTONUM performs the opposite conversion, from hexadecimal to decimal.
Examples
@NUMTOHEX(10) = 'A
@NUMTOHEX(16) = '10
@NUMTOHEX(65535) = 'FFFF
@NUMTOHEX64 - Decimal to Hexadecimal
Syntax
@NUMTOHEX64(Decimal, <Places>)
Decimal Decimal number to convert.
Places Number of characters to return; must be ú 16.
@NUMTOHEX64 returns the hexadecimal string equivalent of a decimal number (up to 64 bits).
Examples
@NUMTOHEX64(10) = A
@NUMTOHEX64(10,2) = 0A
@NUMTOHEX64(123000) = 1E078
@NUMTOHEX64(1000000000) = 3B9ACA00
@NUMTOOCT - Decimal to Octal
Syntax
@NUMTOOCT(Decimal)
Decimal Decimal number to convert.
@NUMTOOCT returns the octal string equivalent of a decimal number. To convert a negative number, precede Decimal with a minus sign.
Examples
@NUMTOOCT(10) = 12
@NUMTOOCT(16) = 20
@NUMTOOCT(30) = 36
@NUMTOOCT64 - Decimal to Octal
Syntax
@NUMTOOCT64(Decimal, <Places>)
Decimal Decimal number to convert.
Places Number of characters to return; must be ú 22.
@NUMTOOCT64 returns the octal string equivalent of a decimal number (up to 64 bits).
Examples
@NUMTOOCT64(8) = 10
@NUMTOOCT64(10,3) = 012
@NUMTOOCT64(123000) = 360170
@NUMTOOCT64(123000,3) = 170
@NUMTOOCT64(2^63) = 1000000000000000000000
@NWKDAY - Nth Weekday in Month
Syntax
@NWKDAY(N, Wkday, Month, Year, <AuxWkday>)
N Number from 1 to 5.
Wkday Number from 1 (Saturday) to 7 (Friday).
Month Number from 1 (January) to 12 (December).
Year Number from 0 (1900) to 199 (2099) or a standard year like 1993.
AuxWkday Auxiliary day of the week that must fall in the same week as Wkday; 0 for no auxiliary day or a number from 1 (Saturday) to 7 (Friday) indicating the auxiliary day (the default is 0).
@NWKDAY returns the serial date number for the date of the Nth occurrence of Wkday in Month. If there is not an Nth occurrence, @NWKDAY returns ERR.
See "Using dates and times in Quattro Pro."
You can use AuxWkday to specify another day that must fall in the same week and month as Wkdday; see the second example.
The valid date calculation range for this function is 01/01/1900 through 12/31/2099.
Examples
@NWKDAY(2,3,4,99) = 36262 (April 12, 1999), the date of the second Monday in April 1999.
@NWKDAY(1,7,12,93,3) = 34313 (December 10, 1993), the first Friday on which both the first Friday and a Monday fall in the same week of December 1993.
@OCTTOBIN - Octal to Binary
Syntax
@OCTTOBIN(Oct)
Oct Octal number to convert; denote negative numbers using a minus sign.
@OCTTOBIN returns the binary string equivalent of an octal number.
Examples
@OCTTOBIN("12") = 1010
@OCTTOBIN("20") = 10000
@OCTTOBIN("36") = 11110
@OCTTOHEX - Octal to Hexadecimal
Syntax
@OCTTOHEX(Oct)
Oct Octal number to convert; denote negative numbers using a minus sign.
@OCTTOHEX returns the hexadecimal string equivalent of an octal number.
Examples
@OCTTOHEX("12") = A
@OCTTOHEX("20") = 10
@OCTTOHEX("36") = 1E
@OCTTONUM - Octal to Decimal
Syntax
@OCTTONUM(Oct)
Oct Octal number to convert; denote negative numbers using a minus sign.
@OCTTONUM returns the decimal equivalent of an octal number.
Examples
@OCTTONUM("12") = 10
@OCTTONUM("20") = 16
@OCTTONUM("36") = 30
@ODD - Round Up to Nearest Odd Integer
Syntax
@ODD(X)
X Value to round.
@ODD rounds X up (away from zero) to the nearest odd integer. If X is already an odd integer, @ODD returns X.
FirstCpn Number representing the first coupon date.
Coupon Coupon rate; must be │ 0.
Yield Annual yield; 0 < Yield ú 1.
Redemption Redemption value per 100 face value (must be > 0; the default is 100).
Freq Frequency of coupon payments in number of payments per year (can be 1, 2, 3, 4, 6, or 12; the default is 2).
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@ODDFPRICE returns the price per 100 face value of a bond having an odd (short or long) first period. In an odd first coupon period, the first coupon payment is a prorated multiple of a normal coupon payment.
Dates for @ODDFPRICE must follow this pattern:
Issue < Settle < Maturity
Issue < FirstCpn < Maturity
Example
This formula returns the price per 100 face value of a bond with the following terms: Settle is March 15, 1993, Maturity is November 15, 1995, Issue is January 4, 1992, FirstCpn is May 15, 1993, Coupon is 8.5%, Yield is 8.7%, Redemption is 100, Freq 2, and Calendar is 0 (30/360).
FirstCpn Number representing the first coupon date.
Coupon Coupon rate; must be │ 0.
Price Price of the security; must be > 0.
Redemption Redemption value per 100 face value (must be > 0; the default is 100).
Freq Frequency of coupon payments in number of payments per year (can be 1, 2, 3, 4, 6, or 12; the default is 2).
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@ODDFYIELD returns the yield of a security having an odd (short or long) first period. In an odd first coupon period, the first coupon payment is a prorated multiple of a normal coupon payment.
Dates for @ODDFYIELD must follow this pattern:
Issue < Settle < Maturity
Issue < FirstCpn < Maturity
Example
This formula calculates the yield for a security with the following terms: Settle is March 15, 1993, Maturity is November 15, 1995, Issue is January 4, 1992, FirstCpn is May 15, 1993, Coupon is 8.5%, Price is 100, Redemption is 100, Freq is 2, and Calendar is 0 (30/360).
Settle Number representing the settlement date; must be < Maturity.
Maturity Number representing the maturity date.
LastCpn Number representing the last coupon date; must be < Maturity.
Coupon Coupon rate; must be │ 0.
Yield Annual yield; 0 < Yield ú 1.
Redemption Redemption value per 100 face value (must be > 0; the default is 100).
Freq Frequency of coupon payments in number of payments per year (can be 1, 2, 3, 4, 6, or 12; the default is 2).
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@ODDLPRICE returns the price per 100 face value of a bond having an odd (short or long) last period. In an odd last coupon period, the last coupon payment is a prorated multiple of a normal coupon payment.
Example
This formula calculates the price per 100 face value of a bond with the following terms: Settle is June 1, 1992, Maturity is December 15, 2012, LastCpn is September 15, 2012, Coupon is 7.5%, and Yield is 5.25%.
Settle Number representing the settlement date; must be < Maturity.
Maturity Number representing the maturity date.
LastCpn Number representing the last coupon date; must be < Maturity.
Coupon Coupon rate; must be │ 0.
Price Price; must be > 0.
Redemption Redemption value per 100 face value (must be > 0; the default is 10 0).
Freq Frequency of coupon payments in number of payments per year (can be 1, 2, 3, 4, 6, or 12; the default is 2).
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@ODDLYIELD returns the yield of a bond having an odd (short or long) last period. In an odd last coupon period, the last coupon payment is a prorated multiple of a normal coupon payment.
Example
This formula calculates the yield of a bond with the following terms: Settle is June 1, 1992, Maturity is December 15, 2012, LastCpn is September 15, 2012, Coupon is 7.5%, and Price is 128.0663.
Reference Reference on which you want to base the offset; cannot refer to non-contiguous areas.
Rows Number of rows from Reference you want the offset to refer to. If Rows = 5, the upper left cell in the offset is five rows below the upper left cell in Reference. Negative Rows are above, positive are below.
Cols Number of columns from Reference you want the offset to refer to. If Cols = 5, the upper left cell in the offset is five columns to the right of the upper left cell in Reference. Negative Cols are to the left, positive are to the right.
Height Height (optional) of the returned offset, in rows. Height must be a positive number. If omitted, Height is the same height as Reference.
Width Width (optional) of the returned offset, in columns. Width must be a positive number. If omitted, Width is the same width as Reference.
@OFFSET returns a reference that is offset from another reference by a specified number of rows and columns; dimensions of the offset can also be specified. The upper left cell of the returned reference is offset from the upper left cell of Reference by the number of Cols and Rows you specify.
@OFFSET does not actually move cells or change the selection; it just returns a reference. Use @OFFSET with any function that expects a reference argument.
If Reference is a selection and you do not specify Height = 1 and Width = 1, you must select a selection of the appropriate size to display the result before entering @OFFSET as an array.
@OFFSET returns ERR if Rows and Cols offset Reference over the edge of the notebook sheet.
Examples
The notebook EXPENSES.QPW contains the following cells:
A B C
1 January February March
2 $652 $833 $599
3 $456 $305 $522
4 $68 $59 $73
@OFFSET(A1,2,1,1,1) = [EXPENSES.qpw]A:B3..B3
@OFFSET(A1..C1,3,0,1,3) = [EXPENSES.qpw]A:A4..C4
@OR - Logical Or
Syntax
@OR(List)
List True-or-false conditions to test.
@OR returns 1 (true) if any argument is true, 0 (false) only if all arguments are false.
Arguments must be logical values, or references or arrays that contain logical values.
@OR ignores text or empty cells.
You can use an @OR array formula to see if a certain value occurs in a list of cells.
Examples
Given the following data:
A B
1 $2 $101
2 $50 $115
3 $127 $130
@OR(A1>10,A2>10,A3>10) = 1 (true)
@OR(A1>200,A2>200,A3>200) = 0 (false)
To find which values in column A are less than 100, enter in cell A4 the formula +A1..A3<100. Quattro Pro enters the formula as an array and returns the array {1|1|0} in cells A4..A6, showing the first two values in column A are less than 100. You can do the same in cell B4 for the amounts in column B.
Suppose a $5 service charge is deducted if the daily account balance falls below the $100 minimum. Use @OR to test the true-or-false conditions in A4..A6 and B4..B6, and @IF to subtract $5 or not, depending on the results:
For account A, @IF(@OR(A4..A6), "$5","$0") = $5
For account B, @IF(@OR(B4..B6), "$5","$0") = $0 (no service charge)
@ORB - Binary OR
Syntax
@ORB(Binary1, <Binary2>, <Bits>)
Binary1 First binary number.
Binary2 Second binary number.
Bits Number of binary bits used for both input and output; if omitted, Bits = number of bits in Binary1 or Binary2, whichever is greater; must be in the range 0 <n ú 64.
@ORB performs a bit-by-bit logical OR of each bit in Binary1 and Binary2. Any bit that is set to 1 in either Binary1 or Binary2 causes the resulting output bit to be set to 1.
If only one number is specified, then @ORB performs an any-ones test, or OR reduction, on Binary1; @ORB returns 1 if any bits in Binary1 are set to 1; otherwise, it returns 0.
Examples
@ORB(10,1) = 11
@ORB(10,10) = 10
@ORB(10) = 1
@ORB(1100,1,5) = 01101
@ORH - Hexadecimal OR
Syntax
@ORH(Hex1, <Hex2>, <Bits>)
Hex1 First hexadecimal number.
Hex2 Second hexadecimal number.
Bits Number of binary bits used for both input and output; if omitted, Bits = number of bits in Hex1 or Hex2, whichever is greater; 4 binary digits = 1 hexadecimal digit; must be in the range 0 <n ú 64.
@ORH performs a bit-by-bit logical OR of each bit in Hex1 and Hex2. Any binary bit that is set to 1 in either Hex1 or Hex2 causes the resulting output bit to be set to 1.
If only one number is specified, then @ORH performs an any-ones test, or OR reduction, on Hex1; @ORH returns 1 if any bits in Hex1 are set to 1; otherwise, it returns 0.
Examples
@ORH("A","F") = F
@ORH("A") = 1
@ORH("C","1",8) = 0D
@PAGEINDEX - Index Number for Notebook Sheet
Syntax
@PAGEINDEX(Name)
Name A string corresponding to the name of a sheet; must be enclosed in quotation marks.
@PAGEINDEX returns the index number (from 0 to 255) for a specified sheet name. If no sheet names match the string Name, @PAGEINDEX returns ERR.
To return the index number for a sheet in another notebook, use @PAGEINDEX2.
Example
@PAGEINDEX("EXPENSES") = 0 (the index number for the sheet named EXPENSES is 0)
@PAGEINDEX2 - Index Number for Sheet in Another Notebook
Syntax
@PAGEINDEX2(NotebookLink, Name)
NotebookLink A reference to a sheet, cell, or cells in another notebook (for example, [BUDGET]A:A1).
Name A string corresponding to the name of a sheet; must be enclosed in quotation marks.
@PAGEINDEX2 returns the index number (from 0 to 255) for a specified sheet name in a notebook specified by NotebookLink. If no sheet names match the string Name, @PAGEINDEX2 returns ERR.
Example
@PAGEINDEX2([BUDGET]A:A1,"EXPENSES") = 0 (the index number for the sheet named EXPENSES in notebook BUDGET is 0)
@PAGENAME - Name of a Notebook Sheet
Syntax
@PAGENAME(Index)
Index A number from 0 to 255 inclusive.
@PAGENAME returns the name of a sheet specified by Index. If there is not a name for the specified sheet, @PAGENAME returns ERR.
To return a sheet name from another notebook, use @PAGENAME2.
Example
@PAGENAME(3) = EXPENSES (the sheet with index number 3 is named EXPENSES)
@PAGENAME2 - Name of Sheet in Another Notebook
Syntax
@PAGENAME2(NotebookLink, Index)
NotebookLink A reference to a sheet, cell, or cells in another notebook (for example, [BUDGET]A:A1).
Index A number from 0 to 255 inclusive.
@PAGENAME2 returns the name of a sheet specified by Index in a notebook specified by NotebookLink. If there is not a name for the specified sheet, @PAGENAME2 returns ERR.
Example
@PAGENAME2([BUDGET]A:A1,3) = EXPENSES (the sheet with index number 3 in notebook BUDGET is named EXPENSES)
@PAGENAMES - Table of Sheet Names
Syntax
@PAGENAMES(<ExcludedNames>)
ExcludedNames A optional argument specifying sheet names to exclude from the table; enclose each sheet name in quotation marks, and separate sheet names with a comma.
@PAGENAMES returns a two-column table showing the sheet letters and corresponding sheet names for the active notebook. The left column of the table contains sheet letters (from A to IV), and the right column contains corresponding sheet names.
Because @PAGENAMES returns an array, it is automatically enclosed within an @ARRAY @function.
If the active notebook does not have any named sheets, @PAGENAMES returns ERR.
Make sure there is enough room for a two-column table, with one row for each sheet name. Quattro Pro overwrites existing data in cells it uses for the table.
To return sheet names for another notebook, use @PAGENAMES2.
Example
The active notebook consists of five named sheets, Qtr1, Qtr2, Qtr3, Qtr4, and Totals, whose sheet letters are A, B, C, D, and E, respectively.
@ARRAY(@PAGENAMES) = table in A1..B5 shown in the next figure
A B
1 A Qtr1
2 B Qtr2
3 C Qtr3
4 D Qtr4
5 E Totals
@PAGENAMES2 - Table of Sheet Names in Another Notebook
Syntax
@PAGENAMES2(NotebookLink, <ExcludedNames>)
NotebookLink A reference to a sheet, cell, or cells in another notebook (for example, [BUDGET]A:A1).
ExcludedNames A optional argument specifying sheet names to exclude from the table; enclose each sheet name in quotation marks, and separate sheet names with a comma.
@PAGENAMES2 returns a two-column table showing the sheet letters and corresponding sheet names for the notebook specified by NotebookLink. The left column of the table contains sheet letters (from A to IV), and the right column contains corresponding sheet names.
Because @PAGENAMES2 returns an array, it is automatically enclosed within an @ARRAY @function.
If the active notebook does not have any named sheets, @PAGENAMES2 returns ERR.
Make sure there is enough room for a two-column table, with one row for each sheet name. Quattro Pro overwrites existing data in cells it uses for the table.
Example
A notebook named BUDGET consists of five named sheets, Qtr1, Qtr2, Qtr3, Qtr4, and Totals, whose sheet letters are A, B, C, D, and E, respectively.
@ARRAY(@PAGENAMES2([BUDGET]F:A1)) = table in A1..B5 shown in the next figure
A B
1 A Qtr1
2 B Qtr2
3 C Qtr3
4 D Qtr4
5 E Totals
@PBDAY - Prior Business Day
Syntax
@PBDAY(Date, <Holidays>, <Saturday>, <Sunday>)
Date Number representing a date. See "Using dates and times in Quattro Pro."
Holidays Cells containing dates that are holidays or the date of a single holiday or 0 to indicate no holidays (the default is 0).
Saturday 0 to specify that Saturday is not a business day; 1 to specify that Saturday is a business day (the default is 0).
Sunday 0 to specify that Sunday is not a business day; 1 to specify that Sunday is a business day (the default is 0).
@PBDAY returns the serial date number of the first business day before Date.
Examples
@PBDAY(@DATE(93,3,1)) = 34026 (February 26, 1993)
@PBDAY(@DATE(93,12,26),A7..C9,0,1) = 34325 (December 22, 1993), assuming that Saturdays and the dates in the cells A7..C9 are holidays.
@PAYMT - Amortized Payment
Syntax
@PAYMT(Rate, Nper, Pv, <Fv>, <Type>)
Rate A numeric value > -1, representing the periodic interest rate (the fixed interest rate per compounding period).
Nper A numeric value > 0, representing the number of periods of the loan (the number of payments to be made) or investment (the number of compounding periods).
Pv A numeric value representing the amount borrowed (the principal).
Fv A numeric value representing the future value of an investment (the value the investment will reach at some point).
Type An optional numeric value that indicates whether payments or cash flows occur at the beginning (1) or the end (0) of the period; default = 0.
@PAYMT calculates the periodic payment needed to reach Fv, given Rate, Nper, and Pv. The last two arguments of @PAYMT, Fv and Type, are optional. If you omit the last one or both of them, Quattro Pro assumes that their values are zero. Enter negative numbers for out-of-pocket money and positive numbers for money coming in.
Related @function @PMT is not as flexible, but can be used if your file must be compatible with 1-2-3.
Examples
Assume you want to take out a 30-year $175,000 mortgage with a 17.5% annual interest rate with 12 payments a year, and you would like to see the difference in your monthly payments if you paid at the start or at the end of the month. All you have to do is enter these two @functions:
@PAYMT(17.5%/12,12*30,175000,0,0) = -2566.07
@PAYMT(17.5%/12,12*30,175000,0,1) = -2529.19
If, on the other hand, your mortgage has a "balloon payment" that leaves you with unpaid principal at the end of the mortgage, you can still calculate the payment. Just insert the balloon payment amount (say, $80,000) as the future value component:
@PAYMT(17.5%/12,12*30,175000,-80000,0) = -2559.68
@PEARSON - Correlation
Syntax
@PEARSON(Array1, Array2)
Array1 Array of independent values.
Array2 Array of dependent values.
@PEARSON returns the Pearson product moment correlation coefficient, which measures the linear association of two data sets. Array1 and Array2 must have the same number of values. @PEARSON uses this formula:
A value of r near or equal to 0 implies little or no linear relationship exists between the two lists of numbers. A value of r near or equal to 1 or -1 indicates a very strong linear relationship.
Example
This example refers to cells in the next figure.
@PEARSON(B2..B16,C2..C16) = 0.989324
A B C
1 Date Advertising Sales
2 04/30/93 $435 $7,000
3 05/07/93 $400 $6,000
4 05/14/93 $505 $7,767
5 05/21/93 $470 $7,800
6 05/28/93 $610 $9,534
7 06/04/93 $540 $7,750
8 06/11/93 $575 $8,945
9 06/18/93 $715 $11,301
10 06/25/93 $645 $9,465
11 07/02/93 $680 $10,760
12 07/09/93 $785 $13,000
13 07/16/93 $750 $11,890
14 07/23/93 $855 $12,980
15 07/30/93 $820 $13,068
16 08/06/93 $890 $14,246
@PERCENTILE - Percentile
Syntax
@PERCENTILE(Array, X)
Array A numeric array or cells of values.
X A percentile value between 0 and 1, inclusive.
@PERCENTILE returns a number from Array at the percentile indicated by X.
Examples
@PERCENTILE({4,5,7,9,10,12,13,16},0) = 4
@PERCENTILE({4,5,7,9,10,12,13,16},0.25) = 6.5
@PERCENTILE({4,5,7,9,10,12,13,16},0.50) = 9.5
@PERCENTILE({4,5,7,9,10,12,13,16},0.75) = 12.25
@PERCENTILE({4,5,7,9,10,12,13,16},1) = 16
The examples above return values from percentile increments of 0.25, which are equal to quartiles. See @QUARTILE.
@PERCENTRANK - Percentage Rank
Syntax
@PERCENTRANK(Array, X, <Digits>)
Array A numeric array or cells of values.
X Number to rank in Array; if X does not match a value in Array, @PERCENTRANK interpolates to return a percentage rank.
Digits Number of significant digits for returned percentage value; must be │ 1 (the default is 3).
@PERCENTRANK returns the percentage rank of X in Array. Use @PERCENTRANK to see where a value stands within a list of values based on a percentage.
Example
This example refers to cells in the next figure. This formula returns the rank of a student's score among the scores of all test takers in the cells A2..A11, where the student's score is in A4:
@PERCENTRANK(A2..A11,A4,3) = 0.222
@PERCENTRANK sample data
A
1 Test Scores
2 78
3 80
4 85
5 85
6 86
7 87
8 91
9 92
10 95
11 98
@PERMUT - Permutations
Syntax
@PERMUT(N, R)
N Number of different objects; n │ 0.
R Number of objects taken at a time; R ú N.
@PERMUT returns the total number of arrangements of objects taken R at a time from a set of N objects. The formula @PERMUT uses is:
@PERMUT is similar to @COMB except that it takes into account the order that objects are selected.
Example
Given 11 different colored marbles, this formula calculates how many different ways an ordered subset of five marbles can be constructed such that no two constructions contain the same five marbles in the same order. (Different constructions can contain the same five marbles, but they cannot share the same ordering.)
@PERMUT(11,5) = 55,440
@PI
Syntax
@PI
@PI returns the value of pi (3.141592653589794...), the classic ratio of a circle's circumference to its diameter.
To figure the area of a circle, given the radius in cell A1, enter this formula:
@PI*A1^2
Examples
@PI*13 = 40.84 (circumference of circle with a diameter of 13)
@PI*(7.5)^2 = 176.7146 (area of circle with a radius of 7.5)
@PI*B3 = the circumference of a circle whose diameter is in B3
Odd|Periods Delay between initial and first cash flow, in number of periods (the default is 1) or cells containing lengths of periods between cash flows (the default is 1).
Simp Flag specifying how to discount:
0 = compounded discounting (default)
1 = mixed compounded and simple discounting
2 = simple discounting
Pathdep Flag specifying whether to apply path-dependent compounding to each flow; 0 = no path (default); 1 = path.
Guess IRR guess for numerical search (useful for locating multiple roots); must be > -100%; the default is 0.10.
Precision Minimum required precision; Precision > 0; the default is 0.000001.
Maxiter Maximum number of iterations for search; Maxiter > 0; the default is 50.
Start A starting cash flow amount to compare against individual flows.
End An ending cash flow amount to compare against individual flows.
@PIRATE computes the internal rate of return for a stream of cash flows. It is similar to @IRR, but @PIRATE accommodates more complex cash flow structures.
The initial Guess for the discount rate is 10%. For some cash flow streams, particularly those with both positive and negative flows, multiple solutions are possible. By specifying a different Guess, it is possible to locate other solutions. If no solution exists, @PIRATE returns ERR.
The default value of Precision is 0.000001; smaller values need more search iterations and may require a larger value for Maxiter, which specifies the maximum number of iterations to use when attempting to find a solution. If the net present value of the cash flows does not converge within Precision to the target value specified by Npv (within Maxiter iterations), @PIRATE returns ERR.
Example
In the next figure, the stream consists of four flows, specified in cells A12..A15. The time lengths of the periods preceding each flow are specified in cells C12..C15. The Npv is $98.34. This formula calculates the internal rate of return, assuming compounded interest:
@PIRATE(B17,A12..A15,0,C12..C15) = 0.050041
A B C
11 Cash Flows Periods
12 $4.50 0.3455
13 $4.50 1.2
14 $4.50 1
15 $104.50 1.5
16
17 npv $98.34
@PMT - Amortized Payment
Syntax
@PMT(Pv, Rate, Nper)
Pv A numeric value representing the amount borrowed (the principal).
Rate A numeric value > -1, representing the periodic interest rate (the fixed interest rate per compounding period).
Nper A numeric value > 0, representing the number of periods of the loan (the number of payments to be made) or investment (the number of compounding periods).
@PMT calculates the fully amortized periodic payment needed to repay a loan with a principal of Pv dollars at Rate percent per period over Nper periods. It assumes that interest is paid at the end of each period and the investment is an ordinary annuity (not an annuity due).
@PMT uses this formula:
where
P principal
R periodic interest rate
N number of periods
An equivalent for this formula using @PAYMT is
@PAYMT(Rate, Nper, - Pv, 0)
You can enter the value for Rate as a percent or a decimal; for example, 9.5% or .095. The amount you specify for Rate must correlate with the unit used for Nper. In other words, if payments are made and interest calculated annually, the amount entered for Nper must represent years. If monthly, Nper must represent the number of months the loan covers. To calculate monthly payments using an annual interest rate, divide the interest rate by 12.
@PMT assumes that the investment is an ordinary annuity. Related @functions @PAYMT, @IPAYMT, and @PPAYMT let you use an optional argument, Type, to indicate whether the investment is an ordinary annuity or an annuity due. @PMTC calculates payments based on semi-annual compounding.
Examples
To calculate a monthly payment (paid on the last day of the month) for a three-year loan of $10,000 at an annual 15% interest rate, enter
@PMT(10000,15%/12,3*12) = $346.65
You can also use @PAYMT to figure this payment (the negative result means the money is out of your pocket):
PAYMT(15%/12,3*12,10000,0,0) = $-346.65
Other examples:
@PMT(1000,0.12,5) = $277.41
@PMT(500,0.16,12) = $96.21
@PMT(5000,16%/12,12) = $453.65
@PMT(12000,0.11,15) = $1,668.78
@PMT(10000,15%/12,36) calculates a monthly payment for a three-year loan of $10,000 at an annual 15% interest rate
@PMTC - Monthly Loan Payment
Syntax
@PMTC(Pv,Rate,Nper)
Pv A numeric value representing the amount borrowed (the principal).
Rate A numeric value > -1, representing the yearly interest rate (the fixed interest rate per compounding period).
Nper A numeric value > 0, representing the number of months of the loan (the number of payments to be made).
@PMTC calculates the monthly loan payments according to Canadian mortgage conventions.
@PMTC uses this formula:
P*((R/12) / (1-(1+(R/12))^-(N*12)))
where
P principal
R yearly interest rate
N number of months
Example
@PMTC(10000,15%,36) = 344.46
@POISSON - Poisson Probability Distribution
Syntax
@POISSON(N, Mean, Cum)
N Number of events; must be │ 0.
Mean Expected numeric value for the mean over the distribution; must be > 0.
Cum 1 to return the cumulative Poisson probability distribution that the number of random events will be in the range from zero to N; 0 to return the Poisson probability mass function that the number of events will be N.
@POISSON returns the Poisson probability distribution, that is, the probability that N number of events will occur over a specified time period. The Poisson distribution function uses this formula:
with
r │ 0
Example
On average, Company Z receives 30 customer service phone calls per hour. What is the probability that Company Z will receive 35 calls in one hour?
@POISSON(35,30,0) = 0.045308
@POWER - Number Raised to a Power
Syntax
@POWER(Num, Power)
Num Number (base) to be raised to a power; can be any real number.
Power Power (exponent), to which Num is to be raised.
@POWER calculates the result of a specified number raised to a power.
Examples
@POWER(4,3) = 64
@POWER(3.14159,2) = 9.8695877
@POWER(2,1/2) = 1.4142136
@PPAYMT - Principal Portion of Payment
Syntax
@PPAYMT(Rate, Per, Nper, Pv, <Fv>, <Type>)
Rate A numeric value > -1, representing the periodic interest rate (the fixed interest rate per compounding period).
Per The number of the loan period for which the principal is desired (where Nper is the total number of periods).
Nper A numeric value > 0, representing the number of periods of the loan (the number of payments to be made) or investment (the number of compounding periods).
Pv A numeric value representing the amount borrowed (the principal).
Fv A numeric value representing the future value of an investment (the value the investment will reach at some point).
Type An optional numeric value that indicates whether payments or cash flows occur at the beginning (1) or the end (0) of the period; default = 0.
@PPAYMT calculates the amount of a particular payment that is going toward the loan principal or investment Pv and is not interest.
@IPAYMT gives the part of the payment which is interest; @PAYMT calculates the total payment for each period.
Examples
Assume you are two years into a 30-year, 10% mortgage on a $100,000 loan. To determine what portion of this month's payment is principal, enter
@PPAYMT(.1/12,2*12,30*12,100000) = $-53.54
The negative result indicates the money is out of your pocket.
Another example:
@PPAYMT (.15/4,24,40,10000,0,1) = $-250.83 quarterly payments for a $10,000 loan at 15% annual percentage rate adjusted to a quarterly basis over a 10-year term
Settle Number representing the settlement date; must be < Maturity.
Maturity Number representing the maturity date; must be > Settle.
Coupon Coupon rate; must be │ 0.
Yield Annual yield; must be > 0 and ú 1.
Redemption Redemption value per 100 face value; must be > 0; the default is 100.
Freq Frequency of coupon payments in number of payments per year (can be 1, 2, 3, 4, 6, or 12; the default is 2).
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@PRICE returns the price per 100 face value of a security that pays periodic interest.
Example
An 8.85% bond that matures August 17, 2017 has a yield-to-maturity of 7.5% for September 22, 1993 settlement. This formula calculates the price of the bond:
Settle Number representing the settlement date; must be < Maturity.
Maturity Number representing the maturity date; must be > Settle.
Discount Rate of discount; 0 ú Discount ú 1.
Redemption Redemption value per 100 face value (must be > 0; the default is 100).
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@PRICEDISC returns the dollar price per 100 face value of a discounted security. The dollar price of a bill is its value less the applicable dollar discount. The discount is the product of the redemption value and the quoted discount rate, prorated for the number of days between settlement and maturity. @PRICEDISC uses this formula:
P price
R redemption
D discount
M maturity
S settle
b basis
tb is the number of days over which the discount rate applies (360 or 365).
Example
This formula calculates the dollar price of a bill with the following terms: Settle is January 17, 1993, Maturity is August 15, 1993, Discount is 8.897%, Redemption is 100, and Calendar is 2 (actual/360).
Settle Number representing the settlement date; must be < Maturity.
Maturity Number representing the maturity date; must be > Settle.
Issue Number representing the issue date; must be < Settle.
Coupon Coupon rate; 0 ú Coupon ú 1.
Yield Annual yield; 0 ú Yield ú 1.
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@PRICEMAT returns the price per 100 face value (not including accrued interest) of a security that pays interest at maturity. The invoice price of a security that pays interest at maturity is the sum of the quoted price and accrued interest between issue and settlement dates.
Example
This formula calculates the price per 100 face value of a security with the following terms: Settle is February 1, 1993, Maturity is April 1, 1993, Issue is January 2, 1993, Coupon is 10%, Yield is 10%, and Calendar is 2 (actual/360).
Xdata Values of X associated with the probabilities.
ProbRange Cells or an array of probability values associated with XData; each value in ProbRange must be │ 0 and ú 1; the sum of ProbRange values must equal 1.
LowerLimit Lower limit on the value for the desired probability.
UpperLimit Upper limit on the value for the desired probability. (optional) (default = lower limit)
@PROB determines the probability that XData values are between two limits. If XData and ProbRange do not have the same number of values, @PROB returns ERR.
@PROPER converts the first letter of every word in String to uppercase, and the rest of the characters to lowercase. A word is defined as an unbroken string of alphabetic characters. Any blank spaces, punctuation symbols, or numbers mark the end of a word.
Examples
@PROPER("GEORGE washINGTON") = George Washington
@PROPER("FIRST QUARTER") = First Quarter
@PROPER("JOHN J. SMITH") = John J. Smith
@PROPER("1979's results") = 1979'S Results
@PROPER(A1) = John J. Smith (where cell A1 contains JOHN J. SMITH)
@PROPERTY - Current Property Setting
Syntax
@PROPERTY(Object.Property)
Object The name of the object whose property settings you are requesting.
Property The property whose settings you are requesting.
Returns the current setting of Property for an Object. See Property Reference for lists of objects and properties you can enter as arguments.
@PROPERTY returns a string, even if the setting is a number. Object.Property must be enclosed in double quotes.
Examples
@PROPERTY("Active_Block.Selection")
Returns: the coordinates of the currently selected cells.
@PROPERTY("Sales:A1..D12.Protection")
Returns: Protect if cells A1..D12 on sheet Sales is protected; otherwise, it returns Unprotect.
@PUREAVG - Average, Ignoring Labels and Blanks
Syntax
@PUREAVG(List)
List One or more numeric or cell values.
@PUREAVG calculates the average of values in a list, ignoring blank cells and labels. Compare this to @AVG, which calculates the average of all values in a list.
@Functions that ignore blank cells and labels are extremely important when using a spreadsheet for statistical analysis.
Examples
A
1 Sales
2 $80,000
3 $90,000
4 $95,000
5 $105,000
@PUREAVG(A1..A5) = $92,500
@AVG(A1..A5) = $74,000, because @AVG includes the column heading and divides the total by 5
@AVG(A2..A5) = $92,500, when only the 4 cells containing numbers are in the list
List One or more numeric or string values, cell addresses, and cell references or names, separated by commas.
@PURECOUNT returns the number of entries and cells in a list, excluding blank cells and labels. Compare this to @COUNT, which returns the number of nonblank cells in a list. @COUNT includes cells with entries of any kind, including labels, a label-prefix character, or the values ERR and NA.
You can use both @PURECOUNT and @COUNT to divert or stop a macro that performs a task on a series of selections when the cell pointer reaches an empty cell.
@Functions that ignore blank cells and labels are extremely important when using a spreadsheet for statistical analysis.
Examples
A
1 Sales
2 $80,000
3 $90,000
4 $95,000
5 $105,000
@PURECOUNT(A1..A6) = 4
@COUNT(A1..A6) = 5 (includes the label)
@PURECOUNT(1,"hello",A1..A3) = 3
@COUNT(1,"hello",A1..A3) = 5
@PUREMAX - Maximum Value, Ignoring Labels and Blanks
Syntax
@PUREMAX(List)
List One or more numeric or string values, cell addresses, and cell references or names, separated by commas.
@PUREMAX returns the largest numeric value in a list, ignoring blank cells and labels. Compare this to @MAX, which returns the largest numeric or data value in a list.
@Functions that ignore blank cells and labels are extremely important when using a spreadsheet for statistical analysis.
Examples
A
1 Expenses
2 ($80)
3 ($90)
4 ($95)
5 ($105)
@PUREMAX(A1..A6) = ($80)
@MAX(A1..A6) = 0, the value of the label, Expenses
@PUREMIN - Minimum Value, Ignoring Labels and Blanks
Syntax
@PUREMIN(List)
List One or more numeric or string values, cell addresses, and cell references or names, separated by commas.
@PUREMIN returns the smallest numeric value in a list, ignoring blank cells and labels. Compare this to @MIN, which returns the smallest numeric or data value in a list.
@Functions that ignore blank cells and labels are extremely important when using a spreadsheet for statistical analysis.
Examples
A
1 Sales
2 $80,000
3 $90,000
4 $95,000
5 $105,000
@PUREMIN(A1..A6) = $80,000
@MIN(A1..A6) = 0, the value of the label, Expenses
@PURESTD - Population Standard Deviation, Ignoring Labels and Blanks
Syntax
@PURESTD(List)
List One or more numeric or string values, cell addresses, and cell references or names, separated by commas.
@PURESTD returns the population standard deviation (square root of the population variance) of numeric values in a list, ignoring blank cells and labels. Compare this to @STD, which returns the population standard deviation of all values in a list. @PURESTDS and @STDS return sample standard deviation.
@STD and @PURESTD use the n method to calculate standard deviation of population data. This method assumes that the sample reflects the entire population. If the sample is small, the standard deviation is biased because of sampling errors, so @STDS or @PURESTDS should be used instead.
@Functions that ignore blank cells and labels are extremely important when using a spreadsheet for statistical analysis.
Examples
A
1 Grades
2 4.0
3 3.4
4 3.7
5 3.6
@PURESTD(A1..A6) = 0.216506, ignoring the label and the blank
@STD(A1..A6) = 1.4827, because the argument includes the label, which to @STD = 0
@STD(A2..A6) = 0.216506, excluding the label from the argument
@PURESTDS(A1..A6) = 0.25, the sample standard deviation
@PURESTDS - Sample Standard Deviation, Ignoring Labels and Blanks
Syntax
@PURESTDS(List)
List One or more numeric or string values, cell addresses, and cell references or names, separated by commas.
@PURESTDS returns the sample standard deviation (square root of the sample variance) of numeric values in a list, ignoring blank cells and labels. Compare this to @STDS, which returns the sample standard deviation of all values in a list. @PURESTD and @STD return population standard deviation.
@STDS and @PURESTDS use the n-1 method to calculate standard deviation of sample population data. This method compensates for sampling errors, returning a slightly larger standard deviation. If the sample is large enough, @STD or @PURESTD can be used.
Examples
A
1 Grades
2 4.0
3 3.4
4 3.7
5 3.6
@PURESTDS(A1..A6) = 0.25, ignoring the label and the blank
@STDS(A1..A6) = 1.657709, because the argument includes the label, which to @STDS = 0
@STDS(A2..A6) = 0.25, excluding the label from the argument
@PURESTD(A1..A6) = 0.216506, the population standard deviation
@PUREVAR - Population Variance, Ignoring Labels and Blanks
Syntax
@PUREVAR(List)
List One or more numeric or string values, cell addresses, and cell references or names, separated by commas.
@PUREVAR calculates the population variance of numeric values in a list, ignoring blank cells and labels. Compare this to @VAR, which calculates the population variance of all values in a list. @PUREVARS and @VARS calculate sample population variance.
Variance @functions perform a statistical test called analysis of variance (anova). @VAR and @PUREVAR use the n method to calculate variance. This method assumes that the sample reflects the entire population. If the sample is small, the variance is biased because of sampling errors, so @VARS and @PUREVARS should be used instead.
@Functions that ignore blank cells and labels are extremely important when using a spreadsheet for statistical analysis.
Examples
A
1 Grades
2 4.0
3 3.4
4 3.7
5 3.6
@PUREVAR(A1..A6) = 0.046875, ignoring the label and the blank
@VAR(A1..A6) = 2.1984, because the argument includes the label, which to @VAR = 0
@VAR(A2..A6) = 0.046875, excluding the label from the argument
@PUREVARS(A1..A6) = 0.0625, the sample population variance
@PUREVARS - Sample Population Variance, Ignoring Labels and Blanks
Syntax
@PUREVARS(List)
List One or more numeric or string values, cell addresses, and cell references or names, separated by commas.
@PUREVARS calculates the sample population variance of numeric values in a list, ignoring blank cells and labels. Compare this to @VARS, which calculates the sample population variance of all values in a list. @PUREVAR and @VAR calculate population variance.
Variance @functions perform a statistical test called analysis of variance (anova). @VARS and @PUREVARS use the n-1 method to calculate variance. This method compensates for sampling errors, returning a slightly larger variance. If the sample is large enough, @VAR or @PUREVAR can be used.
@Functions that ignore blank cells and labels are extremely important when using a spreadsheet for statistical analysis.
Examples
A
1 Grades
2 4.0
3 3.4
4 3.7
5 3.6
@PUREVARS(A1..A6) = 0.0625, ignoring the label and the blank
@VARS(A1..A6) = 2.748, because the argument includes the label, which to @VARS = 0
@VARS(A2..A6) = 0.0625, excluding the label from the argument
@PUREVAR(A1..A6) = 0.046875, the population variance
@PV - Present Value
Syntax
@PV(Pmt, Rate, Nper)
Pmt A numeric value representing the amount of the periodic payment.
Rate A numeric value > -1, representing the periodic interest rate (the fixed interest rate per compounding period).
Nper A numeric value > 0, representing the number of periods of the loan (the number of payments to be made) or investment (the number of compounding periods).
@PV calculates the present value of an investment where Pmt is received for Nper periods and is discounted at the rate of Rate per period. Present value is calculated using this formula:
where
P amount of periodic payment
R periodic interest rate
N number of periods
An equivalent for this formula using @PVAL is
@PVAL(Rate, Nper, - Pmt, 0)
@PV assumes that the investment is an ordinary annuity. Related @function @PVAL lets you use an optional argument, Type, to indicate whether the investment is an ordinary annuity or an annuity due.
Examples
Assume you want to buy a new van that costs $12,000. The dealer presents two offers: Pay $12,000 cash up front, or pay $350 per month for the next five years with 7% interest. The present value of the loan is
@PV(350,7%/12,5*12) = $17,675.70
The loan is worth over $5000 more than paying the cost all at once.
You can also use @PVAL. The car loan example becomes
@PVAL(7%/12,5*12,-350,0,0) = $17,675.70
Other examples:
@PV(277,0.12,5) = $998.52
@PV(600,0.17,10) = $2,795.16
@PV(100,0.11,12) = $649.24
@PVAL - Present Value
Syntax
@PVAL(Rate, Nper, Pmt, <Fv>, <Type>)
Rate A numeric value > -1, representing the periodic interest rate (the fixed interest rate per compounding period).
Nper A numeric value > 0, representing the number of periods of the loan (the number of payments to be made) or investment (the number of compounding periods).
Pmt A numeric value representing the amount of the periodic payment.
Fv A numeric value representing the future value of an investment (the value the investment will reach at some point).
Type An optional numeric value that indicates whether payments or cash flows occur at the beginning (1) or the end (0) of the period; default = 0.
@PVAL calculates the present value of an investment where Pmt is received for Nper periods and is discounted at the rate of Rate per period.
Enter negative numbers for money that is out of your pocket and positive numbers for money coming in to you. The last two arguments, Fv and Type, are optional. If you omit the last one or both of them, Quattro Pro assumes their values are zero.
This @function is not compatible with 1-2-3. If your file must be compatible, use the related @function @PV instead.
Examples
Your grandfather leaves you $24,000 in cash over the next 12 years ($2000 a year) or you can have all his government bonds, which mature in 15 years to a worth of $30,000. To determine which is worth more, compute the present value of the $24,000. Assume you can invest the money as you accumulate it in a 10% money market account.
@PVAL(10%,12,2000,0,0) = -13,627.38
The result is negative because the money you invest is considered an outgoing cash flow. Now compare this figure with the present value of the $30,000, which you will not receive for 15 years:
@PVAL(10%,15,0,30000,0) = -7,181.76
These results tell you that the $24,000 spread over 12 years is the more valuable choice.
