Microsoft Office Tutorials and References
In Depth Information
APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE
etc. If you have the space to enter a criteria range, this function certainly did
SUMIFS before there was a SUMIFS. It also replaces the need for AVERAGEIF,
STDDEVIF, VARIANCEIF, COUNTAIF, etc. The ﬁ gure below shows the DSUM
result for several types of criteria ranges. In J4, the DSUM adds up all records
where the region is East. In J7, the criteria range ﬁ nds East & Central region
sales of product A. In J11, the formula adds up Joe’s sales of product A in the
East and West. Notice the criteria range in F14:F15. The heading in F14 is left
blank and cell F15 contains a formula which points to the ﬁ rst data row of the
database. In this image, the formula is =D4<32 and causes Excel to pull all of
the records where column D is less than 32. You can use complex formula here
such as =NOT(ISNA(VLOOKUP())) to ﬁ nd records that match a list.
DURATION(settlement,maturity,coupon yld,frequency,basis) [Category:
Returns the Macauley duration for an assumed par value of $100. Duration is
deﬁ ned as the weighted average of the present value of the cash ﬂ ows and is
used as a measure of a bond price’s response to changes in yield.
DVAR(database,ﬁ eld,criteria) [Category: Database]
Estimates the variance of a population based on a sample, using the numbers
in a column in a list or database that match conditions that you specify. Guru
Tip: See DSUM.
DVARP(database,ﬁ eld,criteria) [Category: Database]
Calculates the variance of a population based on the entire population, using
the numbers in a column in a list or database that match conditions that you
specify. Guru Tip: See DSUM.