@GAMMA calculates the gamma function. It approximates the gamma function accurately to within six significant figures.
The gamma function has the property that @GAMMA(X+1) = X * @GAMMA(X). Or, @GAMMA(n+1) = n!
@GAMMA returns ERR if X is greater than approximately 171.6242.
Examples
@GAMMA(3) = 2
@GAMMA(4) = 6
@GAMMA(5) = 24
@GAMMADIST - Gamma Distribution
Syntax
@GAMMDIST(X, Alpha, Beta, Cum)
X Value at which to evaluate the function; must be │ 0.
Alpha Parameter to the gamma distribution; must be > 0.
Beta Parameter to the gamma distribution; must be > 0.
Cum 1 to return the cumulative gamma distribution function; 0 to return the probability density function.
@GAMMDIST returns the gamma distribution function, which is the probability that a random variable will be less than X. Use @GAMMADIST to study random variables characterized by skewed and asymmetric distributions.
When Alpha = 1, @GAMMADIST returns the exponential distribution; see @EXPONDIST.
Examples
@GAMMADIST(18,8,2,1) = 0.676103
@GAMMADIST(18,8,2,0) = 0.058558
@GAMMAINV - Inverse of Gamma Distribution
Syntax
@GAMMAINV(Prob, Alpha, Beta)
Prob Probability associated with the gamma cumulative function; must be │ 0 and ú 1.
Alpha A parameter to the gamma distribution; must be > 0.
Beta A parameter to the gamma distribution; must be > 0.
@GAMMAINV returns the inverse of the cumulative gamma distribution function.
Example
@GAMMAINV(0.676103,8,2) = 18
@GAMMALN - Natural Logarithm of Gamma Function
Syntax
@GAMMALN(X)
X Value for which you want to calculate @GAMMALN; must be > 0.
@GAMMALN returns the natural logarithm of the gamma function. Use @GAMMALN to build other common statistical functions such as the beta function (see @BETA) and the factorial function (see @FACT).
Example
@GAMMALN(6) = 4.787492
@GAMMAP - Incomplete Gamma Function
Syntax
@GAMMAP(A, X)
A Parameter to the function; must be > 0.
X Value at which to evaluate the function; must be │ 0.
@GAMMAP returns the incomplete gamma function, also known as the standard cumulative gamma distribution. @GAMMAP is equal to the cumulative gamma distribution when b = 1.
Example
@GAMMAP(3,4) = 0.761897
@GAMMAQ - Complement to Incomplete Gamma Function
Syntax
@GAMMAQ(A, X)
A Parameter to the function; must be > 0.
X Value at which to evaluate the function; must be │ 0.
@GAMMAQ is a complement to the incomplete gamma function and equals (1 - @GAMMAP).
Example
@GAMMAQ(3,4) = 0.238103
@GCD - Greatest Common Divisor
Syntax
@GCD(X, Y)
X Integer to find greatest common divisor of.
Y Integer to find greatest common divisor of.
@GCD returns the greatest common divisor of X and Y (the largest integer that both numbers can be divided by without a remainder.
@GCD should not be confused with the greatest common denominator which is @LCM.
Examples
@GCD(96,78) = 6
@GCD(112,42) = 14
@GCD(-9,-3) = 3
@GEOMEAN - Geometric Mean
Syntax
@GEOMEAN(List)
List One or more numeric or values; values in List must be positive.
@GEOMEAN returns the geometric mean of a positive range of values. The geometric mean is the nth root of the product of a series of numbers. Use @GEOMEAN when you are interested in an average rate of change of values in a data set given a varying rate of change.
@GEOMEAN uses this formula:
Example
@GEOMEAN(3,4,5,6,7) = 4.789389
@GEOMEAN(C10..C14) = 1.129486, where C10=1.15, C11=1.08, C12=1.13, C13=1.18, and C14=1.11
@GEOSUM - Geometric Series
Syntax
@GEOSUM(FirstTerm, Terms, Ratio)
FirstTerm First term of the series.
Terms Number of terms in the series.
Ratio Common ratio of the series.
@GEOSUM calculates the geometric series that is sum of the terms of a geometric sequence of a number of terms (n) based on the first term and common ratio. The notion of a geometric series is the basis of the mathematical model of an annuity. @GEOSUM uses the formula:
where
s geometric series
a first term
r common ratio
n number of terms
The formula assumes: r ╣ 1; if r = 1, @GEOSUM returns NA.
Examples
If you invest $2,000 at 3.5% interest compounded annually, the list of compound amounts at the end of each year for 5 years is
This is a geometric series with the common ration 1.035.
@GEOSUM(2000*1.035,5,1.035) = $11,100.30
@GESTEP - Test if X >= Y
Syntax
@GESTEP(X, <Y>)
X Numeric value to check.
Y Numeric value that X must exceed for function to return 1 (if omitted, assumed to be 0).
@GESTEP tests whether X is greater than or equal to Y. If it is, @GESTEP returns 1 (true); if not, @GESTEP returns 0 (false).
Examples
@GESTEP(1,2) = 0
@GESTEP(2,1) = 1
@GESTEP(1) = 1
@GESTEP(-2) = 0
You can sum several @GESTEP functions to count the number of values that exceed a certain threshold (Y).
@GETGROUP - Name of Group Containing Sheet
Syntax
@GETGROUP(Block, <PageName>)
Block A cell or cells of the notebook to check.
PageName A string value representing a sheet name or an address specifying the sheet name to check (optional).
@GETGROUP returns a string that is the name of the group that includes the sheet containing specified cells.
If Block is used in conjunction with the optional argument PageName, @GETGROUP searches the notebook referenced by Block for the group that contains the sheet specified by PageName. PageName is a string or cell address.
If the sheet is not part of a group, @GETGROUP returns ERR.
Example
@GETGROUP([REPORTQ4]A:C12,"April") searches the notebook REPORTQ4 for the name of the group that contains the sheet named April
@GETGROUP([REPORTQ4]A:C12) searches the notebook REPORTQ4 for the name of the group that contains the sheet named A
@GETREGISTRYKEY
Syntax
@GETREGISTRYKEY(Registry Key, Registry Value)
Registry Key A string value representing the path in the registry.
Registry Value A string value representing the stored value in the registry, at the specified path.
@GETREGISTRYKEY returns the value of the specified key in the registry.
You can use @GETREGISTRYKEY to open a file specified in the registry.
Example
{FileOpen +@GETREGISTRYKEY("HKEY_LOCAL_MACHINE\SOFTWARE\Corel\QuattroPro\9\Location of Files\EN","Template Folder")&"\amortize.qpw"}
@GRANDTOTAL123 - Sum of Subtotals
Syntax
@GRANDTOTAL123 (List)
List Any combination of cells; separated by valid argument separators.
@GRANDTOTAL123 sums all cells in a designated area that contain @SUBTOTAL123 in their formulas.
Example
In the following, Cells A3 and C3 contain @SUBTOTAL123(A1..A2) and @SUBTOTAL123(C1..C2), respectively. Cell A4 contains the formula @GRANDTOTAL123(A1..C3) and sums only the subtotals in the cells. To omit possible subtotals in Column B, you could also write @SUM(A1..A2,B1..B2).
A B C D
1 $30 $18
2 $65 $22
3 $95 $40 @SUBTOTAL123 in A3 and C3
4 $135 @GRANDTOTAL123 in A4
@GROWTH - Fits Exponential Curve to Data
Syntax
@GROWTH(KnownYs, <KnownXs>, <NewXs>, <Const>)
KnownYs Array of known y-values for the curve y = b*m^x.
KnownXs Array of known x-values (optional).
NewXs Array of new x-values for which you want the corresponding y-values (optional).
Const Logical value (optional) that tells @GROWTH whether to force the constant b = 1:
If Const is TRUE or omitted, @GROWTH uses the actual value of b.
If Const is FALSE, @GROWTH sets b = 1, then adjusts the m-values so that y = m^x.
@GROWTH fits an exponential curve to the data KnownYx and KnownXs, and returns the y-values along that curve for the array of NewXs that you specify.
If known y-values are in one column, @GROWTH takes each column of known x-values to be a separate variable. If known y-values are in one row, @GROWTH takes each row of known x-values to be a separate variable.
If any of the known y-values are 0 or negative, @GROWTH returns ERR.
The argument KnownXs can include more than one set of variables. If you use only one variable, KnownYs and KnownXs can be cell areas of any shape, but must have the same dimensions. If you use more than one variable, KnownYs must be a single-column or single-row. Use commas to separate x-values in the same row and pipes ( | ) to separate rows.
The argument NewXs must follow the pattern of KnownXs: It must include a row or column for each independent variable. If you omit the argument NewXs, @GROWTH assumes it is the same as KnownXs. If you omit both KnownXs and NewXs, @GROWTH assumes they are the array {1, 2, 3,...} of a size equal to KnownYs.
Example
Sales for your company in its first four quarters are entered in a selection named Sales:
A B
1 Quarter Sales
2 1 $75,000
3 2 $90,000
4 3 $115,000
5 4 $140,000
To predict second-year sales, @GROWTH(Sales,A2..A5,A6..A9) = {$173,359, $214,246, $264,775, $327,222}
@HALFP
Syntax
@HALFP(String)
String The double-byte (full-width) character string
@HALFP converts a double-byte character string to a half-width (single-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.
@HALFP allows for converting double-byte character set (DBCS) alphanumeric characters to ASCII characters. In cases where there is no matching ASCII character, these characters are not converted and the results displays the same format as the original.
You cannot convert use @HALFP to convert double-byte Katakana characters to single-byte Katakana.
@HARMEAN - Harmonic Mean
Syntax
@HARMEAN(List)
List One or more numeric or cell values; none of the values in List can equal 0.
@HARMEAN returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of the reciprocals of a set of numbers.
@HARMEAN uses this formula:
Example
@HARMEAN(3,4,5,6,7) = 4.575163
@HEXTOASC - Hexadecimal to ASCII
Syntax
@HEXTOASC(Hex)
Hex Hexadecimal number to convert; can be up to 40 hexadecimal digits.
@HEXTOASC returns the ASCII equivalent of a hexadecimal number.
If the hexadecimal number includes nonnumeric characters, enclose it in quotation marks.
Examples
@HEXTOASC("2B") = +
@HEXTOASC("3031414243444546") = 01ABCDEF
@HEXTOASC("5155415454524F") = QUATTRO
@HEXTOBIN - Hexadecimal to Binary
Syntax
@HEXTOBIN(Hex)
Hex Hexadecimal number to convert.
@HEXTOBIN returns the binary string equivalent of a hexadecimal number. To convert a negative number, precede Hex with a minus sign.
Examples
@HEXTOBIN("A") = 1010
@HEXTOBIN("10") = 10000
@HEXTOBIN("1E") = 11110
@HEXTOBIN64 - Hexadecimal to Binary
Syntax
@HEXTOBIN64(Hex, <Places>)
Hex Hexadecimal number to convert, must be >0.
Places Number of characters to return; must be ú 64.
@HEXTOBIN64 returns the binary string equivalent of a hexadecimal number (up to 64 bits).
If the hexadecimal number includes nonnumeric characters, enclose it in quotation marks.
Examples
@HEXTOBIN64("A",2) = 10
@HEXTOBIN64("A",6) = 001010
@HEXTOBIN64("1E078") = 00011110000001111000
@HEXTOBIN64("1E078",7) = 1111000
@HEXTONUM - Hexadecimal to Decimal
Syntax
@HEXTONUM(Hex)
Hex A hexadecimal number enclosed by double quotes, either positive or negative.
@HEXTONUM converts the hexadecimal number in the string to the corresponding decimal value. @NUMTOHEX performs the opposite conversion, from decimal to hexadecimal.
Examples
@HEXTONUM("a") = 10
@HEXTONUM("10") = 16
@HEXTONUM("00FF") = 255
@HEXTONUM(A1) = 10 (if cell A1 contains the label 'a)
@HEXTONUM64 - Hexadecimal to Decimal
Syntax
@HEXTONUM64(Hex, <Signed>)
Hex Hexadecimal number to convert.
Signed 1 if the most significant bit of Hex is a sign bit; 0 if Hex is positive (the default is 0).
@HEXTONUM64 returns the decimal equivalent of a hexadecimal number (up to 64 bits).
If Signed is 1, the most significant bit of Hex is the sign bit. If the sign bit is 0, the number is positive; if it is 1, the number is negative.
If the hexadecimal number includes nonnumeric characters, enclose it in quotation marks.
X The numeric or string value you want to search for.
Block The range of cells.
Row The number of the referenced row. The rows are referenced from 0 to the number of rows in Block minus 1.
The first row (index row) in Block = 0
The second row in Block = 1, ...
Type <optional> Lets you specify whether or not the match must be exact.
0 Does not need to be an exact match
1 Must be an exact match
@HLOOKUP searches horizontally through the index row of Block for the value X. When @HLOOKUP finds the value X, it returns the value displayed Row rows beneath it.
All values in the index column must be sorted in ascending order for the function to work correctly. Otherwise, ERR or an incorrect answer may be returned.
@HLOOKUP returns 0 if the referenced cell is blank. ERR is returned if:
Row is less than 0 or greater than the number of rows minus 1 in Block.
X is less than the smallest value in the topmost row of Block.
X and the first row entries are string values and @HLOOKUP fails to find a match in the top row of Block.
X is a string or label and the index row entries are numeric values.
If X is a string, @HLOOKUP looks for an exact case-sensitive match. If X is a number and @HLOOKUP cannot find an equal number, it locates the highest number, not more than X, in the row.
If X is a number and the index row contains only labels, @HLOOKUP stops at the rightmost column.
Each cell of the index row must contain a value.
Examples
In the following example, @HLOOKUP searches across the index row (1) of the Block (A1..D4), looking for the largest number equal to or less than X (17). It stops at cell D1, then moves down Row rows (3). It stops at cell D4 and returns the value 47.
StartDate Number representing the start date. See "Using dates and times in Quattro Pro."
EndDate Number representing the end date.
Holidays Cells containing dates that are holidays; to indicate no holidays, enter an empty cell or cells.
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).
@HOLS returns the number of holidays between StartDate and EndDate, including the specified dates (if they appear in Holidays).
By default, @HOLS does not include holidays that fall on a Saturday or Sunday; if either Saturday or Sunday is passed as 1, the count also includes holidays falling on that day.
Example
This formula calculates the number of holidays between April 1, 1993 and December 14, 1993, assuming that the dates contained in selection A7..C9 are holidays.
@HOLS(@DATE(93,4,1),@DATE(93,12,14),A7..C9) = 5
@HOUR - Hour Portion of Date Serial Number
Syntax
@HOUR(DateTimeNumber)
DateTimeNumber A numeric value between -109571 and 474816.9999999, representing a date/time serial number: -109571 = January 1, 1600; 0 = December 31, 1899; 474816 = December 31, 3199; the decimal = time (24 hr).
See "Using dates and times in Quattro Pro."
@HOUR returns the hour portion of DateTimeNumber. DateTimeNumber must be a valid date/time serial number. Because only the decimal portion of a serial number pertains to time, the integer portion of the number is disregarded. The result is between 0 (12:00AM) and 23 (11:00PM).
To extract the hour portion of a string that is in time format (instead of serial format), use @TIMEVALUE with @HOUR to translate the time into a serial number. To return standard hours (1-12) instead of military hours (1-24), use @MOD with a parameter of 12.
SampleSuccess Successes in the sample; must be │ 0.
SampleSize Sample size; must be │ 0 and ú PopSize.
PopSuccess Successes in the population; must be │ 0 and ú PopSize.
PopSize Population size; must be │ 0.
@HYPGEOMDIST returns the hypergeometric distribution of a sample. It gives the probability of successes in a sample given the sample's size, the total population, and the number of successful trials in that population. Use @HYPGEOMDIST to determine the probability that a distribution contains exactly SampleSuccess items of a particular type.
SampleSuccess must be greater than or equal to 0, greater than the lesser of SampleSize or PopSuccess, and greater than the larger of 0 or (SampleSize-PopSize+PopSuccess)
@HYPGEOMDIST uses this formula:
where
d SampleSuccess
n SampleSize
D PopSuccess
N PopSize
Examples
Five cards are drawn from a deck of 52 playing cards. This formula calculates the probability that one of the five cards drawn is an ace (assuming there are only four aces in the deck):
@HYPGEOMDIST(1,5,4,52) = 0.299474
@IF - Perform Logical Test
Syntax
@IF(Cond, TrueExpr, FalseExpr)
Cond A logical expression representing the condition to be tested.
TrueExpr A numeric or string value representing the value to use if Cond is true.
FalseExpr A numeric or string value representing the value to use if Cond is false.
@IF evaluates the logical condition specified as Cond. If the condition is found to be true, it returns the value specified as TrueExpr. If the condition is false, it returns the value specified as FalseExpr. Cond is true if it evaluates to any nonzero numeric value.
The formula entered as Cond can be any logical expression that can be evaluated as true or false; for example, B6<0 or C3*D2=53.
You can use compound conditions by connecting expressions with #AND# or #OR#. If you use #AND#, both conditions specified must be met to evaluate true. If you use #OR#, the expression is true if either of the conditions is met. For example, A3<10#OR#A3>5 means that the value in A3 must be either less than 10 or greater than 5 to evaluate true--6,9,1, and 15 are all true; A3<10#AND#A3>5 means that the value in A3 must be between 5 and 10 to evaluate true. If A3 contains a label, the expression evaluates true because labels have a value of 0 (zero).
You can also use the #NOT# operator to negate a condition. For example, #NOT#(B3>10) evaluates true if B3 is not greater than 10.
TrueExpr and FalseExpr can be numbers, formulas resulting in numbers, or text. If text, the string must be enclosed by double quotes; for example, @IF(D6=5,"John","Harry "). You can also use cell references to use the contents of other cells in the notebook. For example, @IF(B10<18,D5,C4) enters the contents of D5 if the condition is true, and enters the contents of C4 if the condition is false.
If the condition you specify with Cond searches a cell for a number and the cell contains a label, the label is evaluated as having a value of 0 and FalseExpr is returned. Likewise, if you search for a label and find a numeric value, TrueExpr results if the value of the referenced cell is 0; FalseExpr results if it is nonzero.
Although logical expressions typically reference other cells, this is not required. Any expression resulting in a numeric value is accepted; for example, A1=1 or A1="Fred". If the result of Cond is nonzero, TrueExpr is the result; otherwise, FalseExpr is the result.
@IF statements can be nested, or used within one another. In other words, TrueExpr can contain yet another test to further validate Cond.
For example, @IF(B5>C6,@IF(B5>C7,1,2),3) tells Quattro Pro to see if the contents of B5 are greater than C6. If they are, it then checks to see if B5 is greater than C7; if so, it enters a 1 in the cell. If not, it enters a 2. If B5 is not greater than C6, it enters a 3. There is no limit on the number of levels @IF expressions that you can nest, as long as the entire expression does not exceed 1024 characters.
Examples
@IF(8=7,4,5) = 5
@IF(B4<100,"Yes","No") = Yes if B4 < 100; otherwise, No
@IF(C10=BLOCK,45,50) = 45 if C10 = the cell named BLOCK; otherwise, 50
@IF(C10,1,0) = 0 if C10 = 0; otherwise, 1
@IMABS - Absolute Value of Complex Number
Syntax
@IMABS(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy for which you want the absolute (modulus) value.
@IMABS returns the absolute value (modulus) of a complex number. It uses this formula:
Given Complex = x + yi
C = Complex
Example
@IMABS("-10+25.6i") = 27.48381
@IMAGINARY - Imaginary Coefficient of Complex Number
Syntax
@IMAGINARY(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy from which you want to extract the imaginary coefficient.
@IMAGINARY returns the imaginary coefficient of a complex number.
Examples
@IMAGINARY("2+8i") = 8
@IMAGINARY("-i") = -1
@IMARGUMENT - Angle of Complex Number
Syntax
@IMARGUMENT(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy for which you want to calculate the angle in the complex plane.
@IMARGUMENT returns the angle Q, in radians, of a number in the complex plane. It uses this formula:
Example
@IMARGUMENT("5+12i") = 1.176005
@IMCONJUGATE - Complex Conjugate of Complex Number
Syntax
@IMCONJUGATE(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy for which you want to calculate the complex conjugate.
@IMCONJUGATE returns the complex conjugate of a complex number.
Example
@IMCONJUGATE("5+12i") = "5-12i"
@IMCOS - Cosine of Complex Number
Syntax
@IMCOS(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy for which you want to calculate the cosine.
@IMCOS returns the cosine of the complex number Complex. @IMCOS uses this formula:
Given Complex = x + yi
C = Complex
Example
@IMCOS("5+12i") = "23083.7+78034.8i"
@IMDIV - Quotient of Complex Numbers
Syntax
@IMDIV(Complex1, Complex2)
Complex1 Complex numerator or dividend in the format x + yi, x + iy, x + yj, or x + jy.
Complex2 Complex denominator or divisor in the format x + yi, x + iy, x + yj, or x + jy.
@IMDIV returns the quotient of two complex numbers (Complex1 and Complex2). It uses this formula:
Given: Complex1 = a + bi and Complex2 = c + di
C = Complex
Example
@IMDIV("5+6i","3+4i") = "1.56-0.08i"
@IMEXP - Exponential of Complex Number
Syntax
@IMEXP(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy for which you want to calculate the exponential.
@IMEXP returns the exponential of a complex number. It uses this formula:
Given Complex = x + yi,
C = Complex
Example
@IMEXP("5+12i") = "125.239-79.6345i"
@IMLN - Natural Logarithm of Complex Number
Syntax
@IMLN(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy for which you want to calculate the natural logarithm.
@IMLN returns the natural logarithm of the complex number Complex. It uses this formula:
Example
@IMLN("5+12i") = "2.56495+1.17601i"
@IMLOG10 - Base 10 Logarithm of Complex Number
Syntax
@IMLOG10(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy for which you want to calculate the base 10 log.
@IMLOG10 returns the base 10 (common) logarithm of the complex number Complex. It uses this formula:
Given Complex = x + yi
C = Complex
Example
@IMLOG10("5+12i") = "1.11394+0.510733i"
@IMLOG2 - Base 2 Logarithm of Complex Number
Syntax
@IMLOG2(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy for which you want to calculate the base 2 log.
@IMLOG2 returns the base 2 logarithm of the complex number Complex. It uses this formula:
Given Complex = x + yi
C = Complex
Example
@IMLOG2("5+12i") = "3.70044+1.69662i"
@IMPOWER - Complex Number Raised to a Power
Syntax
@IMPOWER(Complex, Power)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy.
Power The power to which you want to raise Complex; can be a complex number in the format x + yi, x + iy, x + yj, or x + jy.
@IMPOWER returns the complex number Complex raised to the power Power. Power can be a value or a complex number.
Examples
@IMPOWER("5+12i",3) = "-2035-828i"
@IMPOWER("5+12i","3+2i") = "-150.575+145.094i"
@IMPRODUCT - Product of Complex Numbers
Syntax
@IMPRODUCT(Complex1, Complex2)
Complex1 Complex number in the format x + yi, x + iy, x + yj, or x + jy.
Complex2 Complex number in the format x + yi, x + iy, x + yj, or x + jy.
@IMPRODUCT returns the product of two complex numbers (Complex1 and Complex2). It uses this formula:
Given Complex1 = a + bi and Complex2 = c + di
(Complex1)(Complex2) = (ac - bd) + (ad + bc)i
Example
@IMPRODUCT("5+12i","2-i") = "22+19i"
@IMPRODUCT("10+2i",5) = "50+10i"
@IMREAL - Real Coefficient of a Complex Number
Syntax
@IMREAL(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy from which you want to extract the real coefficients.
@IMREAL returns the real coefficient of a complex number.
Examples
@IMREAL("2+8i") = 2
@IMREAL("-i") = 0
@IMSIN - Sine of a Complex Number
Syntax
@IMSIN(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy for which you want to calculate the sine.
@IMSIN returns the sine of the complex number Complex. It uses this formula:
Given Complex = x + yi
C = Complex
Examples
@IMSIN("5+12i") = "-78034.8+23083.7i"
@IMSIN("1+i") = "1.29846+0.634964i"
@IMSQRT - Square Root of a Complex Number
Syntax
@IMSQRT(Complex)
Complex Complex number in the format x + yi, x + iy, x + yj, or x + jy to calculate square root of.
@IMSQRT returns the square root of a complex number. It uses this formula:
Given Complex = x + yi
C = Complex
Example
@IMSQRT("5+12i") = "3+2i"
@IMSUB - Difference of Complex Numbers
Syntax
@IMSUB(Complex1, Complex2)
Complex1 Complex number in the format x + yi, x + iy, x + yj, or x + jy from which to subtract Complex2.
Complex2 Complex number in the format x + yi, x + iy, x + yj, or x + jy to subtract from Complex1.
@IMSUB returns the difference of two complex numbers (Complex1 and Complex2). It uses this formula:
Given Complex1 = (a + bi) and Complex2 = (c + di)
(a + bi) - (c + di) = (a - c) + (b - d)i
Example
@IMSUB("5+12i","2-i") = "3+13i"
@IMSUM - Sum of Complex Numbers
Syntax
@IMSUM(List)
List One or more complex numbers in the format x + yi, x + iy, x + yj, or x + jy, separated by commas.
@IMSUM returns the sum of a list of complex numbers. It uses this formula:
Given Complex1 = (a + bi) and Complex2 = (c + di)
Complex1 + Complex2 = (a + c) + (b + d)i
Example
@IMSUM("5+12i","7+14i") = "12+26i"
@INDEX - Return Value from Table Index
Syntax
@INDEX(Block, Column, Row, <Page>)
Block A cell reference or name.
Column The number of the referenced column, from 0 to 255 (the first column in Block = 0, the second = 1, and so on).
Row The number of the referenced row; if an offset, the first row in Block = 0, the second = 1, and so on.
Page The number of the referenced sheet, from 0 to 255 (the first sheet in Block = 0, the second = 1, and so on).
@INDEX searches through the table specified as Block and returns the value specified with the Column, Row, and optional Page values. The upper left cell in Block is column 0, row 0. Likewise, the first sheet is 0. The Column and Row values are not the actual coordinates of the resulting cell, but instead are offset values. In other words, @INDEX begins in the top left cell of the specified cells, moves right the number of columns specified by Column, moves down the number of rows specified by Row, and through the number of sheets specified by Page (if you have specified a Page). It then returns the value in the current cell.
The Column, Row, and Page values must be numbers equal to or greater than zero and less than the number of rows, columns, or sheets in the cells. If a fractional number is used (for example, 2.35), the fractional part is dropped (not rounded).
@INDEXTOLETTER - Letter(s) Corresponding to Sheet/Column Index
Syntax
@INDEXTOLETTER(Index)
Index An integer number from 0 to 18277 inclusive.
@INDEXTOLETTER returns a one-, two-, or three-character string equivalent (for example, A, B, AA, AB, and ZZZ) for the index number of a sheet or column.
If Index is < 0 or > 18277, @INDEXTOLETTER returns ERR. If Index is not an integer, it is rounded to the nearest integer.
Examples
@INDEXTOLETTER(0) = A
@INDEXTOLETTER(1) = B
@INDEXTOLETTER(18277) = ZZZ
@INT - Integer
Syntax
@INT(X)
X A numeric value.
@INT drops the fractional portion of X, returning its integer value. @ROUND rounds X to the nearest integer.
Examples
@INT(499.99) = 499
@INT(0.1245) = 0
@INT(-2.3) = -2
@INT(C4) = 5 if C4 contains a value between 5 and 6
@INTERCEPT - Y-Intercept
Syntax
@INTERCEPT(KnownY, KnownX)
KnownY Dependent range of values.
KnownX Independent range of values.
@INTERCEPT returns the y-intercept of the linear regression line through two data sets. KnownY and KnownX must contain the same number of values. The formula @INTERCEPT uses is
Settle Number representing the settlement date; must be < Maturity.
Maturity Number representing the maturity date; must be > Settle.
Investment Amount invested; must be > 0.
Redemption Redemption value; must be > 0.
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@INTRATE returns the simple annualized yield for a fully invested security. @INTRATE computes yield using this formula:
Y yield
R redemption
I investment
b basis
M maturity
S settle
tb is the number of days over which the discount rate applies (360 or 365).
Example
This formula calculates the interest rate for a bond with the following terms: Settle is November 11, 1995, Maturity is May 27, 1996, Investment is $10,000, Redemption is $10,397.50, and Calendar is 1 (actual/actual).
Bits Number of binary bits used for both input and output; if omitted, Bits = number of bits in Binary; must be in the range 0 <n ú 64.
@INVB inverts the bits of a binary number. All bits that are 1 change to 0, and all bits that are 0 change to 1.
Examples
@INVB(0) = 1
@INVB(1010,5) = 10101
@INVB(1100,5) = 10011
@INVH - Hexadecimal Bit Inversion
Syntax
@INVH(Hex, <Bits>)
Hex Hexadecimal number.
Bits Number of binary bits used for both input and output; if omitted, Bits = number of bits in Hex; 4 binary digits = 1 hexadecimal digit; must be in the range 0 <n ú 64.
@INVH inverts the binary bits of a hexadecimal number. All bits that are 1 change to 0, and all bits that are 0 change to 1.
Example
@INVH("A") = 5
@INVH("C",8) = F3
@INTXL - Integer
Syntax
@INTXL(X)
X A numeric value.
@INTXL rounds X down to an integer value. @ROUND rounds X to the nearest integer. @INT drops the fractional portion of X, returning its integer value.
Examples
@INTXL(499.99) = 499
@INTXL(0.1245) = 0
@INTXL(-2.3) = -3
@INTXL(D4) = -6 if D4 contains a value between -5 and -6
@INTXL(C4) = 5 if C4 contains a value between 5 and 6
@IPAYMT - Interest Portion of Payment
Syntax
@IPAYMT(Rate, Per, Nper, Pv, <Fv>, <Type>)
Rate A numeric value > -1, representing the periodic interest rate (the fixed interest rate per compounding period).
Per The number of the loan period for which the interest is desired (where Nper is the total number of periods).
Nper A numeric value > 0, representing the number of periods of the loan (the number of payments to be made) or investment (the number of compounding periods).
Pv A numeric value representing the amount borrowed (the principal).
Fv A numeric value representing the future value of an investment (the value the investment will reach at some point).
Type An optional numeric value that indicates whether payments or cash flows occur at the beginning (1) or the end (0) of the period; default = 0.
@IPAYMT and @PPAYMT tell what portion of a particular loan payment is interest and what portion is principal, respectively. For each month in the transaction period,
@IPAYMT is calculated by computing the simple interest on the outstanding principal from the previous month. @PPAYMT then gives the principal portion of the payment for the current month, and is computed by subtracting @IPAYMT from @PAYMT. The calculation starts by using Pv as the outstanding principal at the beginning of the first month:
The last two arguments, Fv and Type, are optional. If you omit one or both of them, their values are assumed to be zero.
Examples
If you are two years into a 30-year, 10% mortgage on a $100,000 loan and your interest payment is tax-deductible, then @IPAYMT(.1/12,2*12,30*12,100000) returns your current month's deduction: -824.03.
@IRATE - Interest Rate
Syntax
@IRATE(Nper, Pmt, Pv, <Fv>, <Type>)
Nper A numeric value > 0, representing the number of periods of the loan (the number of payments to be made) or investment (the number of compounding periods).
Pmt A numeric value representing the amount of the periodic payment.
Pv A numeric value representing the current value of an investment (the present value).
Fv A numeric value representing the future value of an investment (the value the investment will reach at some point).
Type An optional numeric value that indicates whether payments or cash flows occur at the beginning (1) or the end (0) of the period; default = 0.
@IRATE calculates the interest rate required to pay off a principal (Pv) or reach an investment goal (Fv) in Nper payments of Pmt amount.
The last two arguments, Fv and Type, are optional. If you omit one or both of them, their values are assumed to be zero.
@IRATE requires that the initial cash flow (Pv + Type * Pmt) and the last cash flow (Fv + (1-Type) * Pmt) have opposite signs. Otherwise, @IRATE returns ERR because the transaction is not simple and there may not be a meaningful rate.
@IRATE is not compatible with 1-2-3. If your file must be compatible with 1-2-3, use @RATE instead.
Be sure to enter a negative number for money that is out of your pocket and a positive number for money that's coming in to you.
Examples
Assume you are negotiating to buy a $15,000 new car. The salesperson says you can have the car for $500 a month for the next five years. To calculate the monthly percentage rate:
@IRATE(5*12,-500,15000,0,0) = 0.02632
Another example: Assume that you plan to deposit $2000 a year into a savings account that currently contains only $2.38. What interest rate must the account earn to generate $15,000 at the end of 5 years? Use this formula:
@IRATE(5,-2000,-2.38,15000,0) = 0.203773
@IRR - Internal Rate of Return
Syntax
@IRR(Guess, Block)
Guess A numeric value that estimates the internal rate of return on an investment.
Block Cells (reference or name) containing cash flow information for the investment.
@IRR determines the internal rate of return on an investment. It references cells in your notebook that contains cash flow information and uses the supplied internal rate of return estimate to calculate the results.
Before using @IRR, you must set up a cash flow table, showing expected cash flow amounts over a period of time. Quattro Pro assumes that the amounts are received at regular intervals. Negative amounts are interpreted as cash outflows, and positive amounts as inflows. The first amount must be a negative number, to reflect the initial investment. These amounts can all be the same for each time period, or they can be different (including a mixture of negatives, positives, or zeros).
@IRR always returns ERR or a rate of return greater than or equal to -1. Some cash flows have no rate of return, and some have several. If it can be determined that the cash flow has a unique rate of return, then the Guess is ignored and @IRR gives that unique value.
Quattro Pro can determine unique rates of return for simple transactions or cash flow. A simple cash flow has two sets of values: a series of nonpositive values (negative values and zero) that is your cash outflow, and a series of nonnegative values (positive values and zero) that is your cash inflow. A simple cash flow must contain both a negative value (cash outflow) and a positive value (cash inflow).
Simple values have a unique rate of return, so you can safely use @NA as the Guess argument in most cases. Quattro Pro then tries to determine whether the root is unique and returns that rate without using a Guess argument. If Quattro Pro cannot find a unique root value, @IRR returns ERR.
Typically, you make an investment (a negative cash flow) and then receive several dividends (positive cash flows). This is an example of a simple transaction, and @IRR gives the unique rate of return for this without requiring a Guess. More complex transactions, in which the direction of money changes several times, often do not have a meaningful value for @IRR. For more information, see @IRR with Multiple Rates of Return.
@IRR(Guess,Block) gives the number Rate which satisfies @NPV(Rate,Block,0) = 0. For a simple transaction, @NPV(@IRR(Block),Block,0) will give a number close to 0 (it may not be exactly 0 due to how numbers are rounded off).
Guess can be any value greater than -1. Values that are NA or less than or equal to -1 are ignored. Use @NA for Guess unless your cash flow has multiple rates of return. If you use @NA, you will get ERR if your cash flow has more than one rate of return, rather than the rate of return that happens to be near your Guess.
There is no Type parameter to @IRR because the rate of return is the same regardless of whether the payments are at the end or the beginning of each period.
Examples
A B C
1 3000 -50000 -10000
2 700 -8000 1000
3 600 2000 1000
4 750 4000 1200
5 900 6000 2000
@IRR(0,A1..A5) = -1
@IRR(0,B1..B5) = -38.09%
@IRR(0,C1..C5) = -19.90%
@IRR with Multiple Rates of Return
In unusual cases, @IRR may have as many as N-1 roots, where N is the number of terms in the cells. Consider the selection that has the values ( -10, +150, -145). @IRR(@NA,Block) returns ERR because it is not simple. The two roots are 3.86% and 1296%, obtainable from guesses of 0 and 10, respectively. Both of these values are meaningful, if interpreted properly.
Maybe you invested $10 in an oil well. It struck oil, paying you $150, but then it ran into legal difficulties and required you to pay back $145. You had a net loss of $5, but your rate of return is quite large, as you had the use of a relatively large amount of money for a small investment. Or, maybe the real purpose of the transaction was to get a $150 loan from the bank. The bank required you to pay a $10 application fee ahead of time. After you got the loan, you paid back $145. Because you only paid back $155 on a $150 loan, the interest rate is fairly low. The difference in these two interpretations is that in one you're the lender, and in the other you're the borrower.
If you find a transaction with two roots, there is a mechanical way to determine which is the lender rate and which is the borrower rate. Pick a positive term in the Block, and increase it by a small amount. If the rate increases, it is a lender rate, and if the rate decreases, it is a borrower rate.
If Block has the values (-10,+150,-145), @IRR(0,Block) = 3.86%
If Block has the values (-10,+150,-145), @IRR(10,Block) = 1296%
If Block has the values (-10,+150.1,-145), @IRR(0,Block) = 3.78%
If Block has the values (-10,+150.1,-145), @IRR(10,Block) = 1297%
Because 3.78% < 3.86% and 1297 > 1296%, it follows from this rule that 3.86% is a borrower rate and 1296% is a lender rate.
Most uses of @IRR are for analyzing an investment in which the first cash flow is negative, and the rate is a lender rate.
Some transactions have no rate of return at all. @IRR(Guess,Block), with Block having the values (-1,+1,-1), returns ERR regardless of the Guess. There is no rate of return that is meaningful for this cash flow.
If there are more than two roots, the above analysis can still be used to determine if a particular root is a lender rate or a borrower rate. In some cases, it might still be possible to assign meaning to a root, but it is much more likely that the transaction should be interpreted as several transactions, with a rate of return for each. For example, the cash flow (-1,+6,-11,+6) has three roots, 0%, 100%, 200%. It is difficult to interpret such a transaction in terms of interest rates, and the roots are sensitive to small fluctuations.
@ISBDAY - Business Day Test
Syntax
@ISBDAY(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).
@ISBDAY tests whether Date is a business day. To qualify as a business day, Date cannot fall on a Saturday or Sunday (unless Saturday and Sunday are designated as business days by Saturday and Sunday), and cannot appear in the cells specified by Holidays. If Date is a business day, @ISBDAY returns 1; otherwise, @ISBDAY returns 0.
Example
Given the cells of holidays, A7..C9, and treating Saturday as a business day (except when it is a date included in A7..C9), this formula tests whether May 31, 1993 is a business day:
@ISBDAY(@DATE(93,5,31),A7..C9,1) = 0, since May 31, 1993 is Memorial Day.
@ISBLANK - Blank Test
Syntax
@ISBLANK(Location)
Location Name or address of a cell.
@ISBLANK tests a specified cell to see if it is empty.
If the cell is empty, @ISBLANK returns 1 (true).
If the cell is not empty, @ISBLANK returns 0 (false).
Examples
A B
1 Profits
2 $90
@ISBLANK(A1) = 1
@ISBLANK(B1) = 0
@ISBLANK(A2) = 0
@ISBLANK(B2) = 1
@ISBLANK(A1..B2) = returns the array {1|0|0|1}
@ISBLOCK - Block Test
Syntax
@ISBLOCK(Block)
Block Cell address or presumed cell name.
@ISBLOCK tests input to see if it is a defined cell name or valid address. Valid addresses have sheet name A to IV, column letters A to IV, and row numbers 1 to 8192. @ISBLOCK searches only files in memory.
If Block is a defined cell name or valid address, @ISBLOCK returns 1 (true).
If Block is not a defined cell name or valid address, @ISBLOCK returns 0 (false).
You can use @ISBLOCK with @IF to find out if an entry is a valid cell name for subroutine calls and branching with {DISPATCH}.
Examples
@ISBLOCK(C3) = 1
@ISBLOCK(3) = 0
@ISBLOCK(C3..C5) = 1
@ISBLOCK(PROFITS) = 1, if PROFITS is the name of a selection
@ISBLOCK("PROFITS") = 0, because the cell name is enclosed in quotation marks; arguments to @IS functions are not converted from text
@ISERR - Error Test
Syntax
@ISERR(X)
X A cell address or expression.
@ISERR is normally used to check the contents of a cell for errors. If the cell contains ERR, 1 is returned; otherwise, 0 is returned. You can also use formulas or numeric values with @ISERR.
Examples
@ISERR(C2)=1 if C2 contains ERR; otherwise, 0
@ISERR(10/0)=1
@ISERR(45+C3)=1 if C3 is ERR; otherwise, 0
@ISERR(C2/B3)=1 if B3 is 0 or ERR, or if C2 is ERR; otherwise, 0
@IF(@ISERR(A2),0,A5)=0 if A2 is ERR; otherwise, it returns the value in A5
@ISEVEN - Even Number Test
Syntax
@ISEVEN(Number)
Number Value to test.
@ISEVEN returns 1 (true) if a specified number is even, 0 (false) if it is odd.
If Number is not an integer, it is truncated.
If Number is non-numeric, @ISEVEN returns ERR.
Examples
@ISEVEN(4) = 1
@ISEVEN(4.9) = 1
@ISEVEN(5) = 0
@ISEVEN(-5) = 0
@ISLEGALPAGENAME - Legal Sheet Name Test
Syntax
@ISLEGALPAGENAME(PageName)
PageName A string value.
@ISLEGALPAGENAME returns 1 if PageName is a valid sheet name (even if the sheet name does not currently exist). Otherwise, it returns 0.
Examples
@ISLEGALPAGENAME("A") = 1
@ISLEGALPAGENAME("1st Qtr") = 1
@ISLEGALPAGENAME("1st Qtr: Net Profit") = 0 (name contains a colon, an invalid character)
Files that contain custom spreadsheet names may not open in Quattro Pro if characters used in the names are not recognized by Quattro Pro. In this event, Quattro Pro displays a message warning you about the invalid spreadsheet name. You can change the spreadsheet name to make it compatible with Quattro Pro. Valid Quattro Pro characters include the following: ~ ` ! % _ | \ ' ?.
Spreadsheet names must not exceed 63 characters.
@ISLOGICAL - Logical Value Test
Syntax
@ISLOGICAL(Value)
Value Empty cell, logical value, text, number, ERR, cell reference, or cell name to test.
@ISLOGICAL tests if the value is a logical value (0,1, TRUE, FALSE).; it returns 0 (false) if its argument refers to any other number.
Examples
@ISLOGICAL(A1) = 1 if Cell A1 evaluates to either 0 or 1
@ISLOGICAL(5) = 0
@ISNA - NA Test
Syntax
@ISNA(X)
X A cell address or expression.
@ISNA tests for the special value NA in a cell. If the cell contains an NA value, it returns 1; otherwise, it returns 0. NA is considered a special value; it appears in the notebook only through the use of @NA. Cells containing the label "NA" typed directly (not produced by @NA) are not recognized by @ISNA.
Examples
@ISNA("NA") = 0
@ISNA(@NA) = 1
@ISNA(A18) = 1 if A18 contains NA produced by @NA
@ISNONTEXT - Nontext Test
Syntax
@ISNONTEXT(Value)
Value Empty cell, logical value, text, number, ERR, cell reference, or cell name to test.
@ISNONTEXT tests if the argument is not text. @ISNONTEXT also returns 1 if Value refers to an empty cell.
Examples
A B
1 Profits
2 8/31/95 "8/31/95"
Cell A1 above is empty; Cell A2 contains the formula @TODAY().
@ISNONTEXT(A1) = 1
@ISNONTEXT(B1) = 0
@ISNONTEXT(A2) = 1
@ISNONTEXT(B2) = 0
@ISNONTEXT(A1..B2) = returns the array {1|0|1|0}
@ISNUMBER - Number Test
Syntax
@ISNUMBER(X)
X A cell address or expression.
@ISNUMBER examines X and determines if it contains a numeric value. If X is blank or contains a numeric value, ERR, or NA, @ISNUMBER returns a 1. If X is a label or text, @ISNUMBER returns a 0. @ISNUMBER is usually used with @IF to determine whether an entry is a value.
Examples
@ISNUMBER(88) = 1
@ISNUMBER("88") = 0 (quotes signify a text string)
@ISNUMBER(9/15/87) = 1
@ISNUMBER(@ERR) = 1 (ERR and NA are numeric values)
@ISODD - Odd Number Test
Syntax
@ISODD(Number)
Number Value to test.
@ISODD returns 1 (true) if a specified number is odd, 0 (false) if it is even.
If Number is not an integer, it is truncated.
If Number is non-numeric, @ISODD returns ERR.
Examples
@ISODD(4) = 0
@ISODD(4.9) = 0
@ISODD(5) = 1
@ISODD(-5) = 1
@ISSTRING - String Test
Syntax
@ISSTRING(X)
X A cell address or expression.
@ISSTRING examines X and determines if it contains a label or text string. If X does (even if the string is empty), @ISSTRING returns 1. If X is blank or contains a numeric or date value, @ISSTRING returns 0.
Usually, @ISSTRING is used to test the contents of a cell. You can test any expression, however. Literal string arguments must be enclosed by double quotes.
Examples
@ISSTRING(55) = 0
@ISSTRING(2/5/88) = 0
@ISSTRING("Hello, world.") = 1
@ISSTRING("Hello, "&"world.") = 1
@ISSTRING("55") = 1
@ISSTRING(A15) = 1 if A15 contains a label or formula that results in a string, otherwise 0
@ISSTRING(A15&A16&"!!!") = 1 if A15 and A16 contain labels or formulas that result in strings
@ISSTRING("") = 1 ("" is an empty string)
@ISSTRING(@NA) = 0 (NA and ERR are considered numeric values)
