@KANSUUJI converts a kanji number to its Arabic representation.
@KURT - Kurtosis
Syntax
@KURT(List)
List One or more numeric or cell values.
@KURT returns the kurtosis of List. The kurtosis of a data set measures a distribution's closeness to normality, indicating relative peakedness or flatness. A kurtosis greater than zero is referred to as leptokurtic. A kurtosis less than zero is referred to as platykurtic.
List must have four or more values. The standard deviation of List must not be 0.
@KURT uses this formula:
where s is the sample standard deviation.
Examples
@KURT(5,7,9,12,14,15,4,9,5,6) = -1.11117
@KURT(9.7,10,9.5,9.3,10.2,10,9.5,11) = 1.780277
@KURT(20,25,27,22,35,28) = 0.876754
@LARGEST - Nth Largest Number
Syntax
@LARGEST(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.
@LARGEST returns the Nth largest number in Array. Use @LARGEST to determine a value's rank in a data set from the top of that set.
If there are duplicates in Array, @LARGEST treats them as separate numbers.
Examples
@LARGEST({1,2,3,4,5,6,7,8,9,10},2) = 9
@LARGEST({1,2,3,4,5,6,7,8,9,10},4) = 7
@LARGEST({1,2,3,4,5,6,7,8,9,10},6) = 5
@LASTBLANKPAGE
Syntax
@LASTBLANKPAGE(Block)
Block A cell or reference; can be a link to another opened notebook (for example, [BUDGET]A:A1).
@LASTBLANKPAGE returns a string that contains the letters for the last unnamed blank sheet in a notebook that is not part of a group.
Quattro Pro searches for the last unnamed blank sheet (that is not in a group) starting at sheet IV and continuing toward sheet A. If there are no unnamed blank sheets (or if they are all in groups), @LASTBLANKPAGE returns ERR.
Example
@LASTBLANKPAGE(B17) = IG (if it is the last sheet that is blank and unnamed)
@LASTCELLVALUE
Syntax
@LASTCELLVALUE(block, <type>)
Block A cell or reference.
Type Number 1 (column) or 2 (row); the default type is 1.
@LASTCELLVALUE returns the contents of the last non-blank cell in the cells.
Quattro Pro searches for the last non-blank cell in the row or column. If there is no content to return, @LASTCELLVALUE returns 0.
Example
@LASTCELLVALUE(A1..I34,2) (the last cell in the row that is not blank)
@LASTINGROUP - Last Sheet in Group
Syntax
@LASTINGROUP(Block, GroupName)
Block A cell or cells of the notebook to check.
GroupName A string value representing a group name.
@LASTINGROUP returns a string that contains the letters for the last sheet in the group named GroupName. @LASTINGROUP searches the notebook referenced by Block for the group. If the group does not exist, @LASTINGROUP returns ERR.
Example
@LASTINGROUP([REPORTQ4]A:C12,"Totals") = "C" (if the notebook named REPORTQ4 contains a group named Totals that ends with sheet C)
@LBDAY - Last Business Day in Month
Syntax
@LBDAY(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).
@LBDAY returns the serial date number for the date of the last business day of the month in which Date falls.
Example
This formula calculates the last business day in November 1993, assuming that Sundays and the dates contained in cells A7..C9 are holidays.
@LCM returns the least common multiple of X and Y (the smallest integer into which both X and Y can divide without leaving a remainder).
@LCM is otherwise known as the greatest common denominator, which is not to be confused with @GCD (the greatest common divisor).
Examples
@LCM(9,6) = 18
@LCM(24,12) = 24
@LEFT - Leftmost Characters
Syntax
@LEFT(String, Num)
String A string value.
Num A numeric value equal to or greater than 0.
@LEFT returns the leftmost Num characters of String. It lets you extract a specified number of characters starting from the left end of a string or label.
If String is a numeric or date value or a blank cell, @LEFT returns ERR. If Num is longer than the length of String, all of String is returned. The number of characters returned is never greater than the length of the string.
Examples
@LEFT("Jennifer",5) = Jenni
@LEFT("Jennifer",15) = Jennifer
@LEFT("155",1) = 1
@LEFT(" Jennifer",6) = J (including five leading spaces)
@LEFT(123,1) = ERR (123 is a value)
@LENGTH(@LEFT("Jennifer",255)) = 8
@LENGTH - Number of Characters
Syntax
@LENGTH(String)
String A string value.
@LENGTH returns the number of characters in String, including spaces. You can combine strings or cell addresses with an ampersand (&). When String is a text string, it must be enclosed by double quotes.
If you try to reference a blank cell with this @function, Quattro Pro returns ERR.
@LENGTH("Greetings "&"earthling") = 19 (including space after Greetings)
@LENGTH(29584949) = ERR (29584949 is a value, not a string)
@LENGTH(A6&B10) = total number of characters in A6 and B10
@LENGTH(B10) = ERR (if B10 is blank or a value)
@LETTERTOINDEX - Sheet/Column Index Corresponding to Letter(s)
Syntax
@LETTERTOINDEX(Letters)
Letters A one- or two-character string enclosed in quotation marks; column and sheet letters run in sequence from A to Z, and continue from AA to AZ, up to IV.
@LETTERTOINDEX returns the index number (from 0 to 255) for column letters or sheet letters.
If Letters is a character string outside the range of sheet and column letters (for example, "IW"), @LETTERINDEX returns ERR.
Examples
@LETTERTOINDEX("A") = 0
@LETTERTOINDEX("B") = 1
@LETTERTOINDEX("IV") = 255
@LINEST - Fits Line to Data
Syntax
@LINEST(KnownYs, <KnownXs>, <Const>, <Stats>)
KnownYs Array of known y-values for the line y = mx + b.
KnownXs Array of known x-values (optional).
Const Logical value (optional) that tells @LINEST whether to force the constant b = 0:
If Const is TRUE or omitted, @LINEST uses the actual value of b.
If Const is FALSE, @LINEST sets b = 0, then adjusts the m-values so that y = mx.
Stats Logical value (optional) that tells @LINEST whether to return more regression statistics.
If Stats is TRUE, @LINEST returns the array
If Stats is FALSE or omitted, @LINEST returns only the m-coefficients and b.
@LINEST uses the "least squares" method to calculate a straight line that best fits your data and returns an array to describe the line. @LINEST returns additional regression statistics when the argument Stats = TRUE.
If known y-values are in one column, @LINEST takes each column of known x-values to be a separate variable. If known y-values are in one row, @LINEST takes each row of known x-values to be a separate variable.
The argument KnownXs can include more than one set of variables. If you use more than one variable, KnownYs must be a single-column or single-row selection. Use commas to separate x-values in the same row and semicolons to separate rows.
If you omit the argument KnownXs, @LINEST assumes it is the array {1, 2, 3,...} of a size equal to KnownYs.
The equation for the line is:
or
y = mx + b
where y is a function of x, the independent variable. The m-values are coefficients that correspond to the x-values. The value b is a constant. Values y, x, and m can be vectors. @LINEST returns the array
For @LINEST to work, the x variables need to be in one contiguous block of data.
Slope and y-intercept define a straight line.
Slope (m): Given any two points on a line and
slope m = .
Y-intercept (b): The y-intercept of a line = the value of y where the line crosses the y-axis.
The equation of a straight line is y = mx + b. Knowing the values of m and b, you can find any point on the line if you know either y or x. You can also use @TREND.
If there is only one independent x-variable, use the following formulas to find the slope and y-intercept values directly:
Slope: @INDEX(@LINEST(KnownYs, KnownXs),1)
Y-intercept: @INDEX(@LINEST(KnownYs, KnownXs),2)
The accuracy of @LINEST's calculation depends on the scatter of your data. The more linear your data, the more accurate the @LINEST calculation. @LINEST determines the best fit for the data by the least squares method. Given only one independent x-variable, @LINEST uses the following formulas to calculate m and b:
@LINEST fits the best straight line to your data; @LOGEST fits the best exponential curve. To decide which best fits your data, use @TREND for a straight line, or @GROWTH for an exponential curve, without the NewXs argument, to see an array of y-values predicted along that line or curve at your actual data points. Then compare predicted values with actual values. You can chart them for a visual comparison.
Note that y-values predicted by the regression equation might not be valid if they are outside the cells of y-values you used to determine the equation.
Example 1 - Slope and Y-intercept
@LINEST({4,3,2,1},{0,1,2,3}) = {-1, 4}, meaning the slope = -1 and y-intercept = 4
Example 2 - Simple Linear Regression
Sales for your company in its first four quarters are entered in a selection named Sales:
A B
1 Quarter Sales
2 1 $80,000
3 2 $90,000
4 3 $95,000
5 4 $105,000
To predict third-quarter sales for the following year, enter the formula
@SUM(@LINEST(B2..B5)*{7,1})
The projected sales are displayed in the selected cells:
6 5
7 6
8 7 $128,500
9 8
In general, @SUM({m,b}*{x,1}) = mx + b, the estimated y-value for a specified x-value. You can also use @TREND.
Regression Statistics
@LINEST and @LOGEST return additional regression statistics when the argument Stats = TRUE:
Statistic Description
se1, se2,..., sen Standard error values for coefficients m1, m2,..., mn.
seb Standard error value for constant b (NA when Const is FALSE).
r2 Coefficient of determination, ranging from 0 to 1. Compares the estimated and actual y-values. When r2 = 1, there is a perfect correlation in the sample (no difference between the estimated and actual y-values). As r2 approaches 0, y-values become unpredictable. See note below for information about how r2 is calculated.
sey Standard error for the y estimate.
F F-observed value (F statistic). The F statistic measures whether the relationship observed between the y and x could occur by chance alone.
df Degrees of freedom, used to find F-critical values in a statistical table. Compare these F-critical values to the F statistic to determine a confidence level for the model.
ssreg Regression sum of squares.
ssresid Residual sum of squares.
The additional regression statistics are displayed as follows:
In regression analysis, Quattro Pro calculates for each point the squared difference between the y-value estimated for that point and its actual y-value. The sum of these squared differences is called the residual sum of squares. Quattro Pro then calculates the sum of the squared differences between the actual y-values and the average of the y-values, which is called the total sum of squares (regression sum of squares + residual sum of squares). The smaller the residual sum of squares is, as compared with the total sum of squares, the larger the value of the coefficient of determination, r2, a measure of how well the equation resulting from the regression analysis explains the relationship among the variables.
Multiple Linear Regression
Example 1
High school grade point averages (GPA) are thought to depend on factors like the number of credit hours taken in the semester, the student's year in school, and hours worked at an outside job. Data for a small sample of students is in the following table:
A B C D
1 GPA Credit hrs Yrs Hrs on job
2 3 15 4 2
3 2 16 3 10
4 4 12 4 0
5 3.5 15 2 0
For the full regression display, enter the formula
@LINEST(A2..A5,B2..D5,TRUE,TRUE)
The result is the following array:
A B C D
6 -0.11 -0.14 -0.26 7.68
7 0.00 0.00 0.00 0.00
8 1.00 0.00 NA NA
9 0.00 0.00 NA NA
10 2.19 0.00 NA NA
Numbers in a full regression array are:
The multiple regression equation, y = m1*x1 + m2*x2 + m3*x3 + b, can now be obtained using the values from row 6:
y = - 0.26*x1 + (- 0.14)*x2 + (- 0.11)*x3 + 7.68
where
x1 credit hours
x2 year in school
x3 hours on job
To estimate the expected GPA of a third-year student who takes 14 credit hours and works 5 hours a week at an outside job, substitute:
y = - 0.26*14 - 0.14*3 - 0.11*5 + 7.68 = 3.1
Example 2
You have gathered data on 10 homes recently sold in your neighborhood. The table lists the amount each home sold for, the number of bedrooms and baths, total floor space in square feet, and lot size:
A B C D E
1 Sold for Bedrms Baths Sq. ft. Acres
2 $255,000 2 2 1500 1
3 $435,500 3 2.5 1900 5
4 $395,500 2 2 1200 10
5 $495,000 4 3 2400 1
6 $125,000 1 1 950 0.25
7 $270,000 2 2 1280 2
8 $595,000 4 1.5 2100 4
9 $249,500 2 2 1350 0.5
10 $255,000 2 1 1100 1
11 $244,500 2 1 1080 0.25
@LINEST(A2..A11,B2..E11,TRUE,TRUE) returns the following table of the full regression statistics:
A B C D E
12 x4=acres x3=sq ft x2=baths x1=bedrooms constant b
13 17373.966 46.18235 -29475.5 118123.37 -10502.1
14 1411.8687 39.42453 11029.34 16402.137 13092.08
15 0.9967254 10962.52 NA NA NA
16 380.471 5 NA NA NA
17 1.829E+11 6.01E+08 NA NA NA
Note that these values are rounded off, because the default column width does not allow display of all digits, though full precision is stored in the cell. In further calculations, reference the cell for the value.
The coefficient of determination R-squared, in A15, is 0.9967254, showing a strong relationship between the independent variables and sales price.
To determine if such a strong correlation could occur by chance alone, compare the F statistic in A16 with the F-critical value. Tables of F-critical values can be found in many statistics textbooks. To read the table, assume a single-tailed test and use an alpha value of 0.05, meaning there is less than a 5% chance the correlation is accidental. For the degrees of freedom (abbreviated in most tables as v1 and v2), use
v1 = 4, the number of variables, and
v2 = s - (v1 + 1) = 10 - (4 + 1) = 5
where s = the number of houses in the sample.
The F-critical value in the table, for alpha = 0.05, v1 = 4, and v2 = 5, is 5.19. The F-observed value in Cell A16 is 380.471, which is much larger than 5.19. This means the regression equation can be used with assurance to predict expected sales prices for other homes in the area.
Calculating T-Statistics
You can also test each independent variable to see how well it predicts the expected selling price of a home. You do this by comparing the t-observed value for each variable with t-critical from a statistics table.
First divide slope m for each variable by its estimated standard error, se. For lot size, m appears in A13 and se is in A14, so
+A13/A14 (or 17373.966 / 1411.8687) = 12.30565
From a table of critical values of t, single tail, for alpha = 0.05 and 5 degrees of freedom (Cell B16 in the regression array above), t-critical = 2.015
The absolute value of t-observed for lot size, 12.30565, is above t-critical, showing lot size to be an important variable in predicting sales price for homes in the area. The other variables can be tested in the same way.
Variable t-observed value
Number of bedrooms 7.201712
Number of baths -2.67246
Floor space 1.171412
Lot size 12.30565
Number of bedrooms, number of baths, and lot size all have absolute values of t-observed > t-critical, so these variables are useful in predicting the sales price of a home in the area.
@LINTERP - Linear Interpolation
Syntax
@LINTERP(KnownX, KnownY, X)
KnownX One-dimensional selection containing X values.
KnownY One-dimensional selection containing Y values corresponding to the X values in KnownX.
X Number for which the corresponding Y value is desired.
@LINTERP interpolates a Y value corresponding to X using the XY pairs specified by KnownX (which contains the X coordinates) and KnownY (which contains the Y coordinates). If X lies between two values in KnownX, @LINTERP interpolates using those two values and their respective KnownY counterparts. If X is outside the range of KnownX, the Y value is extrapolated based on the slope of the line between the two closest points.
KnownX and KnownY do not have to be the same size. If KnownY is smaller than KnownX, the last value in KnownY is used as the corresponding Y value for extra KnownX values. If KnownY is larger than KnownX, its extra values are ignored.
Example
This formula calculates the Y value for the X value 6.7 if the data in the next figure is used.
@LINTERP(A3..A9,B3..B9,6.7) = 17.5976
A B
1 x values y values
2
3 -28.345 -9.7821
4 -17.89 -5.6667
5 0.9232 2.891
6 1.212 2.9978
7 4.552 13.67
8 10.75 25.003
9 30.8 33.33
@LLDEC - Latitude and Longitude to Decimal
Syntax
@LLDEC(Degrees, Minutes, Seconds, Direction)
Degrees Degrees of Latitude or Longitude.
Minutes Minutes of Latitude or Longitude.
Seconds Seconds of Latitude or Longitude.
Direction For Latitude, North (1) or South (2) of the equator; for Longitude, East (3) or West (4) of the prime meridian at Greenwich, England.
@LLDEC converts a latitude or longitude coordinate to decimal. Latitude south of the equator is represented as a negative number; longitude west of the prime meridian is represented as a negative number.
Examples
@LLDEC(38, 45, 15, 1) = 38.75417
@LLDEC(38, 45, 15, 2) = -38.75417
@LLDEC(143, 15, 25, 4) = -143.257
@LN - Natural Logarithm
Syntax
@LN(X)
X A numeric value > 0.
@LN returns the natural logarithm of X. A natural logarithm uses the mathematical constant e as a base. @LN produces the inverse of @EXP.
Examples
@LN(3) = 1.098612289
@LN(@EXP(10)) = 10
@LN(16)/@LN(2) = 4
@LN(-4) = ERR (-4 is less than 0)
@LOG - Base 10 Logarithm
Syntax
@LOG(X)
X A numeric value > 0.
@LOG returns the logarithm of a number in base 10.
Examples
@LOG(1000) = 3
@LOG(10^23.8) = 23.8
@LOG(16)/@LOG(2) = 4 (log to base 2 of 16)
@LOGBASE - Logarithm to Base X
Syntax
@LOGBASE(Number, <Base>)
Number Positive real number.
Base Base of the logarithm (optional); the default is 10.
@LOGBASE calculates the logarithm of a specified number to the specified base.
<Base> must be a value greater than 1; otherwise ERR is returned.
Examples
@LOGBASE(100) = 2
@LOGBASE(27,3) = 3
@LOGBASE(18,2) = 4.169925
@LOGCONV - Converts Logarithm to Another Base
Syntax
@LOGCONV(Number, FromBase, ToBase)
Number Value to be converted = the log of a number m to the base b.
FromBase Positive integer greater than 1.
ToBase Positive integer greater than 1.
@LOGCONV converts a specified logarithm from one specified base to another. For Number = @LOGCONV uses the formula:
where
m a number
b original base
a new base
If FromBase = 1, @LOGCONV returns ERR because the log of b to the base a is zero.
If FromBase or ToBase are negative or non-integer, @LOGCONV returns ERR.
Examples
@LOGCONV(2,4,2) = 4
@LOGCONV(3,4,2) = 6
@LOGEST - Fits Curve to Data
Syntax
@LOGEST(KnownYs, <KnownXs>, <Const>, <Stats>)
KnownYs Array of known y-values for the curve y = b*m^x.
KnownXs Array of known x-values.
Const Logical value (optional) that tells @LOGEST whether to force the constant b = 1:
If Const is TRUE or omitted, @LOGEST uses the actual value of b.
If Const is FALSE, @LOGEST sets b = 1, then adjusts the m-values so that y = m^x.
Stats Logical value (optional) that tells @LOGEST whether to return more regression statistics.
If Stats is TRUE, @LOGEST returns the array
If Stats is FALSE or omitted, @LOGEST returns only the m-coefficients and b.
@LOGEST calculates an exponential curve that fits your data and returns an array to describe the curve. @LOGEST returns additional regression statistics when the argument Stats = TRUE.
If known y-values are in one column, @LOGEST takes each column of known x-values to be a separate variable. If known y-values are in one row, @LOGEST takes each row of known x-values to be a separate variable.
If any of the known y-values are 0 or negative, @LOGEST returns ERR.
The argument KnownXs can include more than one set of variables. If you use only one variable, KnownYs and KnownXs can be selections 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 selection. Use commas to separate x-values in the same row and semicolons to separate rows.
If you omit the argument KnownXs, @LOGEST assumes it is the array {1, 2, 3,...} of a size equal to KnownYs.
The equation for the curve is or
where y is a function of x, the independent variable. The m-values are bases that correspond to the exponent x-values. The value b is a constant. Values y, x, and m can be vectors. @LOGEST returns the array .
If there is only one independent x-variable, use the following formulas to find the slope and y-intercept values directly:
The accuracy of @LOGEST's calculation depends on how close the plot of your data comes to an exponential curve. @LINEST fits the best straight line to your data; @LOGEST fits the best exponential curve. To decide which best fits your data, use @TREND for a straight line, or @GROWTH for an exponential curve, without the NewXs argument, to see an array of y-values predicted along that line or curve at your actual data points. Then compare predicted values with actual values. You can chart them for a visual comparison.
The y-values predicted by the regression equation might not be valid if they are outside the cells of the y-values you used to determine the equation.
Using @LOGEST to test an equation is similar to using @LINEST. However, the additional statistics @LOGEST returns are based on the following linear model:
Remember this when evaluating the additional statistics, especially the sei and seb values, which should be compared to ln mi and ln b, not to mi and b.
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
For the full regression display, enter the formula
@LOGEST(Sales,A2..A5,TRUE,TRUE)
The regression statistics are returned as follows:
6 1.235849 60133.78
7 0.008186 0.022419
8 0.99702 0.018305
9 669.1028 2
10 0.224208 0.00067
The equation for the curve is y = b * m1^x1.
So, using the values from the array:
y = 60133.78 * 1.23585^x
To estimate sales for future months, substitute the month number for x in this equation, or use @GROWTH. For example, for the fifth month:
y = 60133.78 * 1.23585^5 = $173,359
You can use the additional regression statistics (cells A6..B10 in this example) to determine how useful the equation is for predicting future values. For instance, the value in cell A8 in this example is the r-squared (r2) value. When r-squared = 1, there is a perfect correlation in the sample (no difference between the estimated and actual y-values). As r-squared approaches 0, y-values become unpredictable.
@LOGINV - Inverse of Cumulative Lognormal Distribution
Syntax
@LOGINV(Prob, Mean, SDev)
Prob Probability associated with the cumulative lognormal distribution function; 0 ú Prob < 1.
Mean Mean of ln(x).
SDev Standard deviation of ln(x); must be > 0.
@LOGINV returns the inverse of the cumulative lognormal distribution.
Example
@LOGINV(0.027985,2.5,0.8) = 2.640543
@LOGNORMDIST - Cumulative Lognormal Distribution
Syntax
@LOGNORMDIST(X, Mean, SDev)
X Value to evaluate the function; must be > 0.
Mean Mean of ln(x).
SDev Standard deviation of ln(x); must be > 0.
@LOGNORMDIST returns the cumulative lognormal distribution.
Example
@LOGNORMDIST(3,2.5,0.8) = 0.03991
@LOOKUP - Looks Up Values
Syntax
@LOOKUP(Value, LookupVector, ResultVector)
Value Value to look for in LookupVector; can be a number, text, logical value, or reference to a value.
LookupVector Cells containing only one row or column.
ResultVector Selection of the same dimensions as LookupVector and containing corresponding values.
@LOOKUP looks up values in a specified row or column. It looks in a designated row or column for a specified value, moves to the corresponding cell in another specified row or column, and returns the value it finds there.
Values in LookupVector and ResultVector must be in ascending order for @LOOKUP to return the correct value (upper- and lowercase text have the same values).
@LOOKUP lets you specify row or column to look in, as well as the one containing the value to return.
Examples
From the wind chill table below, you want to find the apparent temperature at a certain wind speed.
A B C D E F G
1 Wind Speed
2 Deg. F 5 10 15 20 25 30
3 10 6 -9 -18 -25 -29 -33
4 20 16 4 -5 -10 -15 -18
5 30 27 16 9 4 0 -2
6 40 37 28 22 18 16 13
7 50 48 40 36 32 30 28
To find the apparent temperature when the thermometer reads 25 degrees Fahrenheit and the wind is blowing at 10 miles an hour, enter
@LOOKUP(25,A2..A7,C2..C7)
The result is 4 degrees F, which is the apparent temperature corresponding to a thermometer reading of 20 degrees, the next lower value.
At 20 degrees, when the wind is twice as strong (20 mph), the apparent temperature is
@LOOKUP(20,A2..A7,E2..E7) = -10
@LOWER - String in Lowercase
Syntax
@LOWER(String)
String A string value.
@LOWER returns String in lowercase characters. Numbers and symbols within a string are unaffected. Numeric and date values return ERR.
Examples
@LOWER("UPPER") = upper
@LOWER("Hello, world.") = hello, world.
@LOWER("145 Bancroft Lane") = 145 bancroft lane
@LOWER(4839) = ERR
@LOWER(@LEFT("Johnson",1)) = j
@LWKDAY - Last Weekday
Syntax
@LWKDAY(Wkday, Month, Year, <AuxWkday>)
Wkday Number from 1 (Saturday) to 7 (Friday).
Month Number from 1 (January) to 12 (December).
Year Number from 0 (1900) to 199 (2099) or a standard year like 1993.
AuxWkday Auxiliary day of the week that must fall in the same week as Wkday; 0 for no auxiliary day or a number from 1 (Saturday) to 7 (Friday) indicating the auxiliary day (the default is 0).
@LWKDAY returns the serial date number for the date of the last occurrence of Wkday in Month of Year (for example, the last Tuesday in November 1994).
See "Using dates and times in Quattro Pro."
You can use AuxWkday to specify that both Wkday and AuxWkday must fall in the same week of the same month. (See the second example.)
The valid date calculation range for this function is 01/01/1900 through 12/31/2099.
Examples
/@LWKDAY(3,6,115)= 42184 (June 29, 2015), the date of the last Monday in June 2015.
@LWKDAY(4,11,94,7)= 34660 (November 22, 1994), the last Tuesday on which both the last Tuesday and a Friday fall on the same week of November 1994.
@MATCH - Position of Matching Cell
Syntax
@MATCH(Cell Contents, Block, <Match Type>)
Cell Contents Numeric or string value to be matched.
Block Cells, contiguous selections, an array, or array reference.
<Match Type> -1, 0, or 1. Match Type specifies which cell positions are returned:
-1 = smallest, 1 = largest, 0 = first found.
@MATCH returns the relative position of the cell in Block whose contents match the Cell Contents argument.
@MATCH returns the position of the item rather than the item itself.
@MATCH returns ERR or 0 if no matches are found.
Match Type = -1, returns the position of the smallest value that is greater than or equal to Cell Contents. Selections must be arranged in descending order.
Match Type = 0, returns the position of the first value that is exactly equal to Cell Contents. Blocks may be arranged in any order.
Match Type = 1, returns the position of the largest value that is less than or equal to Cell Contents. Selections must be arranged in ascending order.
Match Type =1 is the Default Value.
Examples
A B
1 Name Grade
2 Fred 92
3 Mary 84
4 Reno 75
5 Anne 67
6 John 54
In cell selections, or arrays of cells, @MATCH uses zero-based numbering (the first cell in the selection equals 0).
@MATCH(75,B1..B4,-1) = 3
@MATCH(75,B1..B4,0) = 3
@MATCH(75,B1..B4,1) = 0 (the selection is ordered incorrectly)
@MATCH("RENO",A1..A4,0) = 3 (Reno is third in his class)
Also, @MATCH("b",{"a","b","c"},0) = 1 (the first item in the array equals 0).
@MAX - Maximum Value
Syntax
@MAX(List)
List One or more numeric values, cell addresses, and references or names, separated by commas.
@MAX returns the largest numeric or data value in List. If more than one selection is listed, commas must separate the selections. If any of the cells referenced contain ERR, the resulting value is ERR.
Examples
A B C D
1 Jan. Feb. Mar.
2 JA $652 $833 $599
3 MH $456 $305 $522
4 RB $68 $59 $73
5 PD $379 $379 $379
------ ------ ------
6 $1,555 $1,576 $1,573
@MAX(B3..B5) = $456
@MAX(C3..C5,D3..D5) = $522
@MAX(A1..D6) = $1,576
@MAX(B2..C5,D3) = $833
@MAXLOOKUP - Cell Containing Largest Value
Syntax
@MAXLOOKUP(BlockList)
BlockList Cells or list of selections containing numeric values.
@MAXLOOKUP returns the address of the cell containing the largest value in specified cells or list of selections. @MAXLOOKUP return ERR if no cells in BlockList contain values.
Separate the entries in BlockList with argument separators.
Labels and blank cells in BlockList are ignored.
Example
Suppose you keep lists of contributors and the amounts they gave in separate notebooks for each year. The notebooks have the same layout, because you built them from the same template. To find the cell location of the maximum amount anyone gave over all the years for which you have files, enter
Year Number from 0 (1900) to 199 (2099) or a standard year like 1993.
@MDAYS returns the number of calendar days in Month of Year.
The valid date calculation range for this function is 01/01/1900 through 12/31/2099.
See "Using dates and times in Quattro Pro."
Example
@MDAYS(2,1996)= 29, the number of days in February 1996.
@MDET - Determinant of a Matrix
Syntax
@MDET(Array)
Array A numeric array or a selection of values specifying a square matrix; must have an equal number of rows and columns, and cannot contain blank cells.
@MDET calculates the determinant of a matrix (Array). The determinant is obtained by taking any row or column of the matrix, forming the products of each element and its cofactor, and taking the sum of the products; @MDET uses this formula:
where aij is the element in the ith row and jth column of A and the cofactor aij is the product of the determinant of the minor matrix Mij, formed by deleting row i and column j of A, and a power of -1:
If Array does not contain the same number of rows and columns, or if Array contains any blank cells, @MDET returns ERR. If any two rows or columns in Array are equal or have proportional elements, @MDET returns 0.
The result of @MDET is accurate to approximately 16 digits, which can lead to a small numeric error when the cancellation is not complete.
Examples
@MDET({12,15,21|8,13,17|16,32,44}) = 144
This formula calculates the determinant of the data shown in the next figure:
Settle Number representing the settlement date; must be < Maturity.
Maturity Number representing the maturity date; must be > Settle.
Coupon Coupon rate; 0 ú Coupon ú 1.
Yield Annual yield; 0 < Yield ú 1.
Freq Frequency of coupon payments in number of payments per year (can be 1, 2, 3, 4, 6, or 12; the default is 2).
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@MDURATION returns the modified Macaulay duration for a bond with assumed par value of 100. Modified duration is calculated using this formula:
D Duration
Y Yield
F Frequency
Example
This formula calculates the modified duration of a bond with these terms: Settle is August 8, 1992, Maturity is November 15, 1998, Coupon is 9%, and Yield is 8.816%.
@MEDIAN returns the middle value in a range of values in a data set arranged in ascending or descending order. If the number of values in the data set is even, the median is the mean of the two middle values. Use @MEDIAN when you want a more robust estimation of the central value in a distribution than you obtain with @AVG.
Examples
@MEDIAN(10,12,15,25,30) = 15
@MEDIAN(2,4,5,5,6,8,9,9) = 5.5
@MEMAVAIL - Memory Available
Syntax
@MEMAVAIL
@MEMAVAIL returns the number of bytes of memory currently available.
Example
@MEMAVAIL = 47819 (if 47,819 bytes of memory are available)
@MEMEMSAVAIL - Expanded Memory Available
Syntax
@MEMEMSAVAIL
@MEMEMSAVAIL is included for compatibility with Quattro Pro for DOS; it always returns NA under Windows.
@MID - Extract Characters from String
Syntax
@MID(String, StartNumber, Num)
String A string value.
StartNumber A numeric value equal to or greater than 0.
Num A numeric value equal to or greater than 0.
@MID extracts the first Num characters of String starting at StartNumber, which is the number of characters to the right of the first character (character 0). It is similar to @LEFT, which extracts Num characters of String beginning with the first character. The difference is that you can specify a character other than the first character in the string.
String can be any text string (enclosed by quotes) or reference to a cell containing a label. If StartNumber is greater than or equal to the length of String or if Num is 0, the result is "", or an empty string.
Examples
@MID("Abraham Lincoln",8,7) = Lincoln
@MID("George Washington",7,4) = Wash
@MID("Theodore Roosevelt",19,5) = ""
@MID(A23,@FIND("Roosevelt",A23,0),@LENGTH("Roosevelt")) = Roosevelt (if A23 = Franklin Roosevelt)
@MIN - Minimum Value
Syntax
@MIN(List)
List One or more numeric values, cell addresses, and references or names, separated by commas.
@MIN returns the smallest numeric or data value in List. If List contains more than one value, commas must separate the values. Labels are treated in all statistical functions as 0 and should therefore be excluded from List.
If List is entered as cells and one or more cells in the cells are blank, the blanks are excluded from the calculation; otherwise, blanks are treated as 0.
Examples
A B C D
1 Jan. Feb. Mar.
2 JA $652 $833 $599
3 MH $456 $305 $522
4 RB $68 $59 $73
5 PD $379 $379 $379
------ ------ ------
6 $1,555 $1,576 $1,573
@MIN(B3..B6) = $68
@MIN(B2..D2,B4..D4) = $59
@MIN(B3..D3) = $305
@MINLOOKUP - Cell Containing the Smallest Value
Syntax
@MINLOOKUP(BlockList)
BlockList Cells or list of selections containing numeric values.
@MINLOOKUP returns the address of the cell containing the smallest value in specified cells or list of selections. @MINLOOKUP return ERR if no cells in BlockList contain values.
Separate the entries in BlockList with argument separators.
Labels and blank cells in BlockList are ignored.
Example
Suppose you keep lists of contributors and the amounts they gave in separate notebooks for each year. The notebooks have the same layout, because you built them from the same template. To find the cell location of the minimum amount anyone gave over all the years for which you have files, enter
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."
@MINUTE returns the minute 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 and 59.
To extract the minute portion of a string that is in time format (instead of serial format), use @TIMEVALUE with @MINUTE to translate the time into a serial number. You can also use @TIME to enter a time value instead of a serial number.
Examples
@MINUTE(.36554) = 46
@MINUTE(.2525) = 3
@MINUTE(35) = 0
@MINUTE(@TIME(3,15,22)) = 15
@MINUTE(@TIMEVALUE("10:08 am")) = 8
@MINVERSE - Inverse Matrix
Syntax
@MINVERSE(Array)
Array Square numeric array (same number of rows as columns); you can use a cell reference or cell name or an array constant like {1,2|3,4}.
@MINVERSE returns the inverse matrix for a matrix stored in a square array. Matrices are helpful in solving problems with many variables in mathematics and economics.
@MINVERSE returns ERR if:
Any cells in Array are empty or contain text.
Array does not have an equal number of rows and columns.
To show how the inverse of a 2 x 2 matrix is calculated, suppose cells A1..B2 contains the letters a, b, c, and d, representing any four numbers:
A B
1 a c
2 b d
The inverse of the matrix in A1..B2 is:
A B
1 d/(a*d - b*c) b/(b*c - a*d)
2 c/(b*c - a*d) a/(a*d - b*c)
The product of a matrix and its inverse is the identity matrix, in which all diagonal values = 1 and all other values = 0.
The result of @MINVERSE is accurate to approximately 16 digits; this can lead to a small numeric error when the cancellation is not complete.
Some square matrices cannot be inverted, and @MINVERSE returns ERR. The determinant is 0 for a matrix that cannot be inverted.
Use @INDEX to get individual elements from an inverse matrix.
FinRate Interest rate paid for funds used in cash flows.
ReinvRate Interest rate received on reinvested funds used in cash flows.
Type Timing of the cash flows (optional):
0 = end of each period (default)
1 = beginning of each period
@MIRR calculates the modified internal rate of return on an investment consisting of payments made at regular intervals.
The cells must contain at least one positive value and one negative value. Normally, the first cash-flow amount in the cells is a negative number (a cash outflow) that represents the investment. Quattro Pro assigns the value 0 to all blank cells and labels in Block and includes them in the calculation.
@MIRR helps you determine the profitability of an investment. To assess an investment, combine @MIRR with other financial functions, like @NPV.
MIRR relates to NPV by the following formula:
where
ci cash received in (positive)
co cash paid out (negative)
n number of cash flows
f finance rate
r reinvestment rate
Examples
You bought a small cafe at the end of 1990 for $250,000. The first year of operation (1992) you spent a lot on restoration and advertising, but since then profits have grown. You want to know when the business started to pay for itself and what the return is now. Your finance rate is 9.5%, your reinvestment rate is 11.5%, and you reinvest your profits at the end of the period.
A B C D
1 Year Profit/Loss end/per beg/per
2 1990 ($250,000)
3 1991 ($50,000)
4 1992 $112,500 -25.31% -38.32%
5 1993 $120,500 -2.31% -5.95%
6 1994 $128,500 8.32% 8.03%
7 1995 $136,500 13.77% 14.64%
For each year of profit, enter @MIRR in Column C for the cash flow up through that year. For example, in Cell C4,
@MIRR(B2..B4,0.095,0.115) = -25.31% and in Cell C7,
@MIRR(B2..B7,0.095,0.115) = 13.77%. Your investment began to pay off in 1994.
If you could reinvest your profits at the beginning of the period instead of the end, your return would be higher. You found this out by entering the same formulas in Column D but with Type = 1. For example, in Cell D7,
@MIRR(B2..B7,0.095,0.115,1) = 14.64%
@MMULT - Matrix Product of Two Arrays
Syntax
@MMULT(Array1, Array2)
Array1, Array2 Arrays to be multiplied.
@MMULT calculates the matrix product of two arrays. The resulting array has the same number of rows as Array1 and the same number of columns as Array2.
The number of columns in Array1 must equal the number of rows in Array2.
Array1 and Array2 must contain only numbers.
Array1 and Array2 can be specified as cell names or references, or array constants.
The matrix product array a of two arrays b and c is:
Examples
@MMULT({2,3|1,0},{1,0|0,1}) = {2,3|1,0}
Given the following arrays,
A B C
1 1 2 0
2 3 2 1
3 1 0 3
4
5 2 1
6 0 2
7 1 0
Enter @MMULT({A1..C3},{A5..B7})
The result is displayed as follows:
A B C
8 2 5
9 7 7
10 5 1
@MNTHS - Months
Syntax
@MNTHS(StartDate, EndDate, <EndMnth>)
StartDate Number representing the start date. See "Using dates and times in Quattro Pro."
EndDate Number representing the end date.
EndMnth 1 to indicate adherence to ends of months; 0 to indicate that ends of months are ignored; the default is 1.
@MNTHS calculates the number of whole months between StartDate and EndDate. A whole month is the day of the month on which a specified date falls to that same day in the next month, such as March 11 to April 11.
If the day of the month on which StartDate falls does not exist in the month in which EndDate falls, and EndDate falls on the last day of that month, the number of months returned includes that month. For example, the number of months returned for March 31, 1993 to June 30, 1993 is 3 and not 2.
If StartDate falls on the last day of a month with less than 31 days, and EndDate precedes StartDate, the result depends on the value of EndMnth; for example, when evaluating the period from February 29, 1992, to January 31, 1992, if EndMnth is 1, -1 is returned; if EndMnth is 0, 0 is returned.
Examples
@MNTHS(@DATE(93,4,9),@DATE(94,9,15)) = 17, the number of whole months between April 9, 1993 and September 15, 1994.
@MNTHS(@DATE(93,4,30),@DATE(93,1,31)) = -3
@MOD - Modulus (Remainder)
Syntax
@MOD(X, Y)
X A numeric value.
Y A numeric value not equal to 0.
@MOD divides the X value by Y and returns the remainder, or modulus, value. Because you cannot divide a number by zero, ERR results if the value of Y is zero.
Examples
@MOD(3,1) = 0 (3 divided by 1 leaves no remainder)
@MOD(5,2) = 1 (5 divided by 2 leaves a remainder of 1)
@MOD(3,1.1) = 0.8
@MOD(4,0) = ERR
@MODE - Most Frequent Value
Syntax
@MODE(List)
List One or more numeric or cell values.
@MODE returns the value in a sample or population that appears more frequently than any other value. The mode emphasizes data concentration and is best used to describe large data sets. It is commonly used to decide which resulting value is correct when the same measuring or computing process is repeated several times.
If the data set contains no duplicate data points, @MODE returns NA.
Examples
@MODE(2,2,5,7,9,9,9,10,10,11,12,18) = 9
@MODE(91,87,83,80,86,55,83,68,79,83) = 83
@MODE(1,2,3,4,5) = NA
@MODULO - Remainder (Modulus)
Syntax
@MODULO(x, y)
x Numeric value.
y Numeric value, but not 0.
@MODULO returns the remainder, or modulus, of x/y. @MODULO works like @MOD, with this difference:
@MODULO uses the formula
x - y * @ROUNDDOWN(x/y)
and the sign of the result (+ or -) is always the same as the sign of y.
@MOD uses the formula
x - y * @INT(x/y)
and the sign of the result (+ or -) is always the same as the sign of x.
If x is 0, @MODULO returns 0.
Examples
@MODULO(15,6) = 3
@MOD(15,6) = 3
@MODULO(-7,3) = 2
@MOD(-7,3) = -1
@MONTH - Month Portion of Date Serial Number
Syntax
@MONTH(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."
@MONTH returns the month portion of DateTimeNumber. DateTimeNumber must be a valid date/time serial number. Only the integer portion is used. The result is between 1 (January) and 12 (December).
To extract the month portion of a string that is in date format (instead of serial format), use @DATEVALUE with @MONTH to translate the date into a serial number. You can also use @DATE to enter a date value instead of a serial number.
Examples
@MONTH(69858) = 4
@MONTH(58494) = 2
@MONTH(.3773) = 12
@MONTH(@DATEVALUE("3/5/88")) = 3
@MONTH(@DATE(88,3,5)) = 3
@MOD(@MONTH(@DATEVALUE("3/5/88")),12) = 3
@MROUND - Round to Nearest Multiple
Syntax
@MROUND(X, Y)
X Value to round.
Y Value to make rounded X divisible by.
@MROUND rounds X to the nearest value that is evenly divisible by Y. If Y is zero, @MROUND returns zero. If X and Y do not have the same sign, @MROUND returns ERR.
Int Number │ 0 representing the periodic interest rate.
TtlPer Total periods in the loan from start to finish, or the total periods remaining from the chosen starting period forward.
Principal Original loan balance; also can be any starting point in the loan.
Residual Remaining balance on loan at end of loan term; enter 0 if the loan will be paid in full.
ExtraPrin Extra principal amount to be paid each period (must be positive).
Fper Number of the first period, relative to the starting point, in which extra principal is paid; the default is 1 (the first period).
Lper Number of the last period, relative to the starting point, in which extra principal is paid; the default is until the end of the loan; you can set Lper to any number greater than or equal to the last period number when extra principal payments last the life of the loan (for example, Lper can be 400 for a loan which lasts 360 periods).
Rper Period for which the loan status is reported; the default is at loan end (any number greater than the end of the loan defaults to loan end); Rper does not affect the value @MTGACC returns if Option is 0 or 10.
Option Specifies the output value type (the default is 0):
0 = number of periods to loan end, when balance equals Residual
1 = balance of loan at the Rper
2 = cumulative interest paid at Rper
3 = cumulative principal paid at Rper
10 = number of fewer periods in loan life, due to payment of extra principal
11 = balance reduction at Rper due to payment of extra principal
12 = reduction in cumulative interest paid at Rper due to payment of extra principal
13 = increase in cumulative principal paid at Rper due to payment of extra principal
@MTGACC calculates the effects of paying extra monthly principal for amortized loans. The value that @MTGACC returns depends on the Option you specify. For the specified Rper, you can find the loan balance, cumulative interest, or cumulative principal. You can also find how the number of periods, loan balance, and cumulative interest have been reduced, and how cumulative principal has increased, as a result of paying extra principal.
@MTGACC returns values for the end of the loan or for the end of any payment period. During calculation, @MTGACC rounds currency values to 2 decimal places, giving currency answers in whole cents, as is common with mortgage institutions.
You can use @MTGACC to return information about a loan on which you make no extra principal payments (ExtraPrin = 0); for example, @MTGACC returns exact values of the loan balance, cumulative interest paid, and cumulative principal paid.
Examples
For a mortgage with a yearly interest rate of 9%, monthly payments for 30 years, an original balance of $150,000, and a future value of $80,000, the following formulas calculate the effects of paying $300 extra principal per month starting at the beginning of the second year and continuing through the fourth year. The reporting period is the tenth year (when the home will be sold and the mortgage paid-off).
List One or more numbers or selections of numbers, separated by commas.
@MULT calculates the cumulative product of a set of numbers (List). List is a comma separated list of numbers, selections containing numbers, or both. The numbers in this list are multiplied by each other. Blank cells and cells containing strings in any of the selections passed as arguments are given a value of 1.
Example
This formula calculates the cumulative product 3.4, 5.7, -1.2, and the numbers shown in the next figure.
@MULT(A6..C10,3.4,5.7,-1.2) = 30037.32
A B C
6 3.467 0.123
7 134.23 0.034 1.238
8 87.65 6.54% 0.987
9 -2.35 79.11
10 101.93 0.005
@MULTINOMIAL - Sum of Terms
Syntax
@MULTINOMIAL(List)
List One or more numbers to calculate multinomial of; each number in List must be │ 0.
@MULTINOMIAL returns the multinomial of a list of values. It uses this formula for @MULTINOMIAL(a,b,c):
If any value in List is negative, @MULTINOMIAL returns ERR.
If any value in List is non-integer, it will be truncated to an integer.
