Cell A single cell address that contains another cell address or cell name that is written as a label.
@@ is used to reference a cell that contains another cell address or cell name that is written as a label. @@ translates the label into a cell or single-cell reference and returns the contents of that cell. @@ does not accept a cell name for a selection that is not a single-cell.
Examples
@@("A15") = the contents of A15
@@("BLOCK_NAME") = the contents of the single-cell named BLOCK_NAME
@@(A3) = 50 if A3 contains the label 'A1 and cell A1 contains the value 50
@@(A3) = the label 'Total if A3 contains the label 'Block, which is the name of cell C9, which contains the label 'Total
@@(A1) = ERR, where A1 contains the label 'B1..B5
@@(A1) = B1, where A1 contains the label 'B1
@@("A1") = B1..B5, where A1 contains the label 'B1..B5
@@("[NOTEBK1]A1") = B1..B5, where A1 in the current sheet of NOTEBK1 contains the label 'B1..B5 and NOTEBK1 is open
@SUM(@@(A1)) = the sum of the values in B1..B5, where A1 contains the label 'B1..B5, because Quattro Pro translates the label into cell coordinates for a non-single cell selection
@@("B1..B5") = ERR (not a single-cell selection)
@SUM(@@("B1..B5")) = 5 if cells B1 through B5 each contain 1
Date Number representing the date to which a number of business days should be added. See "Using dates and times in Quattro Pro."
Days Integer representing number of business days to add; can be negative.
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).
@ABDAYS adds or subtracts Days business days from Date and returns a serial date number.
If Date falls on a weekend or a holiday, one business day out of Days is used to bring Date forward to the next business day; if Days is negative, Date is taken backward to the previous business day. For example, if 20 business days are added to June 5, 1993, the result is the same as adding 19 business days to June 7, 1993 since June 5 falls on a Saturday.
Example
This formula calculates the date that precedes January 12, 1994 by 90 business days, assuming that Saturday, Sunday, and the dates in the cells A7..C9 are holidays:
FirstCpn Number representing the first coupon date.
Par Par value (the default is 1000).
Freq Frequency of coupon payments in the 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 = US (NASD) 30/360; default
1 = Actual/actual
2 = Actual/360
3 = Actual/365
4 = European 30/360
@ACCRINT returns the accrued interest for a bond. Accrued interest represents an amount paid to the bond seller as compensation for owning the bond for a fraction of a coupon period. Interest accrues from the last coupon date to the settlement date. @ACCRINT returns the accrued interest per 1000 face value.
Dates for @ACCRINT must follow this pattern:
Issue < Settle < FirstCpn < Maturity
Example
This formula returns the accrued interest, as of May 15, 1993, on an 8.875% bond with a $100,000 face value, maturing February 15, 1998, dated November 22, 1992, and paying its first coupon on August 15, 1993:
Issue Number representing the issue date. (Issue, FirstInt, Settle, Freq, and Basis are truncated to integers.) The Issue value must be < FirstCpn and <Settle.
FirstCpn Number representing the first interest date
Settle Number representing the settlement date.
Coupon Interest rate; 0 ú Coupon.
Par Par value (the default is 1000).
Freq Number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
Calendar Flag specifying which calendar to observe:
0 = US (NASD) 30/360; default
1 = Actual/actual
2 = Actual/360
3 = Actual/365
4 = European 30/360
@ACCRINTXL returns the accrued interest for a security that pays periodic interest.
@ACCRINTXL uses the formula
where
Ia accrued interest
par par value
r coupon rate
f frequency of coupon payments
NC number of quasi-coupon periods that fit in odd period, rounded to next integer
Nli normal in days of the ith quasi-coupon period within the odd period
Ai number of accrued days for ith quasi-coupon period within the odd period
When you use any optional argument, you must also use the ones before it.
Example
This formula returns the accrued interest, as of May 15, 1996, on an 8.875% bond with a $10,000 par value, issued November 22, 1995 and paying its first interest on August 15, 1996. The US 30/360-day year is used and coupon payments are twice a year.
Issue Number representing the issue date; must be < Settle.
Settle Number representing the settlement date.
Coupon Coupon rate; 0 ú Coupon ú 1.
Par Par value (the default is 1000).
Calendar Flag specifying which calendar to observe:
0 = US (NASD) 30/360; default
1 = Actual/actual
2 = Actual/360
3 = Actual/365
4 = European 30/360
@ACCRINTM calculates the amount of interest accrued per par value between the issue date of the coupon and the settle date.
Example
This formula returns the accrued interest on a certificate of deposit (CD) with $1,000,000 face value settling March 11, 1990, dated December 15, 1989, and paying a coupon of 10% on an actual/360 basis:
Settle Number representing the settlement date; must be greater than Issue.
Issue Number representing the issue date.
FirstInt Number representing the first interest date; must be greater than Issue.
Coupon Coupon rate; can be any positive value, including 0.
Par Par value, or the principal to be paid at maturity (optional); the default is 100.
Freq Frequency of coupon payments (optional) in number of payments per year; can be 1, 2, 4, or 12; the default is 2.
Calendar Flag specifying which calendar to observe:
0 = US (NASD) 30/360; default
1 = Actual/actual
2 = Actual/360
3 = Actual/365
4 = European 30/360
@ACCRUED calculates the accrued interest for a security with periodic interest payments. Short, standard, and long coupon periods can also be used.
@ACCRUED uses the formula
where
Ia accrued interest
par par value
r coupon rate
f frequency of coupon payments
NC number of quasi-coupon periods that fit in odd period, rounded to next integer
Nli normal in days of the ith quasi-coupon period within the odd period
Ai number of accrued days for ith quasi-coupon period within the odd period
When you use any optional argument, you must also use the ones before it.
Example
This formula returns the accrued interest, as of May 15, 1996, on an 8.875% bond with a $10,000 par value, issued November 22, 1995 and paying its first interest on August 15, 1996. The US 30/360-day year is used and coupon payments are twice a year.
Date Number representing the date to add days to. See "Using dates and times in Quattro Pro."
Days Integer representing number of days to add; can be negative.
Calendar Flag specifying which calendar to observe:
0 = US (NASD) 30/360; default
1 = Actual/actual
2 = Actual/360
3 = Actual/365
4 = European 30/360
EndMnth 1 to indicate adherence to ends of months; 0 to indicate that ends of months are ignored (the default is 1).
@ACDAYS adds Days days to Date using an actual or 30/360 calendar and returns a serial date number. If Days is negative, @ACDAYS subtracts the absolute value of Days from Date.
With the 30/360 calendar, if the ending month is February and the remaining days push the result to the 29th or 30th, the result is forced to the true end of the month (28th or 29th, depending on whether Date is in a leap year).
More than one result is possible when using the 30/360 calendar. For example, adding 90 days to April 30 can yield either July 30 or July 31.
@ACOS returns the arc cosine of X. The result is the angle (in radians) whose cosine is X. To convert radians to degrees, use @DEGREES.
Examples
@ACOS(1) = 0
@ACOS(0.5) = 1.047198
@DEGREES(@ACOS(0.5)) = 60
@ACOS(@ABS(B10)) = the arc cosine of the absolute value of B10
@ACOS(2) = ERR (means that X is greater than 1)
@ACOSH - Arc Hyperbolic Cosine
Syntax
@ACOSH(X)
X The hyperbolic cosine of an angle. X must be greater than or equal to 1 but less than approximately 1.34078E+154.
@ACOSH returns the arc, or inverse, hyperbolic cosine of a number. The arc hyperbolic cosine is the value whose hyperbolic cosine is X, so @ACOSH(@COSH(X)) = X.
@ACOSH returns the result in radians; to convert to degrees, use @DEGREES.
Examples
@ACOSH(1) = 0
@ACOSH(2) = 1.316958
@ROUND(@DEGREES(@ACOSH(1.600287)), 2) = 60 degrees. Or: "The angle whose hyperbolic cosine is 1.600287, rounded to 2 decimal places."
@ACOSH(@ABS(D33)) = the arc hyperbolic cosine of the absolute value of D33
@ACOSH(0.5) = ERR (means that X is less than 1)
@ACOT - Arc Cotangent
Syntax
@ACOT(X)
X The cotangent of an angle. X can be any value from approximately -1.789E+308 through 1.789E+308.
@ACOT calculates the arc, or inverse, cotangent using the cotangent X of an angle. The result of @ACOT is an angle, in radians, from 0 through p. This represents an angle between 0 and 180 degrees. To convert radians to degrees, use @DEGREES.
Examples
@ACOT(0.5) = 1.107149
@ACOT(1) = 0.785398
@ROUND(@DEGREES(@ACOT(1)),2) = 45
@ACOTH - Arc Hyperbolic Cotangent
Syntax
@ACOTH(X)
X The hyperbolic cotangent of an angle. X can be any value between approximately -1.79E+308 and less than -1 and between greater than 1 and approximately 1.79E+308.
@ACOTH calculates the arc, or inverse, hyperbolic cotangent using the hyperbolic cotangent X of an angle. The result is in radians; to convert to degrees, use @DEGREES.
Examples
@ACOTH(4) = 0.255413
@ACOTH(@PI/4) = ERR, because p /4 is between -1 and 1
@ACOTH(@PI/3) = 1.884943
@ROUND(@DEGREES(@ACOTH(1.524869)), 2) = 45 degrees. Or: "The angle whose hyperbolic cotangent is 1.524869, rounded to 2 decimal places."
@ACSC - Arc Cosecant
Syntax
@ACSC(X)
X The cosecant of an angle. X can be any value between approximately -1.79E+308 and -1 and between 1 and approximately 1.79E+308.
@ACSC calculates the arc, or inverse, cosecant using the cosecant X of an angle. The result of @ACSC is an angle, in radians, from - p/2 through p/2 (from -90 through 90 degrees). To convert radians to degrees, use @DEGREES.
Examples
@ACSC(1) = 1.570796
@ACSC(-2) = - 0.5236
@ROUND(@DEGREES(@ACSC(-2)),2) = - 30 degrees
@ACSC(0.25) = ERR, because X is between -1 and 1
@ACSCH - Arc Hyperbolic Cosecant
Syntax
@ACSCH(X)
X The hyperbolic cosecant of an angle. X can be any value between approximately -1.34078E+154 and 1.34078E+154, but not 0.
@ACSCH calculates the arc, or inverse, hyperbolic cosecant using the hyperbolic cosecant X of an angle. The result is in radians; to convert to degrees, use @DEGREES.
Examples
@ACSCH(@RADIANS(30)) = 1.402581
@ACSCH(@RADIANS(75)) = 0.704265
@ROUND(@DEGREES(@ACSCH(1.825306)), 2) = 30 degrees. Or: "The angle whose hyperbolic cosecant is 1.825306, rounded to 2 decimal places."
@ADDB - Add Binary Numbers
Syntax
@ADDB(Binary1, <Binary2>, <BitIn>, <Bits>)
Binary1 First binary number.
Binary2 Second binary number.
BitIn Input carry bit; can be either 0 (the default) or 1.
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.
@ADDB returns the sum of two binary numbers. If Binary2 is omitted, @ADDB counts the bits in Binary1 that are set to 1; this bit counting operation is called addition reduction.
Use two's complement notation (see Quattro Pro glossary) to represent negative numbers. If BitIn is 1, @ADDB adds one extra bit to the result.
Example
@ADDB(100,100) = 1000
@ADDB(100,100,1,4) = 1001
@ADDB(101) = 2
@ADDB(1100,1,1,5) = 01110
@ADDBO - Overflow of Binary Addition
Syntax
@ADDBO(Binary1, Binary2, <BitIn>, <Bits>)
Binary1 First binary number.
Binary2 Second binary number.
BitIn Input carry bit; can be either 0 (the default) or 1.
Bits Number of binary bits used for input; if omitted, Bits = number of bits in Binary1 or Binary2, whichever is greater; must be in the range 0 <n ú 64.
@ADDBO returns the overflow bit (either 0 or 1) of the sum of two binary numbers. An overflow occurs when a bit is carried out of the word size specified by Bits. For example, if Binary1 = 10 and Binary2 = 10, the sum of the two numbers is 00, with 1 carry bit in the third place not shown.
Use two's complement notation (see Quattro Pro glossary) to represent negative numbers. If BitIn is 1, @ADDBO adds one extra bit to the sum of the two numbers before returning the overflow.
Example
@ADDBO(1000,111) = 0
@ADDBO(1000,111,1) = 1
@ADDBO(1100,100,1,4) = 1
@ADDH - Add Hexadecimal Numbers
Syntax
@ADDH(Hex1, <Hex2>, <BitIn>, <Bits>)
Hex1 First hexadecimal number.
Hex2 Second hexadecimal number.
BitIn Input carry bit; can be either 0 (the default) or 1.
Bits Number of binary bits used for both input and output; if omitted, Bits = number of bits in Hex1 or Hex2, whichever is greater; must be in the range 0 <n ú 64.
@ADDH returns the sum of two hexadecimal numbers. If Hex2 is omitted, @ADDH counts the bits in Hex1 that are set to 1; this bit counting operation is called addition reduction.
Use two's complement notation (see Quattro Pro glossary) to represent negative numbers. If BitIn is 1, @ADDH adds one extra bit to the result.
Example
@ADDH("E00","100") = F00
@ADDH("100","100",1,16) = 0201
@ADDH("9") = 2
@ADDH("C","1",1,8) = 0E
@ADDHO - Overflow of Hexadecimal Addition
Syntax
@ADDHO(Hex1, Hex2, <BitIn>, <Bits>)
Hex1 First hexadecimal number.
Hex2 Second hexadecimal number.
BitIn Input carry bit; can be either 0 (the default) or 1.
Bits Number of binary bits used for both input and output; if omitted, Bits = number of bits in Hex1 or Hex2, whichever is greater; must be in the range 0 <n ú 64.
@ADDHO returns the overflow bit (either 0 or 1) of the sum of two hexadecimal numbers. An overflow occurs when a bit is carried out of the word size specified by Bits. For example, if the binary equivalents for Hex1 and Hex2 are 10 and 10, the sum of the two numbers is 00 with 1 carry bit in the third place not shown.
Use two's complement notation (see Quattro Pro glossary) to represent negative numbers. If BitIn is 1, @ADDHO adds one extra bit to the sum of the two numbers before returning the overflow.
RowNum Row number of the cell for which you want the reference.
ColNum Column number.
RefType Type of cell reference to return (optional).
Format Logical value indicating A1 or R1C1 reference style (optional):
TRUE = A1 (default, if omitted)
FALSE = R1C1
Page Name of notebook sheet (optional).
@ADDRESS returns, as text, a cell reference for which you specify row and column numbers. Optionally, you can specify another sheet and choose reference type and style.
RefType Format Meaning
1 $A:$A$1 All absolute
2 $A:A$1 Absolute sheet and row, relative column
3 $A:$A1 Absolute sheet and column, relative row
4 $A:A1 Absolute sheet, relative column and row
5 A:$A$1 Relative sheet, absolute column and row
6 A:A$1 Relative sheet and column, absolute row
7 A:$A1 Relative sheet and row, absolute column
8 A:A1 All relative
You can use @ADDRESS with @INDEX, @VLOOKUP, or @HLOOKUP to create cell references from tables of values in the current file. Use @ADDRESS with @@ to return values in cell references.
Examples
@ADDRESS(5,4) = "$D$5" - absolute reference to Cell D5
@ADDRESS(5,4,3) = "$D5" - absolute reference to column D, relative reference to row 5
If B7 contains 7, and C7 contains 6, @@(@ADDRESS(B7,C7)) returns the value in cell F7.
@AMAINT - Amortized Accumulated Interest
Syntax
@AMAINT(Principal, Int, Term, n, <Part>, <Residual>, <ResOff>, <Adv>, <Odd>, <Simp>)
Principal Initial loan principal.
Int Periodic interest rate.
Term Term of the loan, expressed in number of total payments.
n Number of payments made; must be an integer from 0 to Term.
Part Part of (n+1)th period passed (must be from 0 to 1; the default is 0).
Residual Remaining balance on loan at end of loan term (the default is 0).
ResOff Number of periods after last periodic payment that residual is to be paid; can have fractional component (the default is 0).
Adv Number of advance payments made at loan inception (the default is 0); n - Adv must be an integer.
Odd Number of periods between loan inception and date of first payment (not including advance payments); can have fractional component (the default is 1).
Simp 0 to specify compounded interest or 1 to specify simple interest (the default is 0).
@AMAINT calculates the accumulated interest paid on a loan after n payments. The accumulated interest is the sum of the interest portions of the first n payments plus the interest of an optional partial payment (specified by Part).
Term and n should include advance payments made at the beginning of the loan. Part handles payoff situations where the payoff date does not coincide with a periodic payment date. For example, if 20 out of 60 monthly payments of a loan have been made and 10 days have passed since the 20th payment date, Part = 10/31, assuming there are 31 days between the 20th and 21st monthly payments.
If Simp = 0, @AMAINT uses this formula:
If Simp = 1, @AMAINT uses this formula:
where
I interest
Pa payment
Pr principal
B balance
P part
where Payment is the periodic payment and Balance is the remaining balance on the loan after n payments. Balance, like Principal, is the present value of an annuity paying Payment.
Examples
A loan of $10,000 was made on September 11, 1992 to be repaid in 48 monthly installments. The annual interest rate is 8.4% (8.4%/12 = 0.7% monthly rate). The first payment was paid in advance. This formula calculates the amount of paid interest after 15 regular payments:
@AMAINT(10000,0.007,48,15,0,0,0,1) = $840.74
For the same loan, this formula calculates how much interest accumulated on the loan as of March 3, 1993, assuming the borrower made regular payments. The previous payment (the 18th) fell on February 11, 1993; there are 21 days between the previous payment and March 3. Interest accrues as simple interest over fractional periods.
Term Term of loan, expressed in number of total payments.
Payment Periodic payment (for example, if Term is expressed in months, Payment must be a monthly payment).
Residual Remaining balance on loan at end of loan term (the default is 0).
ResOff Number of periods after last periodic payment that Residual is to be paid; can have fractional component (the default is 0).
Adv Number of advance payments made at loan inception (the default is 0).
Odd Number of periods between loan inception and date of first payment (not including advance payments); can have fractional component (the default is 1).
Simp 0 to specify compounded interest or 1 to specify simple interest (the default is 0).
Prec Required precision of result (the default is 0.000001); must be │ 0.
@AMINT calculates the interest rate for one payment of an amortized loan. For example, if the arguments passed correspond to a monthly loan, the interest rate returned represents a monthly rate. Use Prec to specify how close @AMINT must be to the actual interest rate.
Example
A loan for $50,000 was made on March 15, 1993. Repayment terms stipulate ten annual payments of $7,500, each to be made on July 31, beginning 1993 and ending 2002, along with a final payment of $2,500 on December 31, 2003. Assuming interest is compounded during fractional periods, this formula calculates the interest rate at which the financing is performed:
A normal period is one year long, but the first period is 138 days long. Since there are 365 days between March 15, 1993 and March 15, 1994, the first period is 138/365 the length of a normal period (Odd = 0.3781). There is also a delay between the last periodic payment and the residual payment of $2,500. The length of the delay is one period (July 31, 2002 to July 31, 2003) plus 153 days (July 31, 2003 to December 31, 2003), so ResOff = 1.4180 (which equals 1+(153/366)).
@AMNTHS - Add Months
Syntax
@AMNTHS(Date, Months, <EndMnth>)
Date Number representing the date to add number of months to. See "Using dates and times in Quattro Pro."
Months Integer representing number of months to add; can be negative.
EndMnth 1 to indicate adherence to ends of months; 0 to indicate that ends of months are ignored (the default is 1).
@AMNTHS adds the number of months specified by Months to Date and returns a serial date number. If Months is negative, @AMNTHS subtracts the absolute value of Months from Date.
Adding one month usually means going from a day in one month to the same day in the next month. However, adding one month to March 31 cannot result in April 31, since April 31 does not exist. In this case, the last day of the month, April 30, is returned. If Date falls on the 31st of a month, the result also falls on the last day of a month.
If Date falls on the last day of a month with less than 31 days, you can use EndMnth to specify one of two different results. To move ahead to the same day of the specified month, specify EndMnth as 0. To move ahead to the last day of the specified month, omit EndMnth or specify it as 1.
Examples
@AMNTHS(@DATE(93,4,30),3) = 34181 (July 31, 1993), which is the last day of the month three months from April 30, 1993.
Consider a loan with 120 payments that pays on the 30th of each month starting on June 30, 1993. In February, it pays on the last day of the month. This formula calculates the date of the 43rd payment:
Int Periodic interest rate (for example, if Term is expressed in months, Int must be a monthly rate).
Term Term of loan, expressed in number of total payments.
Residual Remaining balance on loan at end of loan term (the default is 0).
ResOff Number of periods after last periodic payment that residual is to be paid; can have fractional component (the default is 0).
Adv Number of advance payments made at loan inception (the default is 0).
Odd Number of periods between loan inception and date of first payment (not including advance payments); can have fractional component (the default is 1).
Simp 0 to specify compounded interest or 1 to specify simple interest (the default is 0).
@AMPMT calculates the payment (monthly, annual, and so on) for an amortized loan.
Examples
A loan for $35,000 has 48 monthly payments and a residual payment of $7,500 that is due three months after the last monthly payment. This formula calculates the monthly payment if the annual interest rate is 9% (9%/12 = 0.75% monthly rate):
@AMPMT(35000,0.0075,48,7500,3) = $743.48
An annuity with an investment of $250,000 makes quarterly payments starting five years from the date of investment for a period of 20 years. It also pays a lump sum of $50,000 three and a half years after the last quarterly payment. If the annualized yield from the investment is 8.4% (8.4%/4 = 2.1% quarterly rate), this formula calculates the quarterly payment:
@AMPMT(250000,0.021,80,50000,14,0,20) = $9,431.83
The term of the annuity is 80 quarters. A value of 14 for ResOff specifies, in quarters, the three-and-a-half year delay between the last quarterly payment and the residual payment. A value of 20 for Odd specifies the five year delay between investment and first payment, in quarters.
@AMPMTI - Amortized Interest Portion of Payment
Syntax
@AMPMTI(Principal, Int, Term, n, <Residual>, <ResOff>, <Adv>, <Odd>, <Simp>)
Principal Initial loan principal.
Int Periodic interest rate (for example, if Term is expressed in months, then Int must be a monthly rate).
Term Term of loan, expressed in number of total payments.
n Number of payments made; must be an integer from 0 to Term.
Residual Remaining balance on loan at end of loan term (the default is 0).
ResOff Number of periods after last periodic payment that residual is to be paid; can have fractional component (the default is 0).
Adv Number of advance payments made at loan inception (the default is 0).
Odd Number of periods between loan inception and date of first payment (not including advance payments); can have fractional component (the default is 1).
Simp 0 to specify compounded interest or 1 to specify simple interest (the default is 0).
@AMPMTI calculates the interest portion of the nth payment of an amortized loan. Term and n should include any advance payments made at the beginning of the loan.
If Simp = 0, @AMPMTI uses this formula:
If Simp = 1, @AMPMTI uses this formula:
I interest
B balance
Balance n-1, like Principal, is the present value of an annuity. Both correspond to annuities with the same payment size but differing in number of payments. If Principal corresponds to an annuity with Term payments, Balance n-1 corresponds to annuity with Term n + 1 payments. tn equals 1 except when n equals Adv + 1, in which case tn equals Odd.
Example
This formula calculates the portion of the 15th payment (the 15th after any advanced payments) of a 120 payment loan that constitutes interest, if the original principal of $100,000 is financed at a periodic rate of 4.5% and the first two payments are made in advance:
@AMPMTI(100000,0.045,120,17,0,0,2) = $4,106.92.
The value of 17 passed for n specifies the 15th payment after the two advance payments.
Int Periodic interest rate (for example, if Term is expressed in half-years, Int must be a semiannual rate).
Term Term of loan, expressed in number of total payments.
Payment Periodic payment.
Residual Remaining balance on loan at end of loan term (the default is 0).
ResOff Number of periods after last periodic payment that residual is to be paid; can have fractional component (the default is 0).
Adv Number of advance payments made at loan inception (the default is 0).
Odd Number of periods between loan inception and date of first payment (not including advance payments); can have fractional component (the default is 1).
Simp 0 to specify compounded interest or 1 to specify simple interest (the default is 0).
@AMPRN calculates the initial principal of an amortized loan.
Examples
A loan has an annualized monthly compounded interest rate of 10.8% (10.8%/12 = 0.9% monthly rate) over a period of 48 months, and the monthly payment is $525. Each payment is due at the beginning of the month, including the first payment which coincides with the loan's start date. This formula calculates the amount financed:
@AMPRN(0.009,48,525,0,0,1) = $20,573.04
A savings plan requires a monthly contribution of $1,000 for a period of 25 years. If the plan pays an annual interest rate of 6.6% (6.6%/12 = 0.55% monthly rate), this formula calculates what initial deposit (not payment), if any, is needed in order to accumulate $1,000,000 two and a half years after the last monthly payment:
@AMPRN(0.0055,300,-1000,1000000,30) = $16,907.93.
If the annuity is viewed as a loan and the investor as the lender, the original investment can be treated as the loan principal, the monthly payments as payments from lender to borrower, and the $1,000,000 future value as a residual payment from the borrower to the lender. The interest rate is 0.55%. The negative payment means payment from lender to borrower. A value of 30 for ResOff specifies the thirty month delay between the last monthly contribution and the date on which to measure the end balance.
Int Periodic interest rate (for example, if Term is expressed in months, then Int must be a monthly rate).
Term Term of loan, expressed in number of total payments.
Payment Periodic payment.
ResOff Number of periods after last periodic payment that Residual is to be paid; can have fractional component (the default is 0).
Adv Number of advance payments made at loan inception (the default is 0).
Odd Number of periods between loan inception and date of first payment (not including advance payments); can have fractional component (the default is 1).
Simp 0 to specify compounded interest or 1 to specify simple interest (the default is 0).
@AMRES calculates the residual (or balloon) payment of an amortized loan or the future value of an annuity.
Example
A $10,000,000 loan is paid back in 20 payments of $1,000,000 and a final payment. The first payment is made at the start of the loan. The remaining 19 payments are made annually, starting 9 months after the first payment. The final payment is made 20 months after the last annual payment. If the loan has an annual interest rate of 9.68%, this formula calculates the final payment (assume compounding of interest over fractional periods):
ResOff is set to 20/12 to specify the 20 month delay between the last annual payment and the residual payment. Adv is set to 1 to specify the advance payment. Odd is set to 9/12 (0.75) to specify the nine month period between the start of the loan and the second payment.
@AMRPRN - Amortized Remaining Principal
Syntax
@AMRPRN(Principal, Int, Term, n, <Part>, <Residual>, <ResOff>, <Adv>, <Odd>, <Simp>)
Principal Initial loan principal.
Int Periodic interest rate (for example, if Term is expressed in years, then Int must be a yearly rate).
Term Term of loan, expressed in number of total payments.
n Number of payments made; must be an integer from 0 to Term.
Part Part of (n+1)th period passed; 0 ú Part ú 1 (the default is 0).
Residual Remaining balance on loan at end of loan term (the default is 0).
ResOff Number of periods after last periodic payment that residual is to be paid; can have fractional component (the default is 0).
Adv Number of advance payments made at loan inception (the default is 0).
Odd Number of periods between loan inception and date of first payment (not including advance payments); can have fractional component (the default is 1).
Simp 0 to specify compounded interest or 1 to specify simple interest (the default is 0).
@AMRPRN computes the balance remaining after n payments, accounting for possible interest accrual over part of the following payment period.
Term and n should include advance payments made at the beginning of the loan. Part handles payoff situations where the payoff date (date on which the remaining balance on a loan is fully paid) does not coincide with a periodic payment date. For example, if 15 out of 36 monthly payments of a loan have been made and 17 days have passed since the 15th payment date, Part = 17/30, assuming there are 30 days between the 15th and 16th monthly payments.
If Simp = 0, @AMRPRN uses this formula:
If Simp = 1, @AMRPRN uses this formula:
B Balance
P Part
Examples
A loan of $100,000 has an annual interest rate of 9.6% (9.6%/12 = 0.8% monthly rate). Repayment consists of monthly payments over ten years. This formula calculates the balance remaining after the 57th monthly payment:
@AMRPRN(100000,0.008,120,57) = $64,108.38
A loan of $2,000,000 was made on March 16, 1993, to be paid back in 40 quarterly payments and a final payment of $100,000. The annual interest rate is 9.96% (9.96%/4 = 2.49% quarterly rate). The first four payments are due at the start of the loan. The fifth payment is due July 1, 1993. Thereafter, a payment is due every three months. The final residual payment of $100,000 is due June 15, 2002. This formula calculates the remaining balance due on the loan as of September 23, 1996, assuming timely payments and simple interest accrual over fractional periods:
September 23, 1994 falls in the middle of the payment period following the 17th quarterly payment. It falls 84 days into the quarter, which is 92 days long, so Part = 84/92. The residual is paid 75 days after the 40th quarterly payment. The corresponding quarter (April 1, 2002 to July 1, 2002) contains 91 days, so ResOff = 75/91. The first quarterly payment after the advance payments is paid one period and 16 days after loan inception. The 16 days correspond to the quarter containing the loan inception date, March 16, 1993. That quarter contains 90 days, so Odd = 1+(16/90).
Int Periodic interest rate (for example, if term is expressed in quarters, Int must be a quarterly rate).
Payment Periodic payment.
Residual Remaining balance on loan at end of loan term (the default is 0).
ResOff Number of periods after last periodic payment that residual is to be paid; can have fractional component (the default is 0).
Adv Number of advance payments made at loan inception (the default is 0).
Odd Number of periods between loan inception and date of first payment (not including advance payments); can have fractional component (the default is 1).
Simp 0 to specify compounded interest or 1 to specify simple interest (the default is 0).
@AMTERM calculates the duration of an amortized loan, expressed in number of payments.
Example
A loan for $50,000 was made on April 1, 1993. The loan is repaid monthly, starting on May 1, 1993, except for the first three payments, which are due at the start of the loan (April 1, 1993). If the monthly interest rate is 1.15%, this formula calculates the smallest number of payments that would allow repayment with a maximum allowable monthly payment of $600:
@AMTERM(50000,0.0115,600,0,0,3) = 228.18
The smallest number of payments to support such a loan is 229, which results in a monthly payment of $599.56. Using 228 payments results in a monthly payment of $600.10.
@AND - Logical And
Syntax
@AND(List)
List True-or-false conditions to test.
@AND returns 1 (true) if all arguments are true, 0 (false) if even one argument is false.
Arguments in List must be logical values or references.
@AND ignores text, numbers, or empty cells.
Examples
Given the following data:
A B C
1 $2 $101 $12
2 $50 $115 $22
3 $127 $130 $45
@AND(A1>10,A2>10,A3>10) = 0 (false)
@AND(B1>10,B2>10,B3>10) = 1 (true)
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, and C4 for the amounts in column C.
Suppose a $5 service charge is deducted if the daily account balance falls below the $100 minimum for three consecutive days. Use @AND to test the true-or-false conditions in A4..A6, B4..B6, and C4..C6, and @IF to subtract $5 or not, depending on the results.
For account A, @IF(@AND(A4..A6), "$5", "$0") = $0, no service charge
For account B, @IF(@AND(B4..B6), "$5", "$0") = $0, no service charge
For account C, @IF(@AND(C4..C6), "$5", "$0") = $5
@ANDB - Binary AND
Syntax
@ANDB(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.
@ANDB performs a bit-by-bit logical AND of each bit in Binary1 and Binary2. Use @ANDB to set bits to 0; any bit that is 0 in either Binary1 or Binary2 causes the resulting output bit to be 0.
If only one number is specified, then @ANDB performs an all-ones test, or AND reduction, on Binary1; @ANDB returns 1 if all bits in Binary1 are set to 1; otherwise, it returns 0.
Examples
@ANDB(10,1) = 00
@ANDB(11,10) = 10
@ANDB(11) = 1
@ANDB(1100,111,5) = 00100
@ANDH - Hexadecimal AND
Syntax
@ANDH(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.
@ANDH performs a bit-by-bit logical AND of each bit in Hex1 and Hex2. Use @ANDH to set bits to 0; any binary bit that is 0 in either Hex1 or Hex2 causes the resulting output bit to be 0.
If only one number is specified, then @ANDH performs an all-ones test, or AND reduction, on Hex1; @ANDH returns 1 if all bits in Hex1 are set to 1; otherwise, it returns 0.
Examples
@ANDH("A","F") = A
@ANDH("A") = 0
@ANDH("C","4",8) = 04
@ARRAY - Array Formula
Syntax
@ARRAY(Expression, <Columns>, <Rows>)
Expression Formula or @function using array syntax; @functions can be nested, that is, you can have more than one @function in a single statement.
Columns Number of columns in the output range, including the column of the current cell (the default Columns depends on dimensions of input array(s) in Expression).
Rows Number of rows in the output range, including the row of the current cell (the default Rows depends on dimensions of input array(s) in Expression).
@ARRAY returns the result of Expression, which can be either a formula with array operands or an @function with array arguments. An array is a selection of values treated as a single group. You do not need to type @ARRAY to create an array formula or @function; if an Expression requires array output, Quattro Pro converts it by surrounding it with the @ARRAY @function.
Columns and Rows are optional arguments; the size of an output array is dependent on the size of the input array(s) in Expression. Specify values for Columns and Rows only if you want to truncate or replicate portions of the output array.
By using arrays in formulas and @functions, you can perform an operation on multiple values or cells. You also save time by not having to repeat the same formula or @function in multiple cells. Arrays also save memory by reducing the number of formulas in a notebook.
If an array expression returns an array, the @ARRAY @function appears only in the current cell, which is also the upper left cell of the output array; the other cells in the array contain calculated values. Also, array formulas do not recognize 3-D syntax; if you specify a 3-D selection in Expression, @ARRAY recognizes only the cells on the first sheet of the series of consecutive sheets.
If you use many array formulas in a notebook, recalculation may become noticeably slower. Also, if you plan to share a notebook with other people, keep in mind that array formulas can make notebooks difficult to understand.
Examples
The next figure shows several examples using @ARRAY.
A B C D
1 B1=@ARRAY({1;2;3}*12) 12 24 36
2
3 B3=@ARRAY({1|2|3}*12) 12
4 24
5 36
6
7 B7=@ARRAY(D7..D9*2) 16 8
8 20 10
9 24 12
10
11 B11=@ARRAY(D7..D9/{4;5;6}) 2 1.6 1.333333
12 2.5 2 1.666667
13 3 2.4 2
14
15 1.23 -6.43 9
16 B16=@ARRAY(@ABS(B15..D15)) 1.23 6.43 9
17
18 B18=@ARRAY(@SQRT(C18..C20)) 6.78233 46
19 7.348469 54
20 6 36
21
22 B22=@ARRAY(@UPPER(C22..C25)) THIS This
23 IS is
24 A a
25 TEST Test
@ASCTOHEX - Convert ASCII to Hexadecimal
Syntax
@ASCTOHEX(ASCII, <Places>)
ASCII ASCII character string to convert; can be up to 20 ASCII characters.
Places Number of characters to return; can be from 1 to 40 characters.
@ASCTOHEX returns the hexadecimal string equivalent of an ASCII number.
If the ASCII value includes nonnumeric characters, enclose it in quotation marks.
Examples
@ASCTOHEX("A") = 41
@ASCTOHEX("A",4) = 0041
@ASCTOHEX("01ABCDEF") = 3031414243444546
@ASCTOHEX("QUATTRO",5) = 4524F
@ASEC - Arc Secant
Syntax
@ASEC(X)
X The secant of an angle. X can be any value from approximately -1.789E+308 through -1 and from 1 through approximately 1.789E+308.
@ASEC calculates the arc, or inverse, secant using the secant X of an angle. The result of @ASEC is an angle, in radians, from 0 through p (from 0 through 180 degrees). To convert radians to degrees, use @DEGREES.
Examples
@ASEC(1) = 0
@ASEC(-2) = 2.094395
@DEGREES(@ASEC(-2)) = 120 degrees
@ASEC(0.25) = ERR, because X is between -1 and 1
@ASECH - Arc Hyperbolic Secant
Syntax
@ASECH(X)
X The hyperbolic secant of an angle. X must be greater than 0 and less than or equal to 1.
@ASECH calculates the arc, or inverse, hyperbolic secant using the hyperbolic secant X of an angle. The result is in radians; to convert to degrees, use @DEGREES.
Examples
@ASECH(@RADIANS(30)) = 1.263277
@ASECH(@RADIANS(75)) = ERR, because the hyperbolic secant of a 75-degree angle is between 0 and 1
@ASECH(@RADIANS(45)) = 0.723368
@ROUND(@DEGREES(@ASECH(0.624888)), 2) = 60 degrees. Or: "The angle whose hyperbolic secant is 0.624888, rounded to 2 decimal places."
@ASIN - Arc Sine
Syntax
@ASIN(X)
X A numeric value between -1 and 1.
@ASIN calculates the arc sine of X. The result is the angle (in radians) whose sine is X. To convert radians to degrees, use @DEGREES.
Examples
@ASIN(1) = 1.570796
@ASIN(0.25) = 0.25268
@DEGREES(@ASIN(0.5)) = 30
@ASIN(-2) = ERR (X is less than -1)
@ASINH - Arc Hyperbolic Sine
Syntax
@ASINH(X)
X The hyperbolic sine of an angle. X can be any value from approximately -1.34078E+154 to 1.34078E+154.
@ASINH calculates the arc, or inverse, hyperbolic sine using the hyperbolic sine X of an angle. The result is in radians; to convert to degrees, use @DEGREES.
Examples
@ASINH(1) = 0.881374
@ASINH(0.25) = 0.247466
@ROUND(@DEGREES(@ASINH(0.547853)), 2) = 30 degrees. Or: "The angle whose hyperbolic sine is 1.600287, rounded to 2 decimal places."
@ATAN - Arc Tangent
Syntax
@ATAN(X)
X A numeric value.
@ATAN calculates the arc tangent of X. The result is the angle (in radians) whose tangent is X. To convert radians to degrees, use @DEGREES.
Examples
@ATAN(0.5) = 0.463648
@ATAN(1) = 0.785398
@DEGREES(@ATAN(1)) = 45
@ATAN2 - Arc Tangent of Two Points
Syntax
@ATAN2(X, Y)
X A numeric value.
Y A numeric value.
@ATAN2 calculates the arc tangent of the angle represented by the point with (x,y) coordinates X and Y. The result is the angle (in radians) whose tangent is Y/X. The result is between -pi and pi, with the quadrant chosen appropriately according to the sign of the result. If both X and Y are 0, the result is ERR.
The order of arguments is the same as for 1-2-3, but opposite that of the ATAN2 function in FORTRAN and other programming languages.
To convert radians to degrees, use @DEGREES.
Examples
@ATAN2(1,2) = 1.107149
@DEGREES(@ATAN2(1,1)) = 45
@ATANH - Arc Hyperbolic Tangent
Syntax
@ATANH(X)
X The hyperbolic tangent of an angle. X must be greater than -1 and less than 1.
@ATANH calculates the arc, or inverse, hyperbolic tangent using the hyperbolic tangent X. The result is in radians; to convert to degrees, use @DEGREES.
Examples
@ATANH(0.5) = 0.549306
@ATANH(1) = ERR, because X is not between -1 and 1
@ATANH(0.999999) = 7.254329
@ROUND(@DEGREES(@ATANH(0.655794)), 2) = 45 degrees. Or: "The angle whose hyperbolic tangent is 0.655794, rounded to 2 decimal places."
@AVEDEV - Mean Absolute Deviation
Syntax
@AVEDEV(List)
List One or more numeric or cell values.
@AVEDEV returns the mean absolute deviation, that is, the average of the absolute deviation of the data points in List from their mean. Use @AVEDEV to measure the variability of a data set around the mean. @AVEDEV uses this formula:
Example
@AVEDEV(10,11,12,13,12,11,10) = 0.897959
@AVG - Average
Syntax
@AVG(List)
List One or more numeric or cell values.
@AVG calculates the arithmetic mean of all values in List. It uses the formula: Sum of List divided by N
If List contains more than one item, they must be separated by commas. If any of the cells referenced contains ERR, the resulting value is ERR.
@AVG ignores blank cells in cells when it makes its calculations. Cells containing blank labels, however, are treated as 0; make sure blank cells are empty.
Examples
B C D
1
2 January February March
3 $652 $833 $599
4 $456 $305 $522
5 $68 $59 $73
@AVG(5,20,10,5) = 10
@AVG(B3..D3) = $694.67
@AVG(225,B3..D5) = $379.20
@AVG(B3..B5,PART) = $395.50 when C3..C5 is named PART
