Overview of the Database Functions
by Helmut Reinke Egbert Jeschke Sara Unverhau, Eckehard Pfeifer, Bodo Fienitz, and
Microsoft® Excel® 2010 Formulas & Functions Inside Out
- Microsoft® Excel® 2010 Formulas & Functions Inside Out
- A Note Regarding Supplemental Files
- Introduction
- 1. Solving Problems with Functions
- Introducing the New Functions in Excel 2010
- Scenario and Goals
- The New Way to Work with Data, Formulas, and Functions
- Entering Test Data Fast
- Converting Formula Results into Fixed Values
- Formatting Numeric Values
- Calculating Profit Margin
- Formatting Data as a Table
- Calculating Profit Margin as a Percentage
- Applying Conditional Formatting
- Creating Meaningful Charts
- Working with PivotTables
- Using PivotCharts: Graphical Pivot
- 2. Using Functions and PowerPivot
- Using Date and Time Functions
- The Excel Date System
- Date and Time Number Formats
- Leap Years
- Analysis Functions
- Date and Time Functions in Practice
- Calculating the Start and End of Daylight Saving Time
- Identifying What Day of the Year Today Is
- Calculating with Time
- Calculating Working Hours
- Calculating Beyond the Date Limit
- Adding Time Intervals
- Calculating the Salary for Working Hours
- Rounding Time Values
- Identifying the Quarter
- Converting Normal Hours into Decimal Hours (Industrial Hours)
- Using Text and Data Functions
- Separating Text Strings Such as ZIP Code and Location
- Separating First and Last Names
- Switching the First and Last Names
- Resolving the IBAN
- Calculating the Frequency of a Character in a String
- Removing All Spaces
- Correcting the Position of Signs
- Displaying the File and Worksheet Name
- Concatenating the Content of Cells
- Breaking Lines in Concatenated Text
- Visualizing Data
- Using Logical Functions
- Using Search and Reference Functions
- Using Information Functions
- Using Statistical Functions
- Using Database Functions
- Using Cube Functions
- Using Financial Math Functions
- Using Math and Trigonometry Functions
- PowerPivot
- 3. From Numbers to Formulas
- Entering Formulas
- Operators
- References in Formulas
- Array Formulas
- Tips and Tricks
- Turning the Formula View On and Off
- Entering Formulas in Several Cells
- Entering Formulas in Several Worksheets
- Selecting Formula Cells
- Determining Which Cells Are Referenced in a Formula
- Copying and Moving Formulas
- Moving Formula Cells
- Copying Formula Cells
- Converting Formula Results into Fixed Values
- Converting Existing Values
- Protecting Formulas
- Hiding Formulas
- Specifying the Formula Calculation Type
- Analyzing Formulas
- Troubleshooting: Example 1
- Troubleshooting: Example 2
- 4. Formulas and Functions
- 5. Functions in Special Operations
- Functions in Names
- Functions for Conditional Formatting
- Highlighting Weekends in Color
- Using the MATCH() Function
- Highlighting Identical Values
- Finding the Differences Between Tables on Different Worksheets
- List Print Layout
- Emphasizing the Top Three Elements
- Highlighting Cells Containing Spaces
- Navigating in Tables with Reference Lines
- Formatting Data Groups
- Formatting Subtotal Results
- Tips for Conditional Formatting in Excel 2003
- Tips and Troubleshooting for Conditional Formatting
- Functions for Validation
- 6. Custom Functions
- 7. Date and Time Functions
- 8. Text and Data Functions
- 9. Logical Functions
- 10. Lookup and Reference Functions
- 11. Information Functions
- 12. Statistical Functions
- AVEDEV()
- AVERAGE()
- AVERAGEA()
- AVERAGEIF()
- AVERAGEIFS()
- BETA.DIST()/BETADIST()
- BETA.INV()/BETAINV()
- BINOM.DIST()/BINOMDIST()
- BINOM.INV()/CRITBINOM()
- CHISQ.DIST()
- CHISQ.DIST.RT()/CHIDIST()
- CHISQ.INV()
- CHISQ.INV.RT()/CHIINV()
- CHISQ.TEST()/CHITEST()
- CONFIDENCE.NORM()/CONFIDENCE()
- CONFIDENCE.T()
- CORREL()
- COUNT()
- COUNTA()
- COUNTBLANK()
- COUNTIF()
- COUNTIFS()
- COVAR()
- COVARIANCE.P()
- COVARIANCE.S()
- DEVSQ()
- EXPON.DIST()/EXPONDIST()
- F.DIST()
- F.DIST.RT()/FDIST()
- F.INV()
- F.INV.RT()/FINV()
- F.TEST()/FTEST()
- FISHER()
- FISHERINV()
- FORECAST()
- FREQUENCY()
- GAMMA.DIST()/GAMMADIST()
- GAMMA.INV()/GAMMAINV()
- GAMMALN()
- GAMMALN.PRECISE()
- GEOMEAN()
- GROWTH()
- HARMEAN()
- HYPGEOM.DIST()/HYPGEOMDIST()
- INTERCEPT()
- KURT()
- LARGE()
- LINEST()
- LOGEST()
- LOGNORM.DIST()/LOGNORMDIST()
- LOGNORM.INV()/LOGINV()
- MAX()
- MAXA()
- MEDIAN()
- MIN()
- MINA()
- MODE.SNGL()/MODE()
- MODE.MULT()
- NEGBINOM.DIST()/NEGBINOMDIST()
- NORM.DIST()/NORMDIST()
- NORM.INV()/NORMINV()
- NORM.S.DIST()/NORMSDIST()
- NORM.S.INV()/NORMSINV()
- PEARSON()
- PERCENTILE()
- PERCENTILE.EXC()
- PERCENTILE.INC()
- PERCENTRANK()
- PERCENTRANK.EXC()
- PERCENTRANK.INC()
- PERMUT()
- POISSON.DIST()/POISSON()
- PROB()
- QUARTILE()
- QUARTILE.EXC()
- QUARTILE.INC()
- RANK()
- RANK.AVG()
- RANK.EQ()
- RSQ()
- SKEW()
- SLOPE()
- SMALL()
- STANDARDIZE()
- STDEV.P()/STDEVP()
- STDEV.S()/STDEV()
- STDEVA()
- STDEVPA()
- STEYX()
- T.DIST()
- T.DIST.RT()
- T.DIST.2T()/TDIST()
- T.INV()
- T.INV.2T()/TINV()
- T.TEST()/TTEST()
- TREND()
- TRIMMEAN()
- VAR.P()/VARP()
- VAR.S()/VAR()
- VARA()
- VARPA()
- WEIBULL.DIST()/WEIBULL()
- Z.TEST()/ZTEST()
- 13. Database Functions
- 14. Cube Functions
- 15. Financial Functions
- ACCRINT()
- ACCRINTM()
- AMORDEGRC()
- AMORLINC()
- COUPDAYBS()
- COUPDAYS()
- COUPDAYSNC()
- COUPNCD()
- COUPNUM()
- COUPPCD()
- CUMIPMT()
- CUMPRINC()
- DB()
- DDB()
- DISC()
- DOLLARDE()
- DOLLARFR()
- DURATION()
- EFFECT()
- FV()
- FVSCHEDULE()
- INTRATE()
- IPMT()
- IRR()
- ISPMT()
- MDURATION()
- MIRR()
- NOMINAL()
- NPER()
- NPV()
- ODDFPRICE()
- ODDFYIELD()
- ODDLPRICE()
- ODDLYIELD()
- PMT()
- PPMT()
- PRICE()
- PRICEDISC()
- PRICEMAT()
- PV()
- RATE()
- RECEIVED()
- SLN()
- SYD()
- TBILLEQ()
- TBILLPRICE()
- TBILLYIELD()
- VDB()
- XIRR()
- XNPV()
- YIELD()
- YIELDDISC()
- YIELDMAT()
- 16. Mathematical and Trigonometry Functions
- Functions for Mathematical Calculations
- Functions for Trigonometry Calculations
- Other Functions
- ABS()
- ACOS()
- ACOSH()
- AGGREGATE()
- ASIN()
- ASINH()
- ATAN()
- ATAN2()
- ATANH()
- CEILING()
- CEILING.PRECISE()
- COMBIN()
- COS()
- COSH()
- DEGREES()
- EVEN()
- EXP()
- FACT()
- FACTDOUBLE()
- FLOOR()
- FLOOR.PRECISE()
- GCD()
- INT()
- LCM()
- LN()
- LOG()
- LOG10()
- MDETERM()
- MINVERSE()
- MMULT()
- MOD()
- MROUND()
- MULTINOMIAL()
- ODD()
- PI()
- POWER()
- PRODUCT()
- QUOTIENT()
- RADIANS()
- RAND()
- RANDBETWEEN()
- ROMAN()
- ROUND()
- ROUNDDOWN()
- ROUNDUP()
- SERIESSUM()
- SIGN()
- SIN()
- SINH()
- SQRT()
- SQRTPI()
- SUBTOTAL()
- SUM()
- SUMIF()
- SUMIFS()
- SUMPRODUCT()
- SUMSQ()
- SUMX2MY2()
- SUMX2PY2()
- SUMXMY2()
- TAN()
- TANH()
- TRUNC()
- 17. Engineering Functions
- A. Excel Functions (in Alphabetical Order)
- B. Excel Functions (by Category)
- C. What’s New in Excel 2007 and Excel 2010
- D. About the Authors
- Index
- About the Authors
- Copyright
This section gives you an overview of the individual database functions and how to use them, as described in Table 13-3.
Table 13-3. Overview of the Database Functions
Returns... | |
|---|---|
DAVERAGE() | ... the mean of the values matching the condition(s). |
DCOUNT() | ... the number of fields matching the condition(s). |
DCOUNTA() | ... the number of nonempty fields matching the condition(s). |
DGET() | ... a single value matching the condition(s). |
DMAX() | ... the largest value matching the condition(s). |
DMIN() | ... the smallest value matching the condition(s). |
DPRODUCT() | ... the product of the values matching the condition(s). |
DSTDEV() | ... the estimated standard deviation of the values from a sample matching the condition(s). |
DSTDEVP() | ... the standard deviation of the values matching the condition(s). |
DSUM() | ... the sum of the values matching the condition(s). |
DVAR() | ... the estimated variance of the values from a sample matching the condition(s). |
DVARP() | ... the variance of the values matching the condition(s). |
GETPIVOTDATA() | ... the values from a PivotTable report. |
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.