X Number signifying what quartile value to return:
0 = minimum value in Array
1 = 25th percentile
2 = 50th percentile (median)
3 = 75th percentile
4 = maximum value in Array
@QUARTILE returns a number from Array at the quartile indicated by X. You create quartiles of a data set by partitioning the values into four groups containing an equal number of values.
If the quartile falls between two discrete values in the list, a fractional value is determined using linear interpolation.
Examples
@QUARTILE({4,5,7,9,10,12,13,16},0) = 4
@QUARTILE({4,5,7,9,10,12,13,16},1) = 6.5
@QUARTILE({4,5,7,9,10,12,13,16},2) = 9.5
@QUARTILE({4,5,7,9,10,12,13,16},3) = 12.25
@QUARTILE({4,5,7,9,10,12,13,16},4) = 16
@QUOTIENT - Integer Portion of Quotient
Syntax
@QUOTIENT(X, Y)
X Value to divide.
Y Nonzero value to divide x by.
@QUOTIENT is similar to @INT; it returns the integer portion of X/Y. If Y is zero, @QUOTIENT returns ERR.
Example
@QUOTIENT(7,2) = 3
@RADIANS - Convert Degrees to Radians
Syntax
@RADIANS(X)
X A numeric value representing degrees. Choose a numeric value in the range between -1.698E+308 to 1.085E+308 (approximately).
@RADIANS converts the given number of degrees to radians. It uses this formula:
pi times X divided by 180
One degree is equal to approximately 0.017 radians.
Examples
@RADIANS(1) = 0.017453
@RADIANS(57) = 0.994838
@RADIANS(@DEGREES(3.5)) = 3.5
@RADIANS(A4) = 0.994838 (where cell A4 contains the value 57)
@RAND - Fractional Random Number
Syntax
@RAND
@RAND returns a fractional random number between 0 and 1. This offers a sampling of figures, useful for generating sample data for simulated situations.
To generate random numbers in another range, multiply @RAND by the difference between the new high and low ends, then add the new low end number. The formula is @RAND * (high number - low number) + low number.
For example, to indicate a range of 10 to 100, enter @RAND*90+10. This extends the upper limit to 100 and the lower limit to 10.
@RAND generates a new random number with each recalculation.
Examples
@RAND = a random number between 0 and 1
@RAND*9+1 = a random number between 1 and 10
@RAND*1000 = a random number between 0 and 1000
@RAND+5 = a random number between 5 and 6
-@INT(@RAND*90+10) = a random integer between -10 and -100
@RANDBETWEEN - Random Number Between N and M
Syntax
@RANDBETWEEN(N, M)
N Integer value that random number must be greater than or equal to.
M Integer value that random number must be less than or equal to.
@RANDBETWEEN returns a random number between N and M using a uniform distribution. @RANDBETWEEN returns a new random number each time you recalculate a notebook.
@RANK - Rank of Number in List
Syntax
@RANK(Number, Array, Order)
Number A number from Array.
Array One or more numeric or cell values.
Order Flag indicating how to sort the list of numbers: any nonzero value = ascending order; 0 = descending order.
@RANK returns the rank of Number in Array in either ascending or descending order.
Examples
Theses examples refer to the next figure.
@RANK(B6,B2..B10,0) = 1
@RANK(B2,B2..B10,1) = 3
@RANK(B9,B2..B10,1) = 1
A B
1
2 HJ 117
3 MW 122
4 KM 125
5 WC 109
6 AD 137
7 MS 119
8 DP 125
9 MS 106
10 DM 121
@RATE - Interest Rate per Period
Syntax
@RATE(Fv, Pv, Nper)
Fv A numeric value representing the future value of an investment (the value the investment will reach at some point).
Pv A numeric value representing the current value of an investment (the present value).
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).
@RATE calculates the interest rate required for an investment of Pv to be worth Fv within Nper compounding periods. If Nper represents years, an annual interest rate results; if Nper represents months, a monthly interest rate results, and so on.
@RATE uses this formula to calculate interest rate:
where
Fv future value
Pv present value
N number of periods
An equivalent for this formula using @IRATE is
@IRATE(Nper, 0, - Pv, Fv)
@RATE assumes the investment is an ordinary annuity. The related @function @IRATE lets you use an optional argument, Type, to indicate whether the investment is an ordinary annuity or an annuity due.
Examples
This formula determines what yearly interest rate will double an initial investment of $2000 at the end of 10 years:
Settle Number representing the settlement date; must be < Maturity.
Maturity Number representing the maturity date; must be > Settle.
Investment Amount invested; must be m 0.
Discount Rate of discount; 0 ú Discount ú 1.
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@RECEIVED returns the redemption value (the amount received after maturity) of a discount security. @RECEIVED uses this formula:
R redemption
I investment
D discount
M maturity
S settle
b basis
tb is the number of days over which the discount rate quote applies (360 or 365).
Example
This formula calculates the redemption value of a bill with the following terms: Settle is January 17, 1995, Maturity is June 15, 1995, Investment is $1,000,000, Discount is 7.922%, and Calendar is 2 (actual/360).
XBlock Name or address of cell containing independent variables; can be up to 75 columns and 8,192 rows.
YBlock Name or address of cells containing dependent variable; must be a single-column selection with the same number of rows as XBlock.
Attribute Specifies the type of regression value returned. Valid attributes are 1, 2, 3, 4, 5, 101 to 175, or 201 to 275; see the table for the type of regression value returned for each attribute value.
Compute Logical value (optional) that tells @REGRESSION whether to force the Y intercept to equal 0:
0 = make the Y intercept 0
1 = calculate the Y intercept (default if you omit the argument)
@REGRESSION performs a multiple linear regression, returning the specified statistic. Choose Attribute arguments from this table:
Attribute Regression Value Returned
1 Constant
2 Standard error of Y estimate
3 R squared
4 Number of observations
5 Degrees of freedom
101 to 175 Slope, or X coefficient for the independent variable specified by Attribute
201 to 275 Standard error of the coefficient for the independent variable specified by Attribute
For the last two attributes, Quattro Pro numbers the independent variables in XBlock, starting with 1, from top to bottom in a column and from left to right. For example, if XBlock is B2..D7, use Attribute = 102 to find the X-coefficient for the independent variable in column C. Use Attribute = 201 to find the standard error of coefficient for the independent variable in column B.
Example
In trying to lower employee absenteeism, you suspect sick days correlate directly with outdoor temperatures and deadline pressure. You assemble data for the first Monday of the month over a six-month period:
A B C D E
1 % absent Date Due date Days left 9 a.m. temp.
2 14% 12/04/95 04/01/96 119 17
3 22% 01/02/96 04/01/96 90 10
4 32% 02/05/96 04/01/96 56 20
5 48% 03/04/96 04/01/96 28 35
6 2% 04/01/96 11/03/96 216 52
7 18% 05/06/96 11/03/96 181 55
*This January, "Monday" comes on Tuesday, the day after New Year's Day.
To find values for R squared:
@REGRESSION(D2..D7, A2..A7, 3) = 0.811862, fairly close to 1, indicating a fair correlation between absenteeism and deadlines.
@REGRESSION(E2..E7, A2..A7, 3) = 0.075683, much closer to 0, indicating little relation between absenteeism and weather.
@REPEAT - Repeat Copies of a String
Syntax
@REPEAT(String, Num)
String A string value.
Num A numeric value equal to or greater than 0.
@REPEAT returns Num copies of String as one continuous label. This @function is similar to the repeating label prefix (\) in that it repeats one or more characters. The difference is that you can specify exactly how many times you want the string to be repeated. The \ label prefix adjusts the display to fill the column, even when the width is changed. @REPEAT displays a fixed number of copies of String and does not change.
When you specify a text string with @REPEAT, it must be enclosed by double quotes.
Examples
@REPEAT("-",20) = --------------------
@REPEAT("good day!",3) = good day!good day!good day!
@REPEAT(A5,5) = the contents of A5 repeated 5 times
@REPEAT("-",@CELL("width",A1..A1)) = ------------ if column A is 12 characters wide. If you change the column width, you can press F9 to adjust the repeat string to fill the cell.
@REPLACE - Replace Characters in a String
Syntax
@REPLACE(Original String, Starting Position, Chars to Replace, New String)
Original String A valid string value, representing the text to operate on.
Starting Position A numeric value equal to or greater than 0, representing the character position to begin with.
Chars to Replace A numeric value equal to or greater than 0, representing the number of characters to delete.
New String A string value, representing the characters to insert at position Num.
@REPLACE lets you replace characters in text with a new text string. It searches through the Original String from left to right beginning with the first character (character 0) until it reaches character position specified by Starting Position. Then it removes the number of characters from the string specified by Chars to Replace, replacing them with the New String.
Both the Original String and the New String can be either cell references or text strings. If text strings, they must be enclosed by double quotes.
To replace one string with another, specify 0 as the Starting Position. For Chars to Replace, enter a number equal to or greater than the number of characters in the Original String.
To insert one string into another string, specify 0 as the Chars to Replace.
To add one string to the end of another, specify as Starting Position a number one greater than the number of characters in the Original String.
To delete part or all of a string, specify "" as the New String.
@REPLACE("Leslie J. Cooper",7,3,"") = Leslie Cooper
@REPLACE("Sales Salaries",6,0, "Reps' ") = Sales Reps' Salaries (There must be a space between Reps and the final quotation mark.)
@REPLACE("355 Howard",11,0," St.") = 355 Howard St. (There must be a space between " and St.)
You can use @REPLACE with other string functions. For instance, to replace one word with another within a sentence, you can use @FIND and @LENGTH to simplify the search-and-replace operation. For example,
searches through A7 for man, then replaces man with person.
@RIGHT - Rightmost Characters
Syntax
@RIGHT(String, Num)
String A string value.
Num A numeric value │ 0.
@RIGHT returns Num characters of String counting from right to left. It extracts a specified number of characters from the right side of a string or label.
If String is a not a valid string, @RIGHT returns ERR. If Num is 0, the result is "", or an empty string. If Num is greater than or equal to the number of characters in String, the entire string is returned.
Examples
@RIGHT("Jennifer Meyer",5) = Meyer
@RIGHT("Jennifer Meyer",25) = Jennifer Meyer
@RIGHT("Jennifer ",6) = fer (including 3 subsequent spaces)
Form Style of Roman numeral, ranging from full classic to brief, becoming more concise as the value of Form increases:
FALSE (0) = Classic; default, if omitted
TRUE (1) = More concise
2 = More concise
3 = More concise
4 = Most concise
@ROMAN returns the Roman numeral corresponding to a specified Arabic numeral, displaying it as text.
@ROMAN returns ERR if Number is negative or greater than 3999.
Examples
@ROMAN(1999) = MCMXCIX (classic)
@ROMAN(1999,FALSE) = MCMXCIX
@ROMAN(1999,0) = MCMXCIX
@ROMAN(1999,TRUE) = MLMVLIV
@ROMAN(1999,1) = MLMVLIV
@ROMAN(1999,2) = MXMIX
@ROMAN(1999,3) = MVMIV
@ROMAN(1999,4) = MIM
@ROOTN - Nth Root of a Number
Syntax
@ROOTN(Number, Root)
Number Number; can be positive or negative.
Root Number, not zero.
@ROOTN calculates the nth root of a specified number. @ROOTN returns ERR if N=0.
Examples
@ROOTN(27,3) = 3
@ROOTN(64,2) = 8
@ROUND
Syntax
@ROUND(X, Num)
X A numeric value.
Num A numeric value between -15 and 15.
@ROUND adjusts the precision of X to Num decimal places. Num specifies the power of 10 to which X is rounded. If Num is positive, X is rounded Num digits to the right of the decimal point. If Num is negative, X is rounded Num digits to the left of the decimal point. For example, if Num is -3, X is rounded to the nearest thousand.
If Num is 0, X is rounded to an integer. If Num is not an integer, it is truncated to an integer.
Examples
@ROUND(12345.54321,0) = 12346
@ROUND(12345.54321,2) = 12345.54
@ROUND(12345.54321,-2) = 12300
@ROUNDDOWN - Round Number Down
Syntax
@ROUNDDOWN(X, <Digits>, <Direction>)
X Number to round down.
Digits Number of digits (optional) to which you want to round X.
Direction Argument (optional) specifying how to round negative values:
0 = round negative values down; default if omitted
1 = round negative values up
@ROUNDDOWN rounds a positive number down with a specified precision and rounds a negative number the direction you specify. @ROUNDDOWN behaves like @ROUND, except that it always rounds a number down.
If Digits is positive, X is rounded down to Digits decimal places.
If Digits is negative, X is rounded down to the nearest multiple of the power of 10 specified by Digits.
If Digits is 0 or omitted, X is rounded down to the nearest integer.
If X is positive, Direction has no effect.
Use the Fixed numeric format to display values with a specified number of decimal places if you want to calculate the values to their full precision; do not use @ROUNDDOWN. Quattro Pro stores a maximum of 15 digits.
Examples
@ROUNDDOWN(1234.5678) = 1234
@ROUNDDOWN(1234.5678,2) = 1234.56
@ROUNDDOWN(1234.5678,-2) = 1200
@ROUNDDOWN(-1234.5678) = -1235
@ROUNDDOWN(-1234.5678,0,1) = -1234
@ROUNDDOWNXL - Round Number Down
Syntax
@ROUNDDOWNXL(X, <Digits>)
X Number to round down.
Digits Number of digits to which you want to round X.
@ROUNDDOWNXL rounds positive and negative numbers toward zero.
If Digits is positive, X is rounded down to Digits decimal places.
If Digits is negative, X is rounded down to the nearest multiple of the power of 10 specified by Digits.
If Digits is 0 or omitted, then X is rounded down to the nearest integer.
Rounding is always to a lower absolute value, regardless of sign.
Examples
@ROUNDDOWNXL(1234.5678,0) = 1234
@ROUNDDOWNXL(1234.5678,2) = 1234.56
@ROUNDDOWNXL(1234.5678,-2) = 1200
@ROUNDDOWNXL(-1234.5678,0) = -1234
By contrast, @ROUNDDOWN rounds negative numbers down, if you omit its Direction argument:
@ROUNDDOWN(-1234.5678,0) = -1235
@ROUNDUP - Round Number Up
Syntax
@ROUNDUP(X, <Digits>, <Direction>)
X Number to round up.
Digits Number of digits (optional) to which you want to round X.
Direction Argument (optional) specifying how to round negative values:
0 = round negative values up; default if omitted
1 = round negative values down
@ROUNDUP rounds a positive number up with a specified precision and rounds a negative number the direction you specify. @ROUNDUP behaves like @ROUND, except that it always rounds a number up.
If Digits is positive, X is rounded up to Digits decimal places.
If Digits is negative, X is rounded up to the nearest multiple of the power of 10 specified by Digits.
If Digits is 0 or omitted, X is rounded up to the nearest integer.
If X is positive, Direction has no effect.
Use the Fixed numeric format to display values with a specified number of decimal places if you want to calculate the values to their full precision; do not use @ROUNDUP. Quattro Pro stores a maximum of 15 digits.
Examples
@ROUNDUP(1234.5678) = 1235
@ROUNDUP(1234.5678,2) = 1234.57
@ROUNDUP(1234.5678,-2) = 1300
@ROUNDUP(-1234.5678) = -1234
@ROUNDUP(-1234.5678,0,1) = -1235
@ROUNDUPXL - Round Number Up
Syntax
@ROUNDUPXL(X, <Digits>)
X Number to round up.
Digits Number of digits to which you want to round X.
@ROUNDUPXL rounds a positive number upward and rounds a negative number toward zero.
If Digits is positive, X is rounded up to Digits decimal places.
If Digits is negative, X is rounded up to the nearest multiple of the power of 10 specified by Digits.
If Digits is 0 or omitted, then X is rounded up to the nearest integer.
Rounding is always to a higher absolute value, regardless of sign.
Examples
@ROUNDUPXL(1234.5678,0) = 1235
@ROUNDUPXL(1234.5678,2) = 1234.57
@ROUNDUPXL(1234.5678,-2) = 1300
@ROUNDUPXL(-1234.5678,0) = -1235
By contrast, @ROUNDUP rounds negative numbers up, if you omit its Direction argument:
@ROUNDUP(-1234.5678,0) = -1234
@ROW - Row Number
Syntax
@ROW(<Block>)
Block The cell or cells for which you want the row number(s).
@ROW returns the row number(s) for a cell or cells.
Block can be a cell name.
If you omit Block, Quattro Pro assumes you want the row number of the cell where you entered @ROW.
Block cannot refer to non-contiguous areas.
Examples
@ROW(A5..F5) = 5
@ROW(K1..M5) = {1| 2| 3| 4| 5}
If A12.. F12 is a selection named GIFTS, @ROW(GIFTS) = 12
Entered in C4 without an argument, @ROW = 4
@ROWS
Syntax
@ROWS(Block)
Block Cell reference or name.
@ROWS returns the number of rows within the specified cells.
Examples
@ROWS(A1..A1) = 1
@ROWS(A1..C15) = 15
@ROWS(B100..B8192) = 8093
@ROWS(NAME) = 30 (if the cell NAME contains 30 rows)
@RSQ - r-Squared Value of Linear Regression
Syntax
@RSQ(KnownX, KnownY)
KnownX Independent range of values.
KnownY Dependent range of values.
@RSQ returns r squared, the square of the Pearson product moment correlation coefficient. KnownX and KnownY must have the same number of values. Use @RSQ to test the linear relationship of KnownX and KnownY and to show the proportion of the variance of KnownY that can be attributed to a variance in KnownX. The statistic measures the fraction of the variance explained by the regression equation.
