Nper Number of compounding periods per year, truncated to an integer.
@EFFECT calculates the effective annual interest rate for a specified nominal rate and number of compounding periods a year.
@EFFECT is related to @NOMINAL in the following way:
where
Re effective rate
Rn nominal rate
Nper number of compounding periods per year
@EFFECT returns ERR if either argument is non-numeric, if NomRate <= 0, or if Nper < 1.
Example
@EFFECT(7.18%,4) = 0.073756 or 7.3756%
@EMNTH - Ending Day in Month
Syntax
@EMNTH(Date)
Date Number representing a date. See "Using dates and times in Quattro Pro."
@EMNTH returns the serial date number for the date of the last day of the month in which Date falls.
Example
@EMNTH(@DATE(96,2,14)) = 35124 (February 29, 1996), the last day of the month in which February 14, 1996 falls.
@EOMONTH - Last Day of Month
Syntax
@EOMONTH(StartDate, Months)
StartDate Serial number of start date. See "Using dates and times in Quattro Pro."
Months Number of months before or after StartDate:
If Months is positive, @EOMONTHS returns a date after StartDate.
If Months is negative, @EOMONTHS returns a date before StartDate.
If Months is not an integer, @EOMONTHS truncates it.
@EOMONTH returns the serial date number for the last day of the month a specified number of months before or after StartDate. @EOMONTH lets you calculate maturity dates falling on the last day of the month.
@EOMONTH returns ERR if:
StartDate is not a valid serial date number.
StartDate plus Months yields an invalid serial date number.
Either argument is non-numeric.
Examples
@EOMONTH(@DATE(96,2,14),4) = 35246 or 6/30/96
@EOMONTH(@DATE(96,2,14),-2) = 35064 or 12/31/95
@ERFD - Error Function Derivative
Syntax
@ERFD(X)
X A value from -26.6417 to 26.6417.
@ERFD(X) returns the derivative of the error function. It uses the following formula:
(2/@SQRT(@PI)) * @EXP(-X^2)
If X is less than -26.6417 or greater than 26.6417, @ERFD returns ERR because the calculation is too large to store.
Example
@ERFD(1) = 0.415107
@ERF - Error Function
Syntax
@ERF(Lower, <Upper>)
Lower Lower bound for integrating @ERF; must be │ 0.
Upper Upper bound for integrating @ERF; if omitted, @ERF integrates the error function between 0 and Lower; must be │ 0.
@ERF returns the error function integrated between Lower and Upper. The error function helps solve partial differential equations that involve convection or diffusion.
The equation for @ERF(z) is
The equation for @ERF(a,b) is
This is the same as @ERF(b) minus @ERF(a).
Example
@ERF(0,1) = 0.842701
@ERFC - Complementary Error Function
Syntax
@ERFC(Lower)
Lower Lower bound for integrating @ERF; must be │ 0.
@ERFC returns the complementary error function, which derives from the error function @ERF. The formula for @ERFC(x) is
This is the same as 1 - @ERF(x).
Therefore, @ERFC(Lower) = 1 - @ERF(Lower,Upper).
Example
@ERFC(1) = 0.157299
@ERR - Error Value
Syntax
@ERR
@ERR returns the value ERR in the current cell and in any other cells that reference the current cell, either directly or indirectly. (Exceptions to this are @COUNT, @DCOUNT, @ISERR, @ISNA, @ISNUMBER, @ISSTRING, and @CELL formulas; these will not result in ERR if they reference an ERR cell.)
The ERR value resulting from this @function is the same as the ERR value produced by Quattro Pro when it encounters an error. It is often used with @IF to bring attention to error conditions.
ERR is a unique number, not to be confused with the label ERR.
Examples
@ERR = ERR
@IF(B6>B7,0,@ERR) = 0 (if B6>B7) or ERR (if B6<B7)
@EVEN - Round Up to Even Number
Syntax
@EVEN(X)
X Value to round.
@EVEN rounds X up (away from zero) to the nearest even integer, ignoring the sign of X. If X is already an even integer, @Even returns x unchanged.
Examples
@EVEN(3.2) = 4
@EVEN(-3.2) = -4
@EVEN(8) = 8
@EXACT - Test If Values Are Exactly Alike
Syntax
@EXACT(String1, String2)
String1 A valid string value.
String2 A valid string value.
@EXACT compares the values of String1 and String2. If the values are exactly identical, including capitalization and diacritical marks (such as ~), it returns 1. If there are any differences, it returns 0.
If you are comparing literal strings, surround them with double quotes. If you use a cell name or cell address, no quotes are necessary. You can compare the contents of label cells only. If you try to compare one or more numbers or empty cells, the result is ERR. When you compare labels, label prefixes are ignored.
To compare strings or cell contents without regard to capitalization or diacritical marks, use @IF. For example, @IF(C3=B3,1,0) returns 1 if the contents of the cells are the same but are capitalized differently.
Examples
@EXACT("client","Client") = 0
@EXACT("client","client") = 1
@EXACT(29,"29") = ERR (the first string is a value)
@EXACT(A1,"yes") = 1 (if A1 contains the label yes)
@EXACT(client,client) = syntax error (no quotation marks)
@EXACT("client","client","client ") = syntax error (more than two strings)
@EXP - e Raised to X Power
Syntax
@EXP(X)
X A numerical value equal to or less than 709.
@EXP returns the mathematical constant e, raised to the Xth power. This @function is the inverse of a natural logarithm, @LN.
Examples
@EXP(3.4) = 29.9641000474
@EXP(1) = 2.718281828459 (the actual value of e)
@SQRT(@EXP(2)) = 2.71828183
@LN(@EXP(2.5)) = 2.5
@EXP2 - e Raised to -X^2 Power
Syntax
@EXP2(X)
X A value from -26.6417 to 26.6417.
@EXP2 calculates the value of the constant e raised to the power (-X^2). The constant e equals 2.718281828459.
If X is less than -26.6417 or greater than 26.6417, @EXP2 returns ERR.
Example
@EXP2(1) = 0.367879
@EXPONDIST - Exponential Distribution
Syntax
@EXPONDIST(X, Lambda, Cum)
X Value at which to evaluate the function; must be │ 0.
Lambda Value to indicate; Lambda = 1/Mean; must be > 0.
Cum 1 to perform cumulative distribution function; 0 to perform the probability density function.
The exponential distribution, sometimes called the waiting-time distribution, describes the amount of time or distance between the occurrence of random events. For example, it can be used to find the time between major earthquakes or the time between no hitters pitched in major league baseball. The exponential distribution calculated by @EXPONDIST is a continuous distribution with a probability density function whose formula is:
For the cumulative distribution function, the formula is:
Use this distribution in connection with estimating the length of material life, or the length of time a process might take.
Examples
On average, customers at a certain bank must wait 2 minutes before being served by a teller. This formula calculates the probability that someone would have to wait 3 minutes:
@EXPONDIST(3,1/2,0) = 0.111565
This formula calculates the probability that someone would wait only 1 minute for a teller:
@EXPONDIST(1,1/2,1) = 0.393469
@FACT - Factorial
Syntax
@FACT(N)
N Integer │ 0 specifying the factorial to calculate.
@FACT calculates the factorial of a number. N! is defined as follows: if N │ 0,
N! = N ┤ (N-1) ┤ (N-2) ┤ (N-3) ┤...┤ (2) ┤ (1)
@FACT(0) returns 1. If N is a non-integer or negative number, @FACT returns ERR.
Examples
@FACT(10) = 3628800
@FACT(128) = 3.9E+215
@FACTDOUBLE - Double Factorial
Syntax
@FACTDOUBLE(N)
N Value │ 0 to calculate factorial of.
@FACTDOUBLE returns the double factorial of N. N!! is defined as follows:
If N is even, N!! = N(N-2)(N-4)...(4)(2)
If N is odd, N!! = N(N-2)(N-4)...(3)(1)
If N is negative, @FACTDOUBLE returns ERR.
Examples
@FACTDOUBLE(12) = 46080
@FACTDOUBLE(13) = 135135
@FACTLN - Natural Logarithm of Factorial
Syntax
@FACTLN(n)
n Integer from 0 through 170.
@FACTLN returns the natural logarithm of the factorial of n. The factorial of n is the product of all positive integers from 1 to n. The factorial of 0 is 1 by definition.
Example
@FACTLN(4) = 3.178054
@FALSE - Logical Value 0
Syntax
@FALSE
@FALSE returns the logical value 0 and is usually used in @IF formulas. The zero that it returns is the same as any other zero, but @FALSE makes the formula easier to read.
@TRUE is a related @function.
Examples
@FALSE = 0
@IF(C3=100,10,@FALSE) = 10 (if C3 = 100) or 0 (if C3 is not equal to 100)
@IF(C3=100,@TRUE,@FALSE) = 1 (if C3 = 100) or 0 (if C3 is not equal to 100)
@FBDAY - First Business Day
Syntax
@FBDAY(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).
@FBDAY returns the serial date number of the first business day of the month in which Date falls. If the first of the month is not a business day, @FBDAY returns the business day closest to it within the same month.
Example
This formula calculates the first business day in January 1997, assuming that Saturdays, Sundays, and some dates are holidays:
A
1 01/01/97
2 01/02/97
3 01/03/97
@FBDAY(@DATE(97,1,1),A1..A3,0,0) = 35436 (which is Monday, January 6, 1997)
@FDIST - F-Distribution
Syntax
@FDIST(X, DegFreedom1, DegFreedom2)
X Positive value at which to evaluate the function.
DegFreedom1 Numerator degrees of freedom; must be │ 1.
DegFreedom2 Denominator degrees of freedom; must be │ 1.
@FDIST returns the cumulative F-distribution function, which is the probability that a random variable will be less than X. Use @FDIST to compare two population variances.
Tu Total units; if Tu is negative, @FEETBL uses its absolute value.
Ppu Price per unit.
StdTbl|Val Fee table or a single value that defines the standard fee calculation.
MinTbl|Val Fee table or a single value that defines the minimum fee calculation (if omitted, MinTbl equals StdTbl).
MaxTbl|Val Fee table or a single value that defines the maximum fee calculation (if omitted, MaxTbl equals StdTbl).
RndPlcs Number of places to which the final result is rounded; can be from 0 to 10 places (the default is no rounding).
@FEETBL returns fee calculations from tables. You can use @FEETBL to calculate fees or commissions for many types of stock transactions, taxes, sales commissions, and other types of fees and charges. To use @FEETBL, you need to create a table (or tables) that describes the fees.
@FEETBL is more powerful than other table lookup @functions such as @HLOOKUP and @VLOOKUP because it allows you to
Compare the standard fee with minimum and maximum values
Multiply the lookup value by the number of units or total price
Add a fixed value to the fee
Round the result to a specified number of decimal places
If the fee table is indexed by values of total units or price per unit, Tu or Ppu must be greater than the smallest value in the index; otherwise, @FEETBL cannot find a lookup value. If either Tu or Ppu is zero, @FEETBL returns zero.
If you specify an optional argument, such as RndPlcs, you must also specify all preceding optional arguments. If MinTbl and MaxTbl are not pertinent to the fee calculation, use StdTbl again for MinTbl and MaxTbl, or enter values that have no effect on the final result. For example, enter 0 for MinTbl and 1E+99 for MaxTbl.
The upper left cell of a fee table must contain a table header string that identifies the row index, column index, and cell contents of the table, and also specifies if the table contains an additive factor for the fee calculation. The table header string consists of three or four parameters separated by a space; each parameter has several possible values.
For valid comparison, values for StdTbl, MinTbl, and MaxTbl arguments must have the same units.
Table header parameters
Parameter Description Values
1 Row index tu, ppu, tp, na
2 Column index tu, ppu, tp, na
3 Cell contents fpu, fpct, luo
4 Additive factor fa
Description Values
tu total units
fpu fee per unit
ppu price per unit
fpct fee percentage
tp total price
luo lookup only
na not applicable
fa fixed adder
The first parameter of the table header identifies the contents of the row index, which appears in the first column of the table below the table header. The second parameter identifies the contents of the column index, which appears in the first row of the table to the right of the table header.
The third parameter of the table header determines if @FEETBL multiplies the lookup value from the table by another value. For example, "fpu" (fee per unit) indicates that @FEETBL multiplies the lookup value by the number of units; "fpct" (fee percentage) indicates that the lookup value is a percentage that @FEETBL multiplies by the total price; "luo" (lookup only) indicates that @FEETBL uses the lookup value without modification.
The fourth parameter of the table header is an optional additive factor; specify "fa" (fixed adder) to add a value to the result of the operation specified by the third parameter. If the fee table has no additive factor, omit the fourth parameter.
In the next figure, the table header in cell A3 is "tp na fpct fa"; "tp" indicates that A4..A9 represents the row index values for total price; "na" indicates that B3..C3 has no column index values; "fpct" indicates that the lookup values in B4..B9 are percentages that must be multiplied by the total price; "fa" indicates that the values in C4..C9 are "fixed adders", that is, one of these values must be added to the product of the fee percentage and the total price.
A B C
1 Standard Commission Rate Table
2 Principal %Fee + Fixed Adder
3 tp na fpct fa
4 $0 1.60% $26.00
5 $2,500 0.60% $51.00
6 $6,000 0.30% $69.00
7 $22,000 0.20% $91.00
8 $50,000 0.10% $141.00
9 $500,000 0.08% $241.00
In the next figure, the table header in cell A2 is "tu tp luo"; "tu" indicates that A3..A7 represents the row index values for total units; "tp" indicates that B2..E2 represents the column index values for total price; "luo" indicates that @FEETBL uses the lookup values in B3..E7.
A B C D E
1 Total Units Total Price
2 tu tp luo $0 $10,000 $15,000 $20,000
3 0 $250 $500 $750 $1,000
4 2 $200 $400 $600 $800
5 5 $175 $350 $525 $700
6 10 $150 $300 $450 $600
7 20 $125 $250 $375 $500
@FEETBL treats all string values (other than the table header) or empty cells in fee tables as zero.
Examples
A furniture manufacturer sells 100 bookcases at a price of $150 each to a retailer. This formula calculates the handling fee for the order based on the fee table in the next figure.
@FEETBL(100,150,A1..D5) = $300
Cell A4 is the row index value for 100 total units. Cell C1 is the column index value for $150 price per unit. Cell C4 is the lookup value, which is multiplied by the total units: $3 * 100 = $300.
This formula calculates the handling fee based on the fee table in the next figure for a sale of 5 end tables at a price of $75 each:
@FEETBL(5,75,A1..D5) = $25
Cell A2 is the row index value for 5 units (between 0 and 9). Cell B1 is the column index value for $75 price per unit. Cell B2 is the lookup value, which is multiplied by the total units: $5 * 5 = $25.
A B C D
1 tu ppu fpu $0 $100 $500
2 0 $5 $7 $8
3 10 $4 $5 $6
4 100 $2 $3 $4
5 1000 $2 $1 $2
@FIB - Fibonacci Sequence
Syntax
@FIB(N)
N Integer │ 0 specifying the desired term of a Fibonacci sequence.
@FIB calculates the Nth term of a Fibonacci sequence (1, 1, 2, 3, 5, 8, 13, 21...), in which each number, after the first two, is the sum of the two numbers immediately preceding it. @FIB(0) is defined to be 0.
Examples
@FIB(4) = 3
@FIB(9) = 34
@FIB(15) = 610
@FIELD - Nth Substring in a String
Syntax
@FIELD(String,N,<Delimiter>)
String A string value containing two or more delimited substrings, or a cell reference to a delimited string value.
N Number of the substring you want to find (the first substring is numbered 1, the second 2, and so on).
Delimiter Optional delimiter character; if you do not specify a delimiter, Quattro Pro uses the delimiter character specified in the Application International property.
@FIELD finds the nth substring in String, a delimited list of strings. You can use @FIELD to find a single property value from a delimited list of properties returned by the @PROPERTY function. You can also use it to return a specific field from an imported text file.
Example
The cell in A1 contains the following formula:
@PROPERTY("Active_Block.Shading")
The shading property has not been changed from the default setting, so the formula returns
3,0,Blend7
You can use @FIELD with @PROPERTY to return the third substring in the comma-delimited string:
@FILEEXISTS returns a 1 if a file named FileName exists in the current file directory, and returns a 0 if it does not. FileName can be a cell name containing a path or file name string. If entered as a literal string, FileName must be enclosed by quotes and must include any extension attached to the file name. To search for a file in a directory other than the default directory, include the directory path in FileName.
Examples
@FILEEXISTS("EXAMPLE.QPW") = 1 (if EXAMPLE.QPW is in the working directory)
@FILEEXISTS("C:\DATA\EXAMPLE.QPW") = 1 (if EXAMPLE.QPW is in the specified directory)
@FILEEXISTS(FILE_NAME) = 1 (if the selection FILE_NAME contains a path and file-name label and if the file exists in that directory)
@FIND - Search for String
Syntax
@FIND(Substring, String, StartNumber)
Substring A valid string value, representing the value to search for.
String A valid string value, representing the value to search through.
StartNumber A numeric value │ 0, representing the character position to begin searching with; 0 = the first character.
@FIND searches through String from left to right for Substring. If it finds Substring, it returns the character position of the first occurrence. StartNumber indicates where to begin the search: 0 = the first character in the string, 1 = the second, and so on. The value of StartNumber must not be more than the number of characters in String minus 1.
@FIND is case-sensitive and is also sensitive to diacritical marks used in non-English languages. You can overcome the case sensitivity of this @function by using @UPPER to force one or more of the strings into all uppercase letters. For example, the following formula forces both the substring in cell C3 and the string in cell C4 to uppercase, then searches for the substring:
@FIND(@UPPER(C3),@UPPER(C4),0)
@FIND is most often used in conjunction with two other string functions: @REPLACE (to perform "search and replace" operations on strings) and @MID (to access substrings).
If @FIND fails to find any occurrences of Substring, or if the StartNumber given is invalid, the result is ERR.
Examples
@FIND("i","find",0) = 1
@FIND("nd","find",2) = 2
@FIND("F","find",0) = ERR
@FIND("f","find",3) = ERR
@FIND("d","find",4) = ERR
@FIND(n,find,0) = syntax error (quote marks omitted from strings)
@FIND("hi",C4,0) = 1 (if C4 contains ship)
@FINV - Inverse of F-Distribution
Syntax
@FINV(Prob, DegFreedom1, DegFreedom2)
Prob Cumulative probability value; must be │ 0 and ú 1.
DegFreedom1 Numerator degrees of freedom; must be │ 1.
DegFreedom2 Denominator degrees of freedom; must be │ 1.
@FINV returns the inverse of the cumulative F-distribution function. Use this function to measure the degree of variability in two data sets.
Example
@FINV(0.05,5,4) = 6.256057
@FIRSTBLANKPAGE
Syntax
@FIRSTBLANKPAGE(Block)
Block A cell or reference; can be a link to another opened notebook (for example, [BUDGET]A:A1).
@FIRSTBLANKPAGE returns a string that contains the letters for the first unnamed blank sheet in a notebook that is not part of a group.
Quattro Pro searches for the first unnamed blank sheet (that is not in a group) starting at sheet A and continuing toward sheet IV. If there are no unnamed blank sheets (or they are all in groups), @FIRSTBLANKPAGE returns ERR.
Example
@FIRSTBLANKPAGE(B17) = "AA" (if it is the first sheet that is blank and unnamed)
@FIRSTINGROUP - First Sheet in Group
Syntax
@FIRSTINGROUP(Block, GroupName)
Block A block of cells to check in the notebook.
GroupName A string value representing a group name.
@FIRSTINGROUP returns a string that contains the letters for the first sheet in the group named GroupName. @FIRSTINGROUP searches the notebook referenced by Cell for the group. If the group does not exist, @FIRSTINGROUP returns ERR.
Example
@FIRSTINGROUP([REPORTQ4]A:C12,"Totals") = "A" (if the notebook REPORTQ4 contains a group named Totals that starts with sheet A)
@FISHER - Fisher Transformation
Syntax
@FISHER(X)
X Numeric value; -1 < X < 1.
@FISHER returns the Fisher transformation at the value X. Fisher's z-transformation is used to produce an approximately normally distributed variable (rather than skewed) from the correlation coefficient. The formula @FISHER uses is
Example
@FISHER(0.25) = 0.255413
@FISHERINV - Inverse of Fisher Transformation
Syntax
@FISHERINV(Y)
Y Numeric value ú 354 for which you want the inverse of the Fisher transformation.
@FISHERINV returns the inverse of the Fisher transformation. Use @FISHERINV to determine the confidence limits for a correlation coefficient.
Example
@FISHERINV(0.255413) = 0.25
@FIXED - Decimal Number as Text
Syntax
@FIXED(Num, <Dec>, <NoCommas>)
Num Number to be rounded and converted to text.
Dec Number of decimal places to be displayed. If Dec is negative, @FIXED rounds off Num to the left of the decimal point. If you omit Dec, @FIXED rounds off to 2 decimal places.
NoCommas A logical value:
1 = do not display thousands separators
0 = display thousands separators (the default, if omitted, or if NoCommas ╣ 1)
@FIXED rounds a number to a specified number of decimals, formats it, and displays the result as text. Your display will depend on your Application International Property settings.
Num cannot have more than 15 significant digits.
You can also format numeric values by right-clicking the cell and choosing Cell Properties, then Fixed, then entering the number of decimal places to display. The result looks the same, but the value remains a numeric value when you use Cell Properties, while @FIXED converts it to text.
When you use any optional argument, you must also use the ones before it.
Examples
@FIXED(9449.985, 2) = "9,449.99"
@FIXED(9449.985, 2, 1) = "9449.99"
@FIXED(9449.985, 0) = "9,450"
@FIXED(9449.985, -3) = "9,000"
@FLOOR - Round Down to Nearest Multiple
Syntax
@FLOOR(X, Y)
X Value to round.
Y Value to make rounded X evenly divisible by.
@FLOOR rounds X down (toward zero) to the nearest value that is evenly divisible by Y. If X and Y have different signs, the result of @FLOOR is ERR.
Examples
@FLOOR(3.2,3) = 3
@FLOOR(-3.2,-3) = -3
@FORECAST - Linear Regression Forecast
Syntax
@FORECAST(X, KnownY, KnownX)
X Numeric value at which to evaluate the function.
KnownY Dependent range of values.
KnownX Independent range of values.
@FORECAST returns a predicted Y value corresponding to X based upon a linear regression of KnownY and KnownX.
KnownY and KnownX must contain the same number of values. The variance of KnownX must not be 0.
Example
This example refers to cells in the figure below.
@FORECAST(1000,C2..C16,B2..B16) = $15,868.50
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
@FRACD - Fraction to Decimal
Syntax
@FRACD(Frac, Denom)
Frac Number to be converted.
Denom Denominator; must be an integer > 0.
@FRACD converts the fraction Frac to a decimal number. For example, you can use this @function to convert a number with a fractional portion in 32nds to a decimal number. @FRACD reverses the effect of @DFRAC.
Frac looks like a decimal, but @FRACD does not use it that way. The portion to the right of the decimal point is the numerator of the fraction using the denominator specified by Denom. For example, if Denom is 32 and you want to find the decimal equivalent of 99 , set Frac to 99.12. If Denom were 100, setting Frac to 99.12 represents 99 .
Using a value of @FRACD(1.1,32) computes as 1 10/32. If you want 1 1/32 you must use @FRACD(1.01,32).
Example
This formula finds the decimal equivalent of 106
.
@FRACD(106.14,32) = 106.4375
This formula finds the decimal equivalent of 1 1/32.
@FRACD(1.01,32)= 1.03125
@FRACTION - Decimal to Fraction
Syntax
@FRACTION(Value, <Denom>, <ForceDenom>)
Value Decimal value to be converted.
Denom Denominator; must be an integer > 0. If you specify a denominator that doesn't allow an exact fraction, the numerator is rounded to the nearest whole number. For example, @FRACTION(100.25, 5) would display 100 1/5.
ForceDenom When ForceDenom is not specified, the fraction is displayed in its lowest common denominator form; use 1 to display the denominator value specified in <Denom>.
@FRACTION converts a decimal value to a fraction using the specified denominator.
If you specify @FRACTION(Value, <Denom>), the fraction is created based on the denominator value, then displayed in the lowest common denominator form. If you want the denominator value displayed, even if it's not the lowest common denominator, specify 1 for ForceDenom.
If no <Denom> is specified, @fraction will round to the nearest 64th.
The fraction created from @FRACTION is a string value. You can reference the fraction in a formula by using it with the @VALUE function. For example, if the fraction is computed in B4, you can use it in a formula as @VALUE(B4).
Example
@FRACTION(100.5,4) = 100 1/2
@FRACTION(100.63,8) = 100 5/8
@FRACTION(100.5,8,1) = 100 4/8
@FRACTION(100.75,16,1) = 100 12/16
@FREQDIST - Frequency Distribution
Syntax
@FREQDIST(Data, Intervals)
Data Cells of values for which you want to count frequencies.
Intervals Array of or reference to intervals into which you want to group the values.
@FREQDIST calculates a frequency distribution, displaying it as a vertical array. A frequency distribution reports how many of the specified values occur in each of the specified intervals.
The Intervals argument tells @FREQDIST the upper boundary of each interval, so the result will be an array one greater than the number of cells in Intervals.
Intervals must be in ascending order.
If Data contains no values, @FREQDIST returns an array of zeros.
If Intervals contains no values, @FREQDIST returns the number of elements in Data.
@FREQDIST ignores blank cells and text.
Examples
Your consulting income is recorded in a cell area named INCOME. You want to know how many months your income is $100 or below, from $100 to $500, from $500 to $800, and over $800.
INCOME A B C D
1
2 January February March Intervals
3 $652 $833 $599 100
4 $456 $305 $522 500
5 $68 $59 $73 800
@FREQDIST(A3..C5,D3..D5) returns
7 3
8 2
9 3
10 1
If you named the cells A3..C5 INCOME and cells D3..D5 INTERVALS, you could also enter the formula as:
@FREQDIST(INCOME,INTERVALS) = {3| 2| 3| 1}
If the intervals are not in cells, you can establish them in the formula:
@FREQDIST(INCOME,{100,500,800}) = {3| 2| 3| 1}
@FTEST - F-test
Syntax
@FTEST(Array1, Array2)
Array1 First array of numeric values.
Array2 Second array of numeric values.
@FTEST returns the results of an F-test run against the samples in Array1 and Array2. An F-test is a one-tailed probability that the differences in the sample variances in Array1 and Array2 are different. Use @FTEST to determine if two samples have significantly different variances (that is, if data sets were drawn from different parent populations).
Array1 and Array2 must have more than two values. The variance of Array1 or Array2 must not be zero.
String The single-byte (half-width) character string
@FULLP converts a single-byte character string to a full-width, double-byte character string in a label. Double-byte characters are used in software localized to most Far Eastern languages (for example, Japanese, Chinese, and Korean). The localized machine will display a toolbar that lets you select various single and double-byte character sets from within Quattro Pro.
Using @FULLP in a cell label allows you to convert ASCII text characters to double-byte characters. @FULLP does not convert double-byte character set (DBCS) characters.
You cannot use @FULLP to convert single-byte Katakana characters to double-byte Katakana.
Intrate Interest rate or cells containing interest (discount) rates.
Flows Cells containing cash flows.
Odd|Periods Delay after last cash flow in number of periods (the default is 0) 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.
@FUTV calculates the future value of a specified cash flow structure. The future value of a stream of cash flows is the sum of the future values of each cash flow.
By default, @FUTV computes the future value at the time of the last cash flow. If you specify Periods, @FUTV calculates the future value at a time one period after the last cash flow. If you specify Odd, @FUTV calculates the future value at a time Odd periods after the last cash flow.
@FUTV computes future value using this formula:
where n is the number of cash flows, and IFi is the interest factor associated with the ith cash flow, Fli. IFi is the @FUTV counterpart of the discount factor, DFi used in @NETPV. Unlike DFi, which reduces the value of a flow, IFi increases the value.
FV Future Value
Fl Flows
Example
Suppose a portfolio has a bond that will make 15 annual interest payments of $1,500, and pay $20,000 in principal along with the last interest payment. If the interest earned on investing the annual interest payments (the reinvestment rate) is 8.5%, this formula calculates the amount in the portfolio at the end of 15 years, using the data shown in the next figure:
@FUTV(D2,A2..B3) = $62,348.40
A B C D
1 Cash Flows Interest Rate
2 14 $1,500 8.5%
3 1 $21,500
@FV - Future Value of Investment
Syntax
@FV(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 Number of periods, which should be an integer │ 2.
@FV returns the future value of an investment where Pmt is invested for Nper periods at the rate of Rate per period. @FV calculates the future value with this formula:
where
P amount of periodic payment
R periodic interest rate
N number of periods
An equivalent for this formula using @FVAL is
@FVAL(Rate, Nper, - Pmt, 0)
@FV assumes that the investment is an ordinary annuity. @FVAL, a related @function, uses an optional argument, Type, to indicate whether the investment is an ordinary annuity or an annuity due.
Examples
Assume you want to set aside $500 at the end of each year in a savings account that earns 15% annually. To determine what the account will be worth at the end of six years, enter this formula:
@FV(500,15%,6)
Your yearly payment of $500 will be worth $4,376.87 in six years. You could also use @FVAL:
@FVAL(15%,6,-500,0,0)
Note that in @FVAL, you have to be precise about whether a payment is out of your pocket (a negative number) or paid to you (a positive number).
Other examples:
@FV(200,0.12,5) = $1,270.57
@FV(500,0.9,4) = $6,684.50
@FV(800,0.9,3) = $5,208.00
@FV(800,0.9,A3) = $40,929.67 (if A3 = 6)
@FVAL - Future Value of Investment
Syntax
@FVAL(Rate, Nper, Pmt, <Pv>, <Type>)
Rate A numeric value > -1, representing the periodic interest rate (the fixed interest rate per compounding period).
Nper Number of periods, which should be an integer > 0.
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).
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.
Like the related @function @FV, @FVAL returns the future value of an investment. The last two arguments, Pv and Type, are optional. If you omit the last one or both of them, Quattro Pro assumes their values are zero. These arguments let you define the problem as an annuity due (putting money into an account before it earns its interest for that year means you have an annuity due to you, which increases the future value). Be sure to enter negative numbers for money going out and positive numbers for money coming in to you.
This @function is not compatible with 1-2-3. If your file must be compatible with 1-2-3, use @FV instead.
Examples
Assume you want to set aside $500 at the start of each year in a savings account that earns 15% annually. To determine what the account will be worth at the end of six years, starting at a present value of zero, enter this formula:
@FVAL(15%,6,-500,0,1)
Note that the payment is out of your pocket, so you enter a negative number. Your yearly payment of $500 will be worth $5,033.40 in six years, or $656.53 more than if you deposited the money at the last day of the year as in the example for @FV.
If the account already had $340 in it before your yearly deposits of $500, you could calculate the future value after six years with this formula:
