StartDate Date number. See "Using dates and times in Quattro Pro."
EndDate Date number.
@D360 returns the number of days between two dates, based on a 360-day year (twelve 30-day months). Use this function to help compute payments if your accounting system is based on this calendar. The formula conforms to the 1990 modifications to the Securities Industry Association's 1986 edition of Standard Security Calculation Methods.
A similar function, @DAYS360, differs from @D360 by adding a third argument, Method. When you specify Method as FALSE (the default), you can in some cases get a different result using @DAYS360 from the one returned by @D360. See @DAYS360 for details.
For a conventional year of 365 days (366 in leap years), use @DATEDIF or subtraction.
Examples
To calculate the number of days between March 3 and May 31 of 1996:
@D360(@DATE(96,3,3), @DATE(96,5,31)) = 87
The same calculation using date values: @D360(35127, 35216) = 87
However, @DAYS360 gives a different result when no Method is specified:
@DAYS360(@DATE(96,3,3), @DATE(96,5,31)) = 88
@DATE
Syntax
@DATE(Yr, Mo, Day)
Yr For years 1900 through 3199, the four-digit year is valid. Or, use a numeric value between -300 and 1299 (-300 = 1600, 0 = 1900, 1299 = 3199)
Mo A numeric value between 1 and 12.
Day A numeric value between 1 and 31.
@DATE(40,12,31) and @DATE(1940,12,31) both return 14976 (31-Dec-1940).
@DATE(140,12,31) and @DATE(2040,12,31) both return 51501 (31-Dec-2040)
@DATE returns the date/time "serial number" of the date specified with year, month, and day arguments. This serial number can range from -109,571 (January 1,1600) to 474,816 (December 31, 3199). December 30, 1899 is 0, so a positive number represents the number of days from December 30, 1899 up to the date referenced in the formula.
Date/time serial numbers are used in notebook calculations. (The fractional portion of a serial number is used for the time @functions.)
To display a date/time serial number in a date format, right-click cells, click Cell Properties, then click Numeric Format. This shows the date in its more common form (for example, Jan-1-94 instead of 34335).
Any illegal dates return ERR as their value, for example, @DATE(87,2,29). (This date corresponds to February 29, 1987, which is impossible; 1987 was not a leap year.) See "Using dates and times in Quattro Pro."
Examples
@DATE(1940,12,31)= 14976 (December 31, 1940)
@DATE(2040,12,31)= 51501 (December 31, 2040)
@DATE(93,1,1) = 33970 (January 1, 1993)
@DATE(91,9,13) = 33494 (September 13, 1991)
@DATE(0,1,1) = 2 (January 1, 1900)
@DATE(-300,1,1) = -109571 (January 1, 1600)
@DATE(1299,12,31) = 474816 (December 31, 3199)
@DATEDIF - Days, Months, or Years Between Dates
Syntax
@DATEDIF(StartDate, EndDate, Format)
StartDate Date number. See "Using dates and times in Quattro Pro."
EndDate Date number.
Format Code, entered as text, specifying format of the result:
y = Years
m = Months
d = Days
md = Days, disregarding months and years
ym = Months, disregarding years
yd = Days, disregarding years
@DATEDIF calculates the number of years, months, or days between two dates. @DATEDIF uses a 365-day year and a 366-day leap year.
To find the number of days between two dates using a 360-day financial year, use @D360 or @DAYS360.
Examples
@DATEDIF(@DATE(96,3,3), @DATE(96,5,31),"d") = 89
Similarly, @DATE(96,5,31)- @DATE(96,3,3) = 89
@DATEDIF(@DATE(94,3,3), @DATE(96,5,31),"md") = 28, the number of days from the 3rd to and the 31st of any month, disregarding the difference in years
@DATEDIF(@DATE(94,3,3), @DATE(96,5,31),"ym") = 2, the number of months from March to May, disregarding the difference in years
@DATEINFO - Information About a Date
Syntax
@DATEINFO(Date, Attribute)
Date Date number. See "Using dates and times in Quattro Pro."
Attribute Code for the type of information you want:
1 = Day of the week as a label, in short format (Mon)
2 = Day of the week as a label, long format (Monday)
3 = Day of the week as an integer from 0 (Monday) through 6 (Sunday)
4 = Week of the year as an integer from 1 to 53
5 = Month of the year as a label, in short format (Jan)
6 = Month of the year as a label, in long format (January)
7 = Number of days in the month specified by date
8 = Number of days left in the month specified by date
9 = Last day of the month specified by date
10 = The Quarter date is in, as an integer from 1 (Q1) through 4 (Q4)
11 = 1 if the year specified by date is a leap year; 0 if the year is not a leap year
12 = Day of the year specified by date, as a number from 1 to 366
13 = Days left in the year specified by date, as a number
@DATEINFO returns information about a date number. @DATEINFO uses a 365-day year and a 366-day leap year.
@DATEINFO supports dates ranging from 1900-2099.
The valid date calculation range for this function is 01/01/1900 through 12/31/2099.
Examples
@DATEINFO(@DATE(96,3,3),2) = Sunday
@DATEINFO(@DATE(96,3,3),4) = 9, because March 3, 1996 is in the 9th week of the year; weeks start on Monday and end on Sunday.
@DATEINFO(@DATE(96,3,3),13) = 303, the number of days left in the year 1996
@DATEVALUE - Value Corresponding to Date
Syntax
@DATEVALUE(DateString)
DateString A numeric or string value in any valid date format, enclosed by quotation marks (or coordinates or a cell name for a selection that contains a date string).
@DATEVALUE returns a serial date value that corresponds to the value in DateString. If the value in DateString is not in the correct format or is not enclosed in quotes, ERR or a syntax error message is returned. If DateString is entered using the international format, the year, month, and day must be in the same order as the current international date format (set in Tools Settings International) and the separator character must also agree.
You can display resulting date string values in standard date formats by right-clicking cells, clicking Cell Properties, then clicking Numeric Format.
There are five valid formats for DateString:
DD-MMM-YY ("04-Jul-92").
DD-MMM ("04-Jul") (assumes the current year).
MMM-YY ("Jul-92") (assumes the first of the month).
DD-MMM-YYYY ("04-Jul-1992").
MM-YYYY ("Jul-1992") (assumes the first of the month).
The Long International date format specified as the system default, two of which are MM/DD/YY ("07/04/92") and MM/DD/YYYY ("07/04/1992").
The Short International date format specified as the system default, one of which are MM/DD ("07/18"). This assumes the current year. See "Using dates and times in Quattro Pro."
@DATEVALUE is included for compatibility with other products.
Examples
@DATEVALUE("12-Mar-2010") = 40249
@DATEVALUE("07/04/92") = 33789
@DATEVALUE("JUL-92") = 33786 (July 1, 1992)
@DATEVALUE("04-may-93") = 34093
@DATEVALUE(07/04/94) = 0.018617 (no quotes makes Quattro Pro divide the numbers)
@DATEVALUE("May-04-1992") = ERR
@DAVG - Database Average
Syntax
@DAVG(Block, Column, Criteria)
Block The 2-D cells (reference or name) containing the database, including field names.
Column The number of the column containing the field you want to average (the first column in Block is 0, the second is 1, and so on).
Criteria 2-D cells containing search criteria; the first row must be field names.
@DAVG averages selected field entries in a database. It includes only those entries in Column whose records meet the criteria specified in Criteria.
The field specified in your criteria and the field being averaged need not be the same. The field averaged is that contained within the column you specify as Column.
You can specify all or part of your database as Block, but field names must be included for each field you include in the cells.
Examples
These examples refer to the database and criteria tables.
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 LA RX $28,725
5 Jul-91 Chicago RX $18,600
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 Criteria Table
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
@DAVG(A2..D8,3,A11..A12) = $19,481 (average of July sales)
@DAVG(A2..D8,3,B11..B13) = $26,023 (average of California sales)
@DAVG(A2..D8,3, C11..C12) = $18,123 (average of CJ's sales)
@DAVG(A2..D8,4,A11..C13) = ERR (Column figure too high)
@DAVG(A2..D8,2,A11..A12) = 0 (labels are treated as 0)
@DAY - Day Portion of Date Serial Number
Syntax
@DAY(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).
@DAY converts the date/time serial number you supply as DateTimeNumber into the number associated with that day (1-31). Decimal (time) portions of the number are ignored. See "Using dates and times in Quattro Pro."
Examples
@DAY(33508) = 27 (9/27/91)
@DAY(32134) = 23 (12/23/87)
@DAY(@DATE(93,9,10)) = 10
@DAY(474817) = ERR because the number you entered was larger than 474816.9999999
@DAYS360 - Difference Between Dates
Syntax
@DAYS360(StartDate, EndDate, <Method>)
StartDate Date number. See "Using dates and times in Quattro Pro."
EndDate Date number.
Method Optional logical value that specifies US or European method:
FALSE or 0 = US (NASD); the default if you do not specify a method
TRUE or 1 = European method
@DAYS360 returns the number of days between two dates based on a 360-day year (twelve 30-day months). Use this function to help compute payments if your accounting system is based on this calendar. The formula conforms to the 1990 modifications to the Securities Industry Association's 1986 edition of Standard Security Calculation Methods.
You can express dates either as text strings or date values.
Method affects the result only in some cases when StartDate or EndDate is the 31st of the month:
Using the US method, if StartDate is the 31st, @DAYS360 makes it the 30th of the same month. If the EndDate is the 31st and the StartDate is less than the 30th of a month, @DAYS360 makes EndDate the 1st of the next month; otherwise it makes EndDate the 30th of the same month.
Using the European method, if StartDate or EndDate is the 31st, @DAYS360 makes it the 30th of the same month.
If StartDate occurs after EndDate, DAYS360 returns a negative number.
A similar function, @D360, is available. It omits the optional Method argument and will in some cases give a different result from the one returned by @DAYS360.
For a conventional year of 365 days (366 in leap years), use @DATEDIF or subtraction.
@DATE(96,5,31) - @DATE(96,3,3) = 89, using a 364-day year
@DB - Declining Balance Depreciation
Syntax
@DB(Cost, Salvage, Life, Period,<Month>)
Cost Amount originally paid for an asset.
Salvage Estimated value at end of asset life.
Life Number of periods the asset takes to depreciate to its salvage value.
Period Length of time for which you want to know the depreciation allowance.
Month Number of months in the first year (optional); If Month is omitted, @DB uses 12.
@DB calculates the depreciation of an asset over a specified period using the fixed-declining balance method. @DB uses the following formulas to calculate depreciation for a period:
(cost - total depreciation from prior periods) * rate
rate = , rounded to three decimal places
where
S salvage
C cost
L life
Depreciation for the first and last periods are special cases:
For the first period, DB uses the formula cost * rate * month / 12
For the last period, DB uses this formula:
((cost - total depreciation from prior periods) * rate * (12 - month)) / 12
Cost can be any positive value, including 0. If Cost is 0, the result of @DB is 0.
Salvage can be any positive value, but must be greater than 0, since assets should have a salvage value. If Salvage is greater than Cost, the result of @DB is negative.
Life and Period can be any positive value, but not 0. They must be in the same units, usually years. Life cannot be greater than Period.
The fixed-declining balance method slows the rate of depreciation in comparison to the double-declining balance method, so more depreciation expense can be written off in later periods. Depreciation ends when the asset's book value (asset cost minus accumulated depreciation) reaches its salvage value.
Example
Your office uses a machine purchased on July 29, 1993, for $13,250. It has an expected life of 10 years and expected salvage value of $100. Your fiscal years ends December 31, so the first period has 5 months. To calculate fourth-period depreciation for 1996:
@DB(13250,100,10,4,5) = 1616.145
@DCOUNT - Database Count
Syntax
@DCOUNT(Block, Column, Criteria)
Block The 2-D cells (reference or name) containing the database, including field names.
Column The number of the column containing the field you want to count (the first column in Block is 0, the second is 1, and so on).
Criteria 2-D cells containing search criteria; the first row must be field names.
@DCOUNT counts selected field entries in a database. It includes only those non-blank entries in Column whose records meet the criteria specified in Criteria. (If you specifically want to count blank cells, use @COUNTBLANK instead of @DCOUNT.)
The field specified in your criteria and the field being counted need not be the same. The field counted is that contained within the column you specify as Column.
You can specify all or part of your database as Block, but field names must be included for each field you include in the cells.
Examples
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 San Jose RX $25,000
5 Jul-91 Chicago RX $18,998
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 CRITERIA TABLE
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
These examples refer to the database and criteria tables.
@DCOUNT(A2..D8,3,A11..A12) = 4 (number of July sales)
@DCOUNT(A2..D8,3,B11..B13) = 2 (number of San Fran and LA sales)
@DCOUNT(A2..D8,3,C11..C12) = 3 (number of CJ's sales)
@DCOUNT(A2..D8,4,A11..C13) = ERR (Column figure too high)
@DCOUNT(A3..D8,3,A11..A12) = 6 (incorrect--field names not included)
@DDB - Double-Declining Balance Depreciation
Syntax
@DDB(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.
@DDB determines accelerated depreciation values for an asset, given the initial cost, life expectancy, end value, and depreciation period. It calculates depreciation using the double-declining balance method.
Depreciation value (DDB) and book value (BV) are calculated by:
BV Cost
DDB 2BV/Life
BV BV - DDB
These statements must be true:
Life │ Period │ 1
Life and Period must be integers
Cost │ Salvage │ 0
@SLN and @SYD offer other depreciation methods. @VDB uses the variable-rate declining balance method.
Examples
Suppose you just bought a new $4000 computer. The dealer says you can sell it back to the store for $350 after eight years, but no one would want to buy it after that. In other words, Salvage is $350 and Life is 8. To calculate the double-declining depreciation allowance of this computer by the second year, enter this formula:
@DDB(4000,350,8,2) = $750
These examples show depreciation values for the first five years of a $15,000 investment with a salvage value of $3000 and a life of 10 years:
AppName The DDE-server application to contact. The entire path to each file must be included.
Topic The table, spreadsheet, document, or other file in the DDE-server application from which to retrieve data. The entire path to each file must be included.
DataToReceive The field, cells, bookmark, or other information to receive from the application (DDE Item string).
nCols The number of columns in the data cells (optional).
nRows The number of rows in the data cells (optional).
nSheets The number of sheets in the data cells (optional).
@DDELINK creates a "live" data link from another Windows application that supports DDE (Dynamic Data Exchange). Using @DDELINK is equivalent to choosing Edit|Paste special with data from another application copied to the Clipboard, and then choosing Paste Link from the Paste special dialog box.
AppName and Topic are the same arguments used in the macro {INITIATE} except that "System" is not accepted as a substitute for Topic. DataToReceive is a string indicating the location of the target data in the server application.
When you enter @DDELINK into a cell, the linked data appears there. Unless you indicate otherwise, the data takes up as much space as it did in the original application. You can use nCols, nRows, and nSheets to specify smaller dimensions. If any of the arguments is 0 or omitted, the original dimension applies.
@DDELINK sets up a zone of cells that can be overwritten whenever data changes in the DDE-server application. Avoid storing other data near @DDELINK, and consider using the limit arguments.
Examples
The maximum size of the data cells for the following formula is 5 cells by 5 cells:
@DDELINK([EXCEL|FILE1]"R1C1:R5C5")
With nRows = 3, the maximum size of the data cells drops to 5 cells by 3 cells:
@DDELINK([EXCEL|FILE1]"R1C1:R5C5",0,3)
@DEGREES - Convert Radians to Degrees
Syntax
@DEGREES(X)
X A numeric value representing radians.
@DEGREES converts the given number of radians to degrees. It uses this formula:
180 times X divided by pi
Examples
@DEGREES(0.5) = 28.64789
@DEGREES(0.017) = 0.974028
@DEGREES(@PI/2) = 90
@DELTA - Test If Two Numbers are Equal
Syntax
@DELTA(X, <Y>)
X Numeric value to check.
Y Numeric value that X must equal for the function to return 1 (if omitted, assumed to be zero).
@DELTA tests whether X and Y are equal. If they are, @DELTA returns 1 (True); if not, @DELTA returns 0 (False).
Examples
@DELTA(1,2) = 0
@DELTA(2,2) = 1
@IF(@DELTA(2,2),"Equal","Not Equal") = Equal
@DEVSQ - Sum of the Squares of the Deviations
Syntax
@DEVSQ(List)
List One or more numeric or cell values.
@DEVSQ returns the sum of the squares of the deviations of the numbers in List from their mean value.
@DEVSQ uses this formula:
Example
@DEVSQ(9,10,12,14,15) = 26
@DFRAC - Decimal to Fraction
Syntax
@DFRAC(Dec, Denom)
Dec Number to be converted, expressed as a decimal.
Denom Denominator; must be an integer > 0.
@DFRAC converts a number expressed as a decimal to a fraction using the specified denominator. @DFRAC reverses the effect of @FRACD.
The result looks like a decimal, but the portion to the right of the decimal point is actually the numerator of the fraction using the specified denominator. For example, you can use @DFRAC to convert a decimal to 32nds. Converting 99.375 to 32nds results in 99.12, representing 99 .
Format the cell that contains the @function to show the same number of decimal places as the number of digits in the desired Denom. For example, if Denom is 32, set the cell format to display two decimal places.
Example
@DFRAC(106.4375,32) = 106.14; the 14 to the right of the decimal place signifies .
@DGET - Database Value
Syntax
@DGET(database-block,column,criteria-block)
Block The 2-D cells (reference or name) containing the database, including field names.
Column The number of the column containing the field for which you want to return a value or label (the first column in Block is 0, the second is 1, and so on).
Criteria 2-D cells containing search criteria; the first row must be field names.
@DGET returns a value or label from a field of a database table that meets specified criteria.
If no record matches the criteria, @DGET returns ERR.
If more than one record matches the criteria, @DGET returns ERR.
Examples
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 LA RX $28,725
5 Jul-91 Chicago RX $18,600
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 CRITERIA TABLE
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
@DGET(A2..D8,3,B11..B12) = $14,999 (Amount for San Fran)
@DGET(A2..D8,3,C11..C12) = ERR (Returns ERR because CJ has more than one Amount entry)
@DGET(A2..D8,3,B11..C12) = $14,999 (Amount in July for San Fran)
Settle Number representing the settlement date; must be < Maturity.
Maturity Number representing the maturity date; must be > Settle.
Price Settlement price per 100 face value; must be │ 0 and ú 100.
Redemption Redemption value per 100 face value (must be > 0; the default is 100).
Calendar Flag specifying which calendar to observe (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365; the default is 0).
@DISC computes the discount rate for a security, which is the percentage discount offered on a security for a 360-day or 365-day term.
@DISC computes the discount rate using this formula:
D discount rate
P price
R redemption
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 discount rate for a bond with the following terms: Settle is May 27, 1995, Maturity is November 24, 1995, Price is 96.2492, Redemption is 100, and Calendar is 2 (actual/360).
Block The 2-D cell cells (reference or name) containing the database, including field names.
Column The number of the column containing the field for which you want to find the maximum value (the first column in Block is 0, the second is 1, and so on).
Criteria 2-D cells containing search criteria; the first row must be field names.
@DMAX finds the maximum value of selected field entries in a database. It includes only those entries in Column whose records meet the criteria specified in Criteria.
The field specified in your criteria and the field you are finding the maximum value for need not be the same. The field you are finding the maximum value for is that contained within the column you specify as Column.
You can specify all or part of your database as Block, but field names must be included for each field you include in the cells.
Examples
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 LA RX $28,725
5 Jul-91 Chicago RX $18,600
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 CRITERIA TABLE
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
@DMAX(A2..D8,3,A11..A12) = $28,725 (highest July sale)
@DMAX(A2..D8,3,B11..B13) = $34,345 (highest California sale)
@DMAX(A2..D8,3,C11..C12) = $23,769 (highest of CJ's sales)
@DMAX(A2..D8,4,A11..C13) = ERR (Column figure too high)
@DMAX(A3..D8,3,A11..A12) = $34,345 (incorrect--field names not included)
@DMIN - Database Minimum Value
Syntax
@DMIN(Block, Column, Criteria)
Block Cells (reference or name) containing the database, including field names.
Column The number of the column containing the field for which you want to find the minimum value (the first column in Block is 0, the second is 1, and so on).
Criteria Cells containing search criteria; the first row must be field names.
@DMIN finds the minimum value of selected field entries in a database. It includes only those entries in Column whose records meet the criteria specified in Criteria.
The field specified in your criteria and the field for which you are finding the minimum value need not be the same. The field for which you are finding the minimum value is that contained within the column you specify as Column.
You can specify all or part of your database as Block, but field names must be included for each field you include in the cells.
Examples
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 LA RX $28,725
5 Jul-91 Chicago RX $18,600
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 CRITERIA TABLE
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
@DMIN(A2..D8,3,A11..A12) = $14,999 (smallest July sale)
@DMIN(A2..D8,3,B11..B13) = $14,999 (smallest California sale)
@DMIN(A2..D8,3,C11..C12) = $14,999 (smallest of CJ's sales)
@DMIN(A3..D8,3,A11..A12) = $15,600 (incorrect--field names not included)
@DMIN(A2..D8,4,A11..C13) = ERR (Column figure too high)
@DOLLAR - Dollars as Text
Syntax
@DOLLAR(Num, <Dec>)
Num Numeric value, reference to a cell that contains a numeric value, or formula that returns a numeric value.
Dec How many digits you want to display to the right of the decimal point. If Dec is negative, @DOLLAR rounds off Num to the left of the decimal point. If you omit Dec, @DOLLAR rounds off to 2 decimal places.
@DOLLAR converts a numeric value to text, using currency format. Decimals are rounded to the specified place:
0 $##,###
2 $##,###.##
-3 $##,000
You can also format numeric values by right-clicking the cell and choosing Cell Properties, then Currency. The result looks the same, but the value remains a numeric value when you use Cell Properties, while @DOLLAR converts it to text.
Examples
@DOLLAR(9449.985, 2) = "$9449.99"
@DOLLAR(9449.985, 0) = "$9450"
@DOLLAR(9449.985, -3) = "$9000"
@DOLLAR(9449.985) = "$9449.99"
@DOLLARDE - Fractional Price Into Dollars
Syntax
@DOLLARDE(FracDollar, Denom)
FracDollar Number and numerator of the fraction, expressed as number.numerator.
Denom Denominator of the fraction:
Denom must be a numeric value >0
If Denom is not an integer, @DOLLARDE truncates it
@DOLLARDE converts a fractional price into dollars. Use @DOLLARDE to convert fractional values like stock prices into dollars.
Examples
@DOLLARDE(12.3,4) = 12.75
@DOLLARDE(12.5,8) = 12.625
@DOLLARFR - Dollar Price Into Fraction
Syntax
@DOLLARFR(DecDollar, Denom)
DecDollar Dollar price.
Denom Denominator of the fraction:
Denom must be a numeric value >0
If Denom is not an integer, @DOLLARFR truncates it
@DOLLARFR converts a dollar price into a fractional price. Use @DOLLARFR to convert dollars into fractional values like stock prices. The result is expressed in the format dollar.numerator; the denominator is the number you specified for Denom.
Examples
@DOLLARFR(12.75,4) = 12.3, meaning "12 and 3/4"
@DOLLARFR(12.625,8) = 12.5, meaning "12 and 5/8"
@DOLLARTEXT - Dollar Numeric Value Into Dollar Text
Syntax
@DOLLARTEXT(Number, <Format>)
Number Numeric value, reference to a cell that contains a numeric value, or formula that returns a numeric value.
Format 1 = Displays dollar value in text; ignores decimal values.
2 = Displays dollar value in text, followed by "Dollars".
3 = Displays dollar value in text, followed by cent value in numbers. The decimal is rounded to two decimal places.
4 = Displays dollar value in text, followed by cent value in numbers, followed by "Dollars". This is the default if no Format is specified.
5 = Displays dollar value in text, followed by "Dollars", followed by cent value in text, followed by "Cents".
@DOLLARTEXT converts the numeric value to a cardinal number in text, similar to how numbers are written out on checks or official documents.
Examples
@DOLLARTEXT(100.25,1) = One Hundred
@DOLLARTEXT(100.25,2) = One Hundred Dollars
@DOLLARTEXT(956600.55,3) = Nine Hundred Fifty-Six Thousand Six Hundred and 55/100
@DOLLARTEXT(66500.70,4) = Sixty-Six Thousand Five Hundred and 70/100 Dollars
@DOLLARTEXT(66500.70,5) = Sixty-Six Thousand Five Hundred Dollars and Seventy Cents
Block The 2-D cells (reference or name) containing the database, including field names.
Column The number of the column containing the field for which you want to count all number field entries (the first column in Block is 0, the second is 1, and so on).
Criteria 2-D cells containing search criteria; the first row must be field names.
@DPURECOUNT counts all number field entries in a database that match the criteria.
You can specify all or part of your database as Block, but field names must be included for each field you include in the cells.
The field specified in your criteria and the field to be counted do not need to be the same. The field that is counted is the column you specify for Column.
Examples
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 LA RX $28,725
5 Jul-91 Chicago RX $18,600
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 CRITERIA TABLE
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
@DPURECOUNT(A2..D8,3,B11..B12) = 1 (number of San Fran sales)
@DPURECOUNT(A2..D8,3,B11..B13) = 3 (number of San Fran and LA sales)
@DPURECOUNT(A2..D8,3,A11..A12) = 4 (number of July sales)
@DPURECOUNT(A2..D8,3,C11..C12) = 3 (number of CJ's sales)
@DPURECOUNT(A2..D8,3,A11..C13)= 2 (number of CJ's sales in San Fran in July and LA sales)
@DPURECOUNT(A2..D9,3,A11..B13)= 2 (same as above example because it ignores blank cells)
@DPURECOUNT(A2..D8,1,A11..B13)= 0 (no numbers in the column to count)
@DPURECOUNT(A3..D8,3,A11..A12)= 5 (incorrect, no field names were included)
@DPRODUCT - Database product value
Syntax
@DPRODUCT(database-block,column,criteria-block)
Block The 2-D cells (reference or name) containing the database, including field names.
Column The number of the column containing the field for which you want to multiply all matching values (the first column in Block is 0, the second is 1, and so on).
Criteria 2-D cells containing search criteria; the first row must be field names.
@DPRODUCT multiplies the values in a specified field from all records in a database that match the specified criteria.
@DSTD - Database Standard Deviation
Syntax
@DSTD(Block, Column, Criteria)
Block Cells (reference or name) containing the database, including field names.
Column The number of the column containing the field for which you want to find the standard deviation (the first column in Block is 0, the second is 1, and so on).
Criteria Cells containing search criteria; the first row must be field names.
@DSTD finds the population standard deviation for selected field entries in a database. @DSTDS computes the standard deviation of sample data.
@DSTD includes only those entries in Column whose records meet the criteria specified in Criteria.
The field specified in Criteria and the field for which you are finding the standard deviation need not be the same. The field for which you are finding the standard deviation is the field contained within Column.
You can specify all or part of your database as Block, but field names must be included for each field in the cells.
Examples
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 LA RX $28,725
5 Jul-91 Chicago RX $18,600
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 CRITERIA TABLE
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
@DSTD(A2..D8,3,B11..B13) = $8,126 (population SD of California sales)
@DSTD(A2..D8,3,C11..C12) = $4,000 (population SD of CJ's sales)
@DSTD(A2..D8,4,A11..C13) = ERR (Column figure too high)
@DSTDS - Database Sample Standard Deviation
Syntax
@DSTDS(Block, Column, Criteria)
Block Cells (reference or name) containing the database, including field names.
Column The number of the column containing the field for which you want to find the standard deviation (the first column in Block is 0, the second is 1, and so on).
Criteria Cells containing search criteria; the first row must be field names.
@DSTDS finds the sample standard deviation for selected field entries in a database. @DSTD computes the standard deviation of population data.
This @function is not compatible with 1-2-3. If your file must be compatible with 1-2-3, use @DSTD instead.
Examples
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 LA RX $28,725
5 Jul-91 Chicago RX $18,600
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 CRITERIA TABLE
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
@DSTDS(A2..D8,3,B11..B13) = $9,952 (sample SD of California sales)
@DSTDS(A2..D8,3,C11..C12) = $4,899 (sample SD of CJ's sales)
@DSUM - Database Sum
Syntax
@DSUM(Block, Column, Criteria)
Block Cells (reference or name) containing the database, including field names.
Column The number of the column containing the field you want to total (the first column in Block is 0, the second is 1, and so on).
Criteria Cells containing search criteria; the first row must be field names.
@DSUM totals selected field entries in a database. It includes only those entries in Column whose records meet the criteria specified in Criteria.
The field specified in Criteria and the field you are finding the sum of need not be the same. The field you are finding the sum of is that contained within Column.
You can specify all or part of your database as Block, but field names must be included for each field you include in the cells.
Examples
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 LA RX $28,725
5 Jul-91 Chicago RX $18,600
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 CRITERIA TABLE
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
@DSUM(A2..D8,3,A11..A12) = $77,924 (total of July sales)
@DSUM(A2..D8,3,B11..B13) = $78,069 (total of California sales)
@DSUM(A2..D8,3,C11..C12) = $54,368 (total of CJ's sales)
@DSUM(A2..D8,1,A11..A12) = 0
@DSUM(A2..D8,4,A11..C13) = ERR (Column figure too high)
Discrate Discount rate or cells containing discount rates that correspond to cash flows stored in Flows.
Flows Cells containing cash flows associated with the discount rates in Discrate.
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.
Start A starting cash flow amount to compare against individual flows.
End An ending cash flow amount to compare against individual flows.
@DURAT calculates the duration of a specified cash flow structure. Duration (also called Macaulay duration) is defined as the weighted average time to receipt of a cash flow where the present values of the cash flows are the weights. Each weight in the sum is the present value of a cash flow divided by the net present value of all the cash flows.
@DURAT computes Macaulay duration using this formula:
where
Di Distance
Du Duration
Fl Flows
I Initial
n is the number of cash flows. DFi is the discount factor corresponding to the ith flow.
Modified (or Hicks) duration is defined as a sensitivity of present value to change in the internal rate of return. Modified duration is not defined for multiple discount rates (when Discrate is a selection of discount rates).
To convert Macaulay duration to Modified (or Hicks) duration, use this formula:
Consider a cash flow stream comprising four flows of $5, followed by four flows of $10, followed by seven flows of $11, followed by a final flow of $110. The first flow is 0.56745 periods away. The next 11 flows occur one period apart. The last four flows are 1.5 periods apart. This formula calculates the duration, assuming compound discounting, no initial cash flow, and 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; must be │ 0.
Yield Annual yield; must be > 0 and ú 1.
Freq Frequency of coupon payments in the 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).
@DURATION returns the Macaulay duration for a bond with an assumed par value of 100. Macaulay duration is the weighted average maturity of a bond's cash flow stream where the present values of all future cash receipts are used as weights.
Example
The following formula calculates the Macaulay 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%.
Block Cells (reference or name) containing the database, including field names.
Column The number of the column containing the field for which you want to compute variance (the first column in Block is 0, the second is 1, and so on).
Criteria Cells containing search criteria; the first row must be field names.
@DVAR calculates the population variance for selected field entries in a database. @DVARS computes the variance of sample data.
@DVAR includes only those entries in Column whose records meet the criteria specified in Criteria.
The field specified in Criteria and the field for which you are calculating the variance need not be the same. The field analyzed is the field contained within Column.
You can specify all or part of your database as Block, but field names must be included for each field you include in the cells.
Examples
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 LA RX $28,725
5 Jul-91 Chicago RX $18,600
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 CRITERIA TABLE
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
@DVAR(A2..D8,3,B11..B13) = $66,028,355 (pop. variance of Calif. sales)
@DVAR(A2..D8,3,C11..C12) = $16,000,740 (pop. variance of CJ's sales)
@DVAR(A2..D8,4,A11..C12) = ERR (Column figure too high)
@DVARS - Database Sample Variance
Syntax
@DVARS(Block, Column, Criteria)
Block Cells (reference or name) containing the database, including field names.
Column The number of the column containing the field for which you want to compute variance (the first column in Block is 0, the second is 1, and so on).
Criteria Cells containing search criteria; the first row must be field names.
@DVARS calculates the sample variance for selected field entries in a database. @DVAR computes variance with population data.
This @function is not compatible with 1-2-3. To use the file in 1-2-3, use @DVAR instead.
Examples
A B C D
1
2 DATE LOCATION REP AMOUNT
3 Jul-91 San Fran CJ $14,999
4 Jul-91 LA RX $28,725
5 Jul-91 Chicago RX $18,600
6 Jul-91 NY CJ $15,600
7 Aug-91 Chicago CJ $23,769
8 Aug-91 LA RX $34,345
9
10 CRITERIA TABLE
11 Date Location Rep
12 Jul-91 San Fran CJ
13 LA
@DVARS(A2..D8,3,B11..B13) = $99,042,532 (sample variance of Calif. sales)
@DVARS(A2..D8,3,C11..C12) = $24,001,110 (sample variance of CJ's sales)
