@S returns the string value of the upper left cell of Block. If that cell contains a numeric or date value or is blank, it returns "" (an empty string).
Quattro Pro transforms cells prefixed with an exclamation point (as used in 1-2-3) to a one-cell range (!C3 changes to C3).
Discrate Discount rate or cells containing discount rates corresponding to cells of cash flows.
Flows Cells containing cash flows.
Initial Initial cash flow (the default is 0).
Odd|Periods Delay between initial and first cash flow, in number of periods (the default is 1) or cells containing lengths of periods between cash flows (the default is 1).
Simp Flag specifying how to discount:
0 = compounded discounting (default)
1 = mixed compounded and simple discounting
2 = simple discounting
Pathdep Flag specifying whether to apply path-dependent compounding to each flow; 0 = no path (default); 1 = path.
Guess Initial margin for numerical search (useful for locating multiple roots) (must be > -100%; the default is 0).
Precision Minimum required precision (must be > 0; the default is 0.000001).
Maxiter Maximum number of iterations for search (must be > 0; the default is 50).
Start A starting cash flow amount to compare against individual flows.
End An ending cash flow amount to compare against individual flows.
@SCMARG computes the discount scenario margin, a value that must be added to the Discrate series so that the net present value of the cash flow series equals Npv. An initial guess is made for the margin and is refined until the difference between the computed net present value and Npv is less than Precision. If @SCMARG does not find a solution within Maxiter search iterations, it returns ERR.
If Discrate is a number, @SCMARG returns the difference between the internal rate of return and Discrate. If Discrate is a selection of discount rates, the margin is the amount to add to each discount rate to make the net present value equal Npv.
Example
A stream of cash flows is indexed off the current London InterBank Offer Rate (LIBOR). The time distances between flows is specified in cells C11..C15. The first flow is 1 period away, the second is 1.5 periods after that, and so on. If the current LIBOR is 8.5%, this formula calculates the margin to LIBOR if the net present value is $167,000 and compounded discounting is used:
X An angle measured in radians. X can be any value from approximately -9.00719E+15 through 9.00719E+15.
@SEC returns the secant of angle X, in radians. X must be specified in radians, not degrees. To convert degrees to radians, use @RADIANS.
In a right triangle, the secant of an acute angle is the ratio hypotenuse : side adjacent. Secant is the reciprocal of cosine.
Examples
@SEC(@RADIANS(60)) = 2
@SEC(@RADIANS(75)) = 3.863703
@SEC(@RADIANS(45)) = 1.414214
@SEC(@PI/3) = 2
@SECOND - Second Portion of Date Serial Number
Syntax
@SECOND(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."
@SECOND returns the second portion of DateTimeNumber. DateTimeNumber must be a valid date/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 and 59.
To extract the second portion of a string that is in time format (instead of serial format), use @TIMEVALUE with @SECOND to translate the time into a serial number. You can also use @TIME to enter a time value instead of a serial number.
Examples
@SECOND(.3655445) = 23
@SECOND(.2543222) = 13
@SECOND(35) = 0
@SECOND(@TIME(3,15,22)) = 22
@SECOND(@TIMEVALUE("10:08:45 am")) = 45
@SECOND(@TIMEVALUE("10:08 am")) = 0
@SECH - Hyperbolic Secant
Syntax
@SECH(X)
X A value from approximately -708.39599 to approximately 708.39599.
@SECH calculates the hyperbolic secant of X.
The hyperbolic secant is the reciprocal of the hyperbolic cosine. X must be specified in radians, not degrees. To convert degrees to radians, use @RADIANS.
@SECH returns a value greater than 0 or less than or equal to 1.
Examples
@SECH(@RADIANS(60)) = 0.624888
@SECH(@RADIANS(75)) = 0.503455
@SECH(@RADIANS(45)) = 0.75494
@SECH(@PI/3) = 0.624888
@SEMEAN - Standard Error of Sample Mean
Syntax
@SEMEAN(Block)
Block Cell reference or name.
@SEMEAN returns the standard error of the sample mean for values in specified cells.
Examples
@SEMEAN({5,3,7,8}) = 1.108678
Given cells H1.. H4 containing the values 4.0, 3.4, 3.7, and 3.6,
@SEMEAN(H1..H4) = 0.125
@SERIESSUM - Sum of a Power Series
Syntax
@SERIESSUM(X, N, M, Coefficients)
X Value in the power series.
N Initial power to raise X to.
M Increment of the power N for each successive term in the power series.
Coefficients Cells or array of one or more numeric values by which each power of X is multiplied; the number of values in Coefficients sets the number of terms in the power series.
@SERIESSUM returns the sum of a power series. It uses this formula for @SERIESSUM(X,N,M,A):
Example
If the cells A1..A3 contain the values 1, 2, and 3, then @SERIESSUM(0.5,1,2,A1..A3) = 0.84375
@SETSTRING - Label of Given Length
Syntax
@SETSTRING(Text, Length, <Alignment>)
Text Label text, in quotation marks.
Length Integer from 1 through 1022 specifying label length.
Alignment Optional argument specifying text alignment:
0 = align text left; default if you omit the argument
1 = center text
2 = align text right
@SETSTRING returns a label as long as the number of characters you specify. The label consists of the specified text plus enough blank spaces to total Length, aligning the text as you specify in Alignment.
If Length is smaller than the number of characters in Text, @SETSTRING still returns the entire text.
With proportionally spaced fonts, blank spaces are narrower than most letters.
Examples
Each dot represents a blank space in these examples; the dots are not displayed by Quattro Pro.
@SHEETS returns the number of sheets within the given cells. This is most often used with 3-D selections. @SHEETS always returns 1 for 2-D selections. This @function is similar to @COLS and @ROWS.
Examples
@SHEETS(B:A1..D:IV1) = 3
@SHEETS(A1..C7) = 1
@SHEETS(A..E:NAME) = 5
@SHLB - Binary Shift Left
Syntax
@SHLB(Binary, <ShiftBits>, <BitIn>, <Bits>)
Binary Binary number.
ShiftBits Number of bits to shift; 0 ú ShiftBits ú 64; the default is 1.
BitIn Binary bit inserted during the shift (can be 0, 1, "S" or "I"; "S" = same as the least significant bit before shifting; "I" = inverse of the least significant bit before shifting; the default is 0).
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.
@SHLB shifts the specified binary number left by ShiftBits bits. @SHLB inserts the BitIn bit at the least significant bit (LSB).
Examples
@SHLB(1000,1,0,5) = 10000
@SHLB(1000,1,1,6) = 010001
@SHLB(1000,2,1,6) = 100011
@SHLBO - Overflow of Binary Shift Left
Syntax
@SHLBO(Binary, <Bits>)
Binary Binary number.
Bits Number of input binary digits used during the shift operation; if omitted, Bits = the number of bits in Binary; must be in the range 0 <n ú 64.
@SHLBO returns the overflow bit (either 0 or 1) of the specified binary number after it has been shifted left by one bit.
An overflow occurs when a bit is shifted out of the word size specified by Bits. For example, if Binary = 1000 and Bits = 4, shifting the number left one bit results in 0000 with an overflow of 1 bit shifted to the fifth place not shown.
Examples
@SHLBO(1000) = 1
@SHLBO(100,4) = 0
@SHLBO(1100,4) = 1
@SHLH - Hexadecimal Shift Left
Syntax
@SHLH(Hex, <ShiftBits>, <BitIn>, <Bits>)
Hex Hexadecimal number.
ShiftBits Number of bits to shift; 0 ú ShiftBits ú 64; the default is 1.
BitIn Binary bit inserted during the shift (can be 0, 1, "S" or "I"; "S" = same as the least significant bit before shifting; "I" = inverse of the least significant bit before shifting; the default is 0).
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.
@SHLH shifts the specified hexadecimal number left by ShiftBits bits. @SHLH inserts the BitIn bit at the least significant bit (LSB).
Examples
@SHLH("41",1) = 82
@SHLH("41",1,0,12) = 082
@SHLH("C",1,1,12) = 019
@SHLHO - Overflow of Hexadecimal Shift Left
Syntax
@SHLHO(Hex, <Bits>)
Hex Hexadecimal number.
Bits Number of equivalent input binary digits used during the shift operation; if omitted, Bits = the number of bits in Hex; 4 binary digits = 1 hexadecimal digit; must be in the range 0 <n ú 64.
@SHLHO returns the overflow bit (either 0 or 1) of the specified hexadecimal number after it has been shifted left by one bit.
An overflow occurs when a bit is shifted out of the word size specified by Bits. For example, if the binary equivalent of Hex = 1000 and Bits = 4, shifting the number left one bit results in 0000 with an overflow of 1 bit shifted to the fifth place not shown.
Examples
@SHLHO("A") = 1
@SHLHO("A",5) = 0
@SHLHO("C",4) = 1
@SHRB - Binary Shift Right
Syntax
@SHRB(Binary, <ShiftBits>, <BitIn>, <Bits>)
Binary Binary number.
ShiftBits Number of bits to shift; 0 ú ShiftBits ú 64; the default is 1.
BitIn Binary bit inserted during the shift (can be 0, 1, "S" or "I"; "S" = same as the most significant bit before shifting; "I" = inverse of the most significant bit before shifting; the default is "S").
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.
@SHRB returns the result of shifting the specified binary number right by ShiftBits bits. @SHRB inserts the BitIn bit at the most significant bit (MSB).
Examples
@SHRB(1000,1) = 1100
@SHRB(1000,1,1,5) = 10100
@SHRB(1100,1,0,6) = 000110
@SHRBO - Overflow of Binary Shift Right
Syntax
@SHRBO(Binary, <Bits>)
Binary Binary number.
Bits Number of input binary digits used during the shift operation; if omitted, Bits = the number of bits in Binary; must be in the range 0 <n ú 64.
@SHRBO returns the overflow bit (either 0 or 1) of the specified binary number after it has been shifted right by one bit.
An overflow occurs when a bit is shifted out of the word size specified by Bits. For example, if Binary = 1001 and Bits = 4, shifting the number right one bit results in 0100 with an overflow of 1 bit shifted off the right side.
Examples
@SHRBO(1001) = 1
@SHRBO(10010,4) = 0
@SHRBO(1100,4) = 0
@SHRH - Hexadecimal Shift Right
Syntax
@SHRH(Hex, <ShiftBits>, <BitIn>, <Bits>)
Hex Hexadecimal number.
ShiftBits Number of bits to shift; 0 ú ShiftBits ú 64; the default is 1.
BitIn Binary bit inserted during the shift (can be 0, 1, "S" or "I"; "S" = same as the most significant bit before shifting; "I" = inverse of the most significant bit before shifting; the default is "S").
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.
@SHRH returns the result of shifting the specified hexadecimal number right by ShiftBits bits. @SHRH inserts the BitIn bit at the most significant bit (MSB).
Examples
@SHRH("41",1,1) = A0
@SHRH("41",1,1,4) = 8
@SHRH("C",1,0,12) = 006
@SHRHO - Overflow of Hexadecimal Shift Right
Syntax
@SHRHO(Hex, <Bits>)
Hex Hexadecimal number.
Bits Number of equivalent input binary digits used during the shift operation; if omitted, Bits = the number of bits in Hex; 4 binary digits = 1 hexadecimal digit; must be in the range 0 <n ú 64.
@SHRHO returns the overflow bit (either 0 or 1) of the specified hexadecimal number after it has been shifted right by one bit.
An overflow occurs when a bit is shifted out of the word size specified by Bits. For example, if the binary equivalent of Hex = 1001 and Bits = 4, shifting the number right one bit results in 0100 with an overflow of 1 bit shifted off the right side.
Examples
@SHRHO("A") = 0
@SHRHO("B",5) = 1
@SHRHO("C",4) = 0
@SIGN - Tests for Sign
Syntax
@SIGN(X)
X A numeric value.
@SIGN returns 1 if X is positive, 0 if X is zero, and -1 if X is negative.
Examples
@SIGN(2*4) = 1
@SIGN(0*4) = 0
@SIGN(-2*4) = -1
@SIN - Sine
Syntax
@SIN(X)
X A numeric value.
@SIN returns the sine of the angle X. X must be given in radians, not degrees. To convert degrees to radians, use @RADIANS.
Examples
@SIN(@RADIANS(30)) = 0.5
@SIN(@RADIANS(75)) = 0.965926
@SIN(@RADIANS(45)) = 0.707107
@SIN(@PI/6) = 0.5
@SINH - Hyperbolic Sine
Syntax
@SINH(X)
X A value from approximately -710.47558 to approximately 710.47558.
@SINH returns the hyperbolic sine of X, in radians. X must be specified in radians, not degrees. To convert degrees to radians, use @RADIANS.
Examples
@SINH(@RADIANS(30)) = 0.547853
@SINH(@RADIANS(75)) = 1.716184
@SINH(@RADIANS(45)) = 0.868671
@SINH(@PI/6) = 0.547853
@SKEW - Skewness of a Distribution
Syntax
@SKEW(List)
List One or more numeric or cell values.
@SKEW returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean value. Use @SKEW when you want a non-dimensional quantity to measure the "shape" of a distribution rather than its moment, which is a measure in the same units as the elements of the distribution. @SKEW finds the skewness coefficient using this formula:
A positive result means that the distribution is skewed to the right (the median is less than the mean). A negative result means that the distribution is skewed to the left (the median is greater than the mean). @SKEW returns 0 when the distribution is symmetrical around its mean.
If there are less than three data points in List, or if the standard deviation is zero, @SKEW returns ERR.
Example
@SKEW(4,5,8,5,7,12,6,9,2,5) = 0.685055
@SLOPE - Slope of the Linear Regression
Syntax
@SLOPE(KnownY, KnownX)
KnownY Dependent range of values.
KnownX Independent range of values.
@SLOPE returns the slope of the linear regression line through data points in x and y. The slope (the rate of change along the regression line) is the distance between the y values of two points, divided by the distance between their respective x values.
@SLOPE uses this formula:
Example
@SLOPE({1,2,3,4,5,6},{4,8,12,16,20,24}) = 0.25
@SLN - Straight-Line Depreciation
Syntax
@SLN(Cost, Salvage, Life)
Cost A numeric value representing the amount paid for an asset.
Salvage A numeric value representing the value of an asset at the end of its useful life.
Life A numeric value representing the expected useful life of an asset (in years).
@SLN calculates the straight-line depreciation allowance for an asset over one period of its life. It uses this formula:
(Cost - Salvage) divided by Life
To compute accelerated depreciation with the sum-of-the-years'-digits method (allowing higher depreciation values in the first years of the asset's life), use @SYD. To calculate depreciation using the double-declining balance method, use @DDB.
Examples
Assume you just bought a new $4000 computer. The dealer says you can sell it back to the store for $350 after eight years, but that no one would want to buy it after that. In other words, the Salvage value of that computer is $350 and its Life is 8. To determine the depreciation allowance of the computer for each year of its life, enter this formula:
@SLN(4000,350,8) = 456.25
Other examples:
@SLN(15000,3000,10) = $1,200
@SLN(5000,500,5) = $900
@SLN(1800,0,3) = $600
@SMALLEST - Nth Smallest number
Syntax
@SMALLEST(Array, N)
Array A numeric array or cells of values.
N Number that indicates the rank in size from the data set Array; must be greater than 0 and less than or equal to the number of values in Array.
@SMALLEST finds the Nth smallest number in Array. Use @SMALLEST to determine a value's rank in a data set from the bottom of that set.
If there are duplicates in Array, @SMALLEST treats them as separate numbers.
KnownX's Independent selection or array of values.
KnownY's Dependent selection or array of values.
OutputBlock The cell address where the result of the @function is to be displayed.
@SPLINE returns a polynomial fitted piecewise to pass through a specified set of points.
KnownX's and KnownY's must contain the same number of values.
Values in KnownX's must be unique: The same x-value cannot have more than one y-value. If an x-value has more than one y-value, @SPLINE returns NA.
@SPLINE takes the 1-dimenisional arrays of KnownX's and KnownY's and produces a set of coefficients in the OutputBlock. The coefficients produced are those for a linear spline.
A spline is an elastic ruler used by engineers and shipbuilders that bends to pass through a specified set of points.
Example
Given the following data,
A B C D E
1
2 1 2 3
3 4 2 5
4 -2 3
@SPLINE(C2..E2,C3..E3,C4..D4) = 1
@SQRT - Square Root
Syntax
@SQRT(X)
X A numeric value equal to or greater than 0.
@SQRT returns the square root of X. If X is a negative value, the result of @SQRT is ERR. If X is a string or reference to a cell containing a label, the @function returns 0.
Examples
@SQRT(9) = 3
@SQRT(2) = 1.414213562
@SQRT(144) = 12
@SQRT(@SQRT(16)) = 2
@SQRT(-4) = ERR
@SQRTPI - Square Root of pi*X
Syntax
@SQRTPI(X)
X Value │ 0 to multiply by pi.
@SQRTPI returns the square root of (@PI * X). If X is negative, @SQRTPI returns ERR.
Example
@SQRTPI(2) = 2.506628
@STANDARDIZE - Normalize Values from a Distribution
Syntax
@STANDARDIZE(X, Mean, SDev)
X Number to normalize.
Mean Arithmetic mean of a distribution.
SDev Standard deviation of a distribution.
@STANDARDIZE normalizes the values from a distribution. A standard normal cumulative distribution assumes a mean of 0 and a standard deviation of 1. Use @STANDARDIZE to normalize values for use with other statistical @functions that require normally distributed variables (such as @ZTEST).
@STANDARDIZE uses this formula:
Example
@STANDARDIZE(2.6,1.6,0.5) = 2
@STD - Population Standard Deviation
Syntax
@STD(List)
List One or more numeric values, cell addresses, and cell references or names, separated by commas.
@STD returns the population standard deviation (the square root of the population variance) of all values in List. @STDS computes the standard deviation of sample data.
List can be any combination of single cell references, cell selections, and numeric values. When more than one component is used, all components must be separated by commas. @STD treats any labels within a cell selection as zero and ignores any blank cells. If the List contains only blank cells, however, @STD returns ERR.
@STD determines how much individual values in List differ from the average (mean) of all values in List. It can be used to verify the reliability of the average; the lower the value returned by @STD, the less individual values vary from the average.
Examples
A B C D
1 January February March
2 John $652 $833 $599
3 Mary $456 $305 $522
4 Ralph $68 $59 $73
5 Anna $80 $80 $80
@STD(B4..D4) = $5.79
@STD(C2..C5,260) = $279.97
@STD(B2..D5) = $270.20
@STD(A15..D20) = ERR (because the entire selection is blank)
@STDS(B4..D4) = $7.09
@STDS(B2..D5) = $282.22
@STDS - Sample Standard Deviation
Syntax
@STDS(List)
List One or more numeric values, cell addresses, and cell references or names, separated by commas.
@STDS returns the sample standard deviation (the square root of the sample variance) of all values in List. @STD computes population standard deviation.
Examples
A B C D
1 January February March
2 John $652 $833 $599
3 Mary $456 $305 $522
4 Ralph $68 $59 $73
5 Anna $80 $80 $80
@STD(B4..D4) = $5.79
@STD(C2..C5,260) = $279.97
@STD(B2..D5) = $270.20
@STD(A15..D20) = ERR (because the entire selection is blank)
@STDS(B4..D4) = $7.09
@STDS(B2..D5) = $282.22
@STEC - Standard Error of Regression Coefficients
Syntax
@STEC(KnownY, KnownX)
KnownY Dependent range of 3 or more values.
KnownX Independent range of 3 or more values.
@STEC computes the standard error of the regression coefficient.
@STEC is equal to StdError divided by (Std(x) times square root of n)
@STEC returns ERR if KnownY and KnownX do not have the same number of values, or if KnownY and KnownX have less than 3 values each.
Example
This formula calculates the standard error of the regression coefficient for range y (3,7,4,5) in cells A1..A4 and range x (3.4,5.3,6,8) in cells B1..B4:
@STEC(A1..A4,B1..B4) = 0.593769
@STEYX - Standard Error of Linear Regression
Syntax
@STEYX(KnownY, KnownX)
KnownY Dependent range of 3 or more values.
KnownX Independent range of 3 or more values.
@STEYX returns the standard error of a linear regression. The standard error is the deviation of the observed y values from the linear combinations. @STEYX uses this formula:
If KnownY or KnownX have a different number of data points, or if the variance of KnownX = 0, @STEYX returns ERR.
OptCode Option code string with expiration month, strike-price, and put or call symbol enclosed in quotation marks (for example, "MAY 22.5 C"); the strike price can be a decimal or fractional number, but not both (for example, it can be 11/32 or 1.625, but not 2 3/8); valid month codes are JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, and DEC; you can add spaces between month, strike-price, and Put or Call symbol; the total string (not including quotation marks) must be 20 characters or less.
OptPrem Option premium or price
UndStkValue Value or price of the underlying stock.
Date Serial date number between 2 (January 1, 1900) and 73050 (December 31, 2099) representing the date on which to evaluate the stock option.
Load Load or commission involved in sale or purchase.
CmdString Command string enclosed in quotation marks specifying the operations to perform; cannot exceed 20 characters (not including quotes); see the table below for a list of valid CmdString characters.
@STKOPT calculates values useful in evaluating stock options. It takes as input the basic information for the option, such as the option code (expiration month, strike price, and call or put indicator), the premium, the underlying value, the date at which the option is evaluated, and the associated "load" or fee. It then calculates a value such as the expiration date of the option, the number of calendar days remaining until the option expires, or the intrinsic value of the option.
If the expiration month specified in OptCode is earlier in the year than the month specified by Date, the expiration date of the option occurs in the following year.
Load should be in the same units as OptPrem. You can use the Load argument as a general purpose value to incorporate any factor in the calculated result. For example, if you prefer a different annualization, such as 360/D, set Load to 360 and replace "A" in the CmdString formula with "L/D".
@STKOPT calculates output values according to a formula that you specify in the CmdString argument. The formula may include the following characters.
@STKOPT is valid only for options whose duration is 1 year or less
Character Description
A Annualization ratio (365.25/D); can be used to adjust percentages to equivalent yearly percentages
D Days from DateTimeNum until expiration of option specified
E Expiration date code of option specified
I Intrinsic value of option
L Load or fee to purchase or sell the option; can also be a general-purpose numeric constant
P Premium or market value of the option
S Strike or exercise price of the option
T Time value of the option
U Underlying value or price of the stock
+ Addition operator
- Subtraction operator
* Multiplication operator
/ Division operator
@STKOPT evaluates the command string formula from left to right with no operator precedence or associativity. The command string can be upper- or lowercase. The next table shows some examples of command strings.
String Result
"D" Outputs the number of days until option expiration
"I" Outputs the intrinsic value
"L/P" Outputs the ratio of the Load divided by the Premium
"T/U*A" Time Value / Underlying value * Annualization ratio
"P-L/U*A" ((Premium - Load) / Underlying value) * Annualization ratio
For a call, the intrinsic value is the underlying value minus the strike price or zero, whichever is greater. For a put, the intrinsic value is the strike price minus the underlying value or zero, whichever is greater. The time value is equal to the premium minus the intrinsic value. The load is an expense for either the buyer or the seller. A positive time value is considered to be an expense for the buyer and income for the seller of the option.
Examples
A November put-option on stock of company XYZ has a strike price of $65 and a premium of $2.625, with an average load of $.12 per share. XYZ's stock is currently trading at $67 per share, and the date of evaluation is June 8, 1993.
@STRCMPNORM compares two strings. @STRCMPNORM also lets you compare single-byte and double-byte character strings 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.
The result returned by @STRCMPNORM is:
1 for equal (indicates both the strings are the same)
0 for not equal (indicates that the strings are different)
-1 for an error (indicates a problem with the function or with the string or cells selected)
Example
If you have strings in cells A1 and B1, type @STRCMPNORM(A1, B1) in cell C1.
@STRING
Syntax
@STRING(X, DecPlaces)
X A numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.
DecPlaces A numeric value from 0 through 15.
@STRING converts X to a string, rounding X to the decimal precision indicated by DecPlaces.
Once a number or date has been converted to a label using @STRING, no display formatting can be done with it. To format strings derived from numbers as anything other than General format, you must build a macro that uses the {CONTENTS} keyword.
Examples
@STRING(3.59,0) = 4
@STRING(98.6,2) = 98.60
@STRING(0.3902,0) = 0
@STRING("Harry",0) = 0
@STRING(A1,2) = 10.00, if A1 contains the value 10
@SUBB - Subtract Binary Numbers
Syntax
@SUBB(Binary1, Binary2, <BitIn>, <Bits>)
Binary1 First binary number.
Binary2 Second binary number.
BitIn Input borrow bit (either 0 or 1); the default is 0.
Bits Number of binary bits used for both input and output; if omitted, Bits = number of bits in Binary1 or Binary2, whichever is greater; must be in the range 0 <n ú 64.
@SUBB returns the difference of two binary numbers.
Use two's complement notation (see Quattro Pro glossary) to represent negative numbers. If BitIn is 1, @SUBB subtracts one extra bit from the result.
Examples
@SUBB(100,100,0,1) = 0
@SUBB(1000,100,1,3) = 011
@SUBB(1100,1,1,5) = 01010
@SUBBO - Overflow of Binary Subtraction
Syntax
@SUBBO(Binary1, Binary2, <BitIn>, <Bits>)
Binary1 First binary number.
Binary2 Second binary number.
BitIn Input borrow bit (either 0 or 1); the default is 0.
Bits Number of input binary digits used for subtraction; if omitted, Bits = the number of bits in Binary1 or Binary2, whichever is greater; must be in the range 0 <n ú 64.
@SUBBO returns the overflow bit (either 0 or 1) of the difference of two binary numbers. An overflow occurs when a bit is borrowed from outside the word size specified by Bits. For example, if Binary1 = 10 and Binary2 = 110, the difference of the two numbers is 100 with 1 borrow bit in the fourth place not shown.
Use two's complement notation (see Quattro Pro glossary) to represent negative numbers. If BitIn is 1, @SUBBO subtracts one extra bit from the difference of the two numbers before returning the overflow.
Examples
@SUBBO(1000,1111) = 1
@SUBBO(1000,111,1,5) = 0
@SUBBO(1100,1101,1,4) = 1
@SUBH - Subtract Hexadecimal Numbers
Syntax
@SUBH(Hex1, Hex2, <BitIn>, <Bits>)
Hex1 First hexadecimal number.
Hex2 Second hexadecimal number.
BitIn Input borrow bit (either 0 or 1); the default is 0.
Bits Number of binary bits used for both input and output; if omitted, Bits = number of bits in Hex1 or Hex2, whichever is greater; 4 binary digits = 1 hexadecimal digit; must be in the range 0 <n ú 64.
@SUBH returns the difference of two hexadecimal numbers.
Use two's complement notation (see Quattro Pro glossary) to represent negative numbers. If BitIn is 1, @SUBH subtracts one extra bit from the result.
Examples
@SUBH("1000","100",1) = 0EFF
@SUBH("1000","100",0) = 0F00
@SUBH("C","1",1,8) = 0A
@SUBHO - Overflow of Hexadecimal Subtraction
Syntax
@SUBHO(Hex1, Hex2, <BitIn>, <Bits>)
Hex1 First hexadecimal number.
Hex2 Second hexadecimal number.
BitIn Input borrow bit (either 0 or 1); the default is 0.
Bits Number of binary bits used for both input and output; if omitted, Bits = number of bits in Hex1 or Hex2, whichever is greater; 4 binary digits = 1 hexadecimal digit; must be in the range 0 <n ú 64.
@SUBHO returns the overflow bit (either 0 or 1) of the difference of two hexadecimal numbers. An overflow occurs when a bit is borrowed from outside the word size specified by Bits. For example, if the binary equivalent for Hex1 and Hex2 are 10 and 110, respectively, the difference of the two numbers is 100 with 1 borrow bit in the fourth place not shown.
Use two's complement notation (see Quattro Pro glossary) to represent negative numbers. If BitIn is 1, @SUBHO subtracts one extra bit from the difference of the two numbers before returning the overflow.
Text Text or reference to single cell containing OldText.
OldText Text to be changed.
NewText Text to substitute for OldText.
InstanceNum Which occurrence of OldText to change. If you specify InstanceNum, @SUBSTITUTE changes only that instance. Otherwise, @SUBSTITUTE changes all occurrences.
@SUBSTITUTE returns a copy of a specified text string, substituting new text for old text. Use @SUBSTITUTE to replace specific text in a text string; to replace any text in a specific location in a text string, use @REPLACE.
Example
To copy "11/1/96" but change it to "11/8/96", enter @SUBSTITUTE("11/1/96","1","8",3)
@SUBTOTAL - Subtotal
Syntax
@SUBTOTAL(FunctionNum, Ref)
FunctionNum Number from 1 to 11, specifying which function to use in calculating subtotals.
Ref List of selections or cell names to subtotal.
@SUBTOTAL returns a subtotal in a list or database. You can create a list using @SUBTOTAL, then modify it by editing the @SUBTOTAL formula.
@SUBTOTAL ignores any nested subtotals within Ref.
@SUBTOTAL ignores any hidden rows in filtered lists, so you can subtotal only visible data.
FunctionNum Quattro Pro Function
1 PUREAVG
2 PURECOUNT
3 COUNT
4 PUREMAX
5 PUREMIN
6 MULT
7 PURESTDS
8 PURESTD
9 SUM
10 PUREVARS
11 PUREVAR
Examples
In the following, Cells A3 and C3 contain @SUBTOTAL(9,A1..A2) and @SUBTOTAL(9,C1..C2), respectively. Cell A4 contains the formula @TOTAL(A1..C3) and sums all the values in the cells except those generated by @SUBTOTAL or @SUBTOTAL123. 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 @SUBTOTAL in A3 and C3, FunctionNum=9
4 $135 @TOTAL in A4
In the next selection, rows 9 through 12 contain @SUBTOTAL functions, illustrating the way they are ignored in subsequent @SUBTOTAL calculations.
A B
5 28 37
6 31 35
7 29 36
8 32 40
9 120 148
10 268
11 33.5
12 4.174754
A9 and B9 contain @SUBTOTAL functions summing rows 5 through 8 of their respective columns.
A10 contains @SUBTOTAL(9,A5..B9) = 268, the sum of the 8 values in A5.. B8, ignoring the @SUBTOTAL functions in row 9.
A11 contains @SUBTOTAL(1, A5..B10) = 33.5, the average of the 8 values in A5.. B8, ignoring all other @SUBTOTAL functions in the referenced cells.
A12 contains @SUBTOTAL(7, A5..B11) = 4.174754, the sample standard deviation of the 8 values in A5.. B8, ignoring all other @SUBTOTAL functions in the cells.
@SUBTOTAL123 - Subtotal
Syntax
@SUBTOTAL123(List)
List One or more numeric values, cell addresses, and cell references or names, separated by commas.
@SUBTOTAL123 adds the values in a list or cell reference. Use @SUBTOTAL123 to indicate which cells @GRANDTOTAL123 should sum.
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
@SUM
Syntax
@SUM(List)
List One or more numeric values, cell addresses, and cell references or names, separated by commas.
@SUM returns the total of all numeric values in List. List can be any combination of single cell references, cell selections, and numeric values. When more than one component is used, they must be separated by commas. Any labels or blank cells within a cell selection are ignored by @SUM.
Any dates in the cells will be converted to serial numbers and included in the calculation. Since this will throw off your sum, avoid including dates in the @SUM argument cells.
If you use a mouse, the QuickSum button on the Main Toolbar offers a convenient way to total columns, rows, or both. It can total rows and columns in the selected cells, but you do not need to enter a formula.
Examples
A B C D
1 January February March
2 John $652 $833 $599
3 Mary $456 $305 $522
4 Ralph $68 $59 $73
5 Anna $80 $80 $80
@SUM(B4..D4) = $200
@SUM(C2..C5,260) = $1,537
@SUM(A5,534) = $534
@SUM(B2..B5,D2..D5) = $2,530
@SUM(B2..D5) = $3,807
@SUMIF - Sum Matching Cells
Syntax
@SUMIF(Block, Criteria, <Sum Range>)
Block Overall range of one or more cell addresses, a cell reference, or name.
Criteria Numeric or string values that determine whether a cell within the Block is added.
<Sum Range> Cell addresses within the Block to be included in the sum. Cell values must meet Criteria in order to be included in the sum. (optional)
@SUMIF adds those cells in Block that meet Criteria. An optional Sum Range may be specified to limit Criteria consideration and sum inclusion to particular cells within the Block.
Examples
A B C D
1 Customer QTY Price Total
2 Adams Electric 5 $1 $5
3 Frys Service 2 $1 $2
4 Major Hardware 4 $1 $4
5 Adams Electric 5 $1 $5
6 Adams Electric 5 $1 $5
@SUMIF(A2..D6,"Adams Electric",D2..D6) = $15
The second argument (criteria) must be on the left-hand side of the third argument (sum range). As well, the columns containing these arguments must be directly beside each other. If these conditions are not met the function will return ERR.
@SUMNEGATIVE - Adds Negative Values Only
Syntax
@SUMNEGATIVE(List)
List One or more numeric values or formulas, cell addresses, and cell references or names, separated by commas.
@SUMNEGATIVE sums only negative values in cells or list. It ignores blank cells and labels.
Examples
A
1 Profit/Loss
2 ($300)
3 $2,500
4 ($70)
5 $500
@SUMNEGATIVE(A1..A6) = ($370), ignoring the label and the empty cell
@SUMNEGATIVE(-300,2500,-70,500) = ($370)
@SUMPOSITIVE - Adds Positive Values Only
Syntax
@SUMPOSITIVE(List)
List One or more numeric values or formulas, cell addresses, and cell references or names, separated by commas.
@SUMPOSITIVE sums only positive values in cells or a list. It ignores blank cells and labels.
Examples
A
1 Profit/Loss
2 ($300)
3 $2,500
4 ($70)
5 $500
@SUMPOSITIVE(A1..A6) = $3,000, ignoring the label and the empty cell
@SUMPOSITIVE(-300,2500,-70,500) = $3,000
@SUMPRODUCT - Sum of Products of Corresponding Coordinates
Syntax
@SUMPRODUCT(Block1, Block2)
Block1 A cell reference or name.
Block2 A cell reference or name.
@SUMPRODUCT(Block1, Block2) returns the dot product of the vectors corresponding to the selections. Quattro Pro multiplies each corresponding cell from Block1 and Block2 and then totals those results. The selections must be the same size (same number of rows and same number of columns), or else the selections must both be one-dimensional (either a row or a column) and they must have the same length. If the selections do not match, @SUMPRODUCT returns ERR.
This @function is not compatible with 1-2-3. If your notebook must be compatible with 1-2-3, do not use @SUMPRODUCT.
@SUUJI converts an arabic number into its kanji representation. The Style parameter lets you specify how you want the representation to be displayed.
@SYD - Sum-of-the-years'-digits Depreciation
Syntax
@SYD(Cost, Salvage, Life, Period)
Cost A numeric value representing the amount paid for an asset.
Salvage A numeric value representing the value of an asset at the end of its useful life.
Life A numeric value representing the expected useful life of an asset (in years).
Period A numeric value representing the time period for which you want to calculate depreciation.
@SYD calculates depreciation amounts for an asset using an accelerated depreciation method. This allows higher depreciation in the earlier years of the asset's life. @SYD uses this formula to compute depreciation:
((Cost - Salvage)(Life - Period + 1)) divided by (Life(Life + 1)/2)
Cost must be equal to or greater than Salvage; both must be equal to or greater than 0. Life must be equal to or greater than Period; both must be equal to or greater than 1.
@DDB and @SLN offer other methods of calculating depreciation.
Examples
Assume you just bought a new $4000 computer. The dealer says you can sell it back to the store for $350 after eight years, but that no one would want to buy it after that. The Salvage value of that computer is $350 and its Life is 8. To see what the depreciation allowance of this computer will be by the second year (using this method of depreciation), enter this formula:
@SYD(4000,350,8,2) = 709.72
These examples show depreciation values for the first five years of an asset's life. These can be compared to those calculated with @DDB,FUNCTION_DDB which distributes more of the depreciation in the first year of life.
