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:

Financial]*

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.