Microsoft® Excel® 2010 Formulas & Functions Inside Out

Microsoft® Excel® 2010 Formulas & Functions Inside Out

by Helmut Reinke Egbert Jeschke Sara Unverhau, Eckehard Pfeifer, Bodo Fienitz, and Jens Bock
Released December 2011
Publisher(s): Microsoft Press
ISBN: 9780735665064

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Conquer Microsoft Excel formulas and functions—from the inside out!

You’re beyond the basics, so dive right in and really put Excel formulas and functions to work! This supremely organized reference packs hundreds of timesaving solutions, troubleshooting tips, and workarounds. It’s all muscle and no fluff. Discover how the experts increase their data analysis capabilities using Excel 2003, 2007, or 2010—and challenge yourself to new levels of mastery.

  • Customize Excel formulas using 350+ built-in functions

  • Create reusable formulas for common calculations

  • Learn smarter ways to calculate date and time values

  • Systematically search worksheets with lookup and reference functions

  • Perform advanced calculations using mathematical, statistical, and financial functions

  • Build complex formulas by nesting one function inside of another

  • Analyze profit margins and more with new functions in Excel 2010

  • Develop your own functions with Visual Basic for Applications (VBA)

    • NOTE: The sample Excel files that accompany the book were updated on 12/4/2012. Click the Companion Content link to download the files.

    Table of contents

    1. Microsoft® Excel® 2010 Formulas & Functions Inside Out
    2. Introduction
      1. Who This Book Is For
        1. Assumptions About You
      2. How This Book Is Organized
        1. Introducing Formulas and Functions in Excel
        2. Creating Your Own Solutions in Excel
        3. Functions
        4. Appendices
      3. Features and Conventions Used in This Book
        1. Text Conventions
        2. Design Conventions
      4. Your Companion eBook
      5. Using the Sample Files
      6. Acknowledgments
      7. Support and Feedback
        1. Errata & Support
        2. We Want to Hear from You
        3. Stay in Touch
    3. 1. Solving Problems with Functions
      1. Introducing the New Functions in Excel 2010
      2. Scenario and Goals
      3. The New Way to Work with Data, Formulas, and Functions
        1. Creating the Month Data Series
        2. Creating an Individual Data Series
      4. Entering Test Data Fast
      5. Converting Formula Results into Fixed Values
      6. Formatting Numeric Values
      7. Calculating Profit Margin
      8. Formatting Data as a Table
      9. Calculating Profit Margin as a Percentage
      10. Applying Conditional Formatting
      11. Creating Meaningful Charts
        1. Creating a Column Chart
        2. Creating a Pie Chart
        3. Formatting a Chart
      12. Working with PivotTables
        1. Creating a PivotTable
        2. Using a PivotTable
      13. Using PivotCharts: Graphical Pivot
        1. Creating a PivotChart
        2. Changing the Original Data
    4. 2. Using Functions and PowerPivot
      1. Using Date and Time Functions
      2. The Excel Date System
        1. Date and Time Number Formats
        2. Leap Years
        3. Analysis Functions
        4. Date and Time Functions in Practice
          1. Calculating the Start and End of Daylight Saving Time
          2. Identifying What Day of the Year Today Is
          3. Calculating with Time
          4. Calculating Working Hours
          5. Calculating Beyond the Date Limit
          6. Adding Time Intervals
          7. Calculating the Salary for Working Hours
          8. Rounding Time Values
          9. Identifying the Quarter
          10. Converting Normal Hours into Decimal Hours (Industrial Hours)
      3. Using Text and Data Functions
        1. Separating Text Strings Such as ZIP Code and Location
        2. Separating First and Last Names
        3. Switching the First and Last Names
        4. Resolving the IBAN
        5. Calculating the Frequency of a Character in a String
        6. Removing All Spaces
        7. Correcting the Position of Signs
        8. Displaying the File and Worksheet Name
        9. Concatenating the Content of Cells
        10. Breaking Lines in Concatenated Text
        11. Visualizing Data
      4. Using Logical Functions
        1. Calculating Profitability
      5. Using Search and Reference Functions
        1. Searching Through a Cross Table
      6. Using Information Functions
      7. Using Statistical Functions
        1. Overview
          1. The Path to a Happy Medium
          2. Correlation and Regression
          3. Symmetry
          4. Excel and Spreads
          5. Trends and Forecasts
          6. One Plus One
        2. Statistical Functions in Practice
          1. Finding the Number of Answers
          2. Average Rating
          3. Who Gives Better Ratings—Men or Women?
      8. Using Database Functions
        1. Dynamic Database Names
        2. Database Functions in Practice
          1. How Many Excel Seminars Took Place?
          2. Calculating Revenue
          3. Finding the Average Rating of the Seminars
      9. Using Cube Functions
      10. Using Financial Math Functions
        1. An Annuity Calculation Example
        2. Calculating Repayment
        3. Calculating Exchange Rates
        4. Investment Appraisal Example
        5. Amortization Calculation Example
      11. Using Math and Trigonometry Functions
        1. Generating Random Test Data
      12. PowerPivot
        1. Using PowerPivot in Excel 2010
    5. 3. From Numbers to Formulas
      1. Entering Formulas
      2. Operators
        1. Arithmetic Operators
          1. Specifying the Priority of Operations by Using Parentheses
          2. Priority of Arithmetic Operators
        2. Comparison Operators
        3. The & Text Operator
        4. Reference Operators
          1. The Range Operator
          2. References to Entire Columns or Rows
          3. The Connection Operator
          4. The Intersection Operator
      3. References in Formulas
        1. Relative References
        2. Absolute References
        3. Mixed References
        4. What Is a Circular Reference?
      4. Array Formulas
      5. Tips and Tricks
        1. Turning the Formula View On and Off
        2. Entering Formulas in Several Cells
        3. Entering Formulas in Several Worksheets
        4. Selecting Formula Cells
        5. Determining Which Cells Are Referenced in a Formula
        6. Copying and Moving Formulas
          1. Using the Keyboard to Make a Selection
          2. Using the Mouse to Make a Selection
        7. Moving Formula Cells
          1. Using the Clipboard to Move a Formula Cell
          2. Using the Mouse to Move a Formula Cell
        8. Copying Formula Cells
          1. Using the Clipboard to Copy
          2. Using the Mouse to Copy
          3. Using the Fill Function to Copy
          4. Using the Mouse to Fill Cells
          5. Filling with a Double-Click
        9. Converting Formula Results into Fixed Values
        10. Converting Existing Values
        11. Protecting Formulas
        12. Hiding Formulas
        13. Specifying the Formula Calculation Type
        14. Analyzing Formulas
          1. Formula Auditing
          2. Tracing the Data and Formula Flow
          3. The Formula Auditing Toolbar in Excel 2003
        15. Troubleshooting: Example 1
        16. Troubleshooting: Example 2
    6. 4. Formulas and Functions
      1. What Is a Worksheet Function?
        1. The Syntax of a Function
        2. Arguments as Arithmetic Data in a Function
        3. Options for Passing Arguments
          1. Option 1: Values as Constants
          2. Option 2: Values as Cell References
          3. Option 3: Values as Addition Expressions
          4. Option 4: Values as Range References
        4. Including Calculations in a Function
      2. Entering Functions
        1. Using the Function Wizard
        2. Hands-On Practice
        3. Searching for Functions
        4. Entering Cell References
        5. Getting Excel Help for Functions
        6. Specifying Nested Functions
        7. Editing Formulas
      3. Tips and Tricks
        1. Viewing Arguments
        2. Using a Complex Formula in Several Workbooks
        3. Partial Calculations in Formulas
    7. 5. Functions in Special Operations
      1. Functions in Names
        1. Querying Current Information
          1. Querying the Path of the Current Workbook
          2. Querying the File Name of the Current Workbook
          3. Querying the Current Sheet Name
        2. Payment Targets as “Text Modules”
        3. Dynamic Range Names
      2. Functions for Conditional Formatting
        1. Highlighting Weekends in Color
          1. Highlighting Weekends and Holidays
        2. Using the MATCH() Function
        3. Highlighting Identical Values
          1. When Everything Is the Same
          2. When Only One Matches
          3. When Two or More Are the Same
        4. Finding the Differences Between Tables on Different Worksheets
        5. List Print Layout
        6. Emphasizing the Top Three Elements
        7. Highlighting Cells Containing Spaces
        8. Navigating in Tables with Reference Lines
          1. Highlighting the Amount or Duration
          2. Highlighting the Result Cell and the Leader Line
        9. Formatting Data Groups
        10. Formatting Subtotal Results
        11. Tips for Conditional Formatting in Excel 2003
          1. Changing and Deleting Conditional Formats
          2. Finding Cells Containing Conditional Formats
          3. Using an Icon to Apply Conditional Formatting
        12. Tips and Troubleshooting for Conditional Formatting
      3. Functions for Validation
        1. Cell Protection with Validation
        2. Variable List Areas
        3. Limiting Input with Formulas
        4. Avoiding Duplicate Entries
        5. Displaying Messages upon Field Completion
    8. 6. Custom Functions
      1. Creating a Custom Function
      2. The AreaCircle() Function
      3. The AreaQuad() Function
        1. Functions with Several Arguments
        2. Functions with Optional Arguments
        3. Branches with Logical Conditions
      4. The AreaSect() Function
        1. Optional Arguments with Default Values
      5. The DigitSum() Function
        1. Programming Loops
      6. The AreaCircle1() Function
        1. Using Built-in Functions in Custom Functions
      7. The Functions in the Function Wizard
        1. Using Your Own Functions
      8. Saving Functions in Add-Ins
    9. 7. Date and Time Functions
      1. DATE()
      2. DATEDIF()
      3. DATEVALUE()
      4. DAY()
      5. DAYS360()
      6. EDATE()
      7. EOMONTH()
      8. HOUR()
      9. MINUTE()
      10. MONTH()
      11. NETWORKDAYS()
      12. NETWORKDAYS.INTL()
      13. NOW()
      14. SECOND()
      15. TIME()
      16. TIMEVALUE()
      17. TODAY()
      18. WEEKDAY()
      19. WEEKNUM()
      20. WORKDAY()
      21. WORKDAY.INTL()
      22. YEAR()
      23. YEARFRAC()
    10. 8. Text and Data Functions
      1. ASC()
      2. BAHTTEXT()
      3. CHAR()
      4. CLEAN()
      5. CODE()
      6. CONCATENATE()
      7. DOLLAR()
      8. EXACT()
      9. FIND(), FINDB()
      10. FIXED()
      11. LEFT(), LEFTB()
      12. LEN(), LENB()
      13. LOWER()
      14. MID(), MIDB()
      15. PHONETIC()
      16. PROPER()
      17. REPLACE(), REPLACEB()
      18. REPT()
      19. RIGHT(), RIGHTB()
      20. SEARCH(), SEARCHB()
      21. SUBSTITUTE()
      22. T()
      23. TEXT()
      24. TRIM()
      25. UPPER()
      26. VALUE()
    11. 9. Logical Functions
      1. AND()
      2. FALSE()
      3. IF()
      4. IFERROR()
      5. NOT()
      6. OR()
      7. TRUE()
    12. 10. Lookup and Reference Functions
      1. ADDRESS()
      2. AREAS()
      3. CHOOSE()
      4. COLUMN()
      5. COLUMNS()
      6. GETPIVOTDATA()
      7. HLOOKUP()
      8. HYPERLINK()
      9. INDEX()
      10. INDIRECT()
      11. LOOKUP()
      12. MATCH()
      13. OFFSET()
      14. ROW()
      15. ROWS()
      16. RTD()
      17. TRANSPOSE()
      18. VLOOKUP()
    13. 11. Information Functions
      1. CELL()
      2. COUNTBLANK()
      3. ERROR.TYPE()
      4. INFO()
      5. ISBLANK()
      6. ISERR()
      7. ISERROR()
      8. ISEVEN()
      9. ISLOGICAL()
      10. ISNA()
      11. ISNONTEXT()
      12. ISNUMBER()
      13. ISODD()
      14. ISREF()
      15. ISTEXT()
      16. N()
      17. NA()
      18. TYPE()
    14. 12. Statistical Functions
      1. AVEDEV()
      2. AVERAGE()
      3. AVERAGEA()
      4. AVERAGEIF()
      5. AVERAGEIFS()
      6. BETA.DIST()/BETADIST()
      7. BETA.INV()/BETAINV()
      8. BINOM.DIST()/BINOMDIST()
      9. BINOM.INV()/CRITBINOM()
      10. CHISQ.DIST()
      11. CHISQ.DIST.RT()/CHIDIST()
      12. CHISQ.INV()
      13. CHISQ.INV.RT()/CHIINV()
      14. CHISQ.TEST()/CHITEST()
      15. CONFIDENCE.NORM()/CONFIDENCE()
      16. CONFIDENCE.T()
      17. CORREL()
      18. COUNT()
      19. COUNTA()
      20. COUNTBLANK()
      21. COUNTIF()
      22. COUNTIFS()
      23. COVAR()
      24. COVARIANCE.P()
      25. COVARIANCE.S()
      26. DEVSQ()
      27. EXPON.DIST()/EXPONDIST()
      28. F.DIST()
      29. F.DIST.RT()/FDIST()
      30. F.INV()
      31. F.INV.RT()/FINV()
      32. F.TEST()/FTEST()
      33. FISHER()
      34. FISHERINV()
      35. FORECAST()
      36. FREQUENCY()
      37. GAMMA.DIST()/GAMMADIST()
      38. GAMMA.INV()/GAMMAINV()
      39. GAMMALN()
      40. GAMMALN.PRECISE()
      41. GEOMEAN()
      42. GROWTH()
      43. HARMEAN()
      44. HYPGEOM.DIST()/HYPGEOMDIST()
      45. INTERCEPT()
      46. KURT()
      47. LARGE()
      48. LINEST()
      49. LOGEST()
      50. LOGNORM.DIST()/LOGNORMDIST()
      51. LOGNORM.INV()/LOGINV()
      52. MAX()
      53. MAXA()
      54. MEDIAN()
      55. MIN()
      56. MINA()
      57. MODE.SNGL()/MODE()
      58. MODE.MULT()
      59. NEGBINOM.DIST()/NEGBINOMDIST()
      60. NORM.DIST()/NORMDIST()
      61. NORM.INV()/NORMINV()
      62. NORM.S.DIST()/NORMSDIST()
      63. NORM.S.INV()/NORMSINV()
      64. PEARSON()
      65. PERCENTILE()
      66. PERCENTILE.EXC()
      67. PERCENTILE.INC()
      68. PERCENTRANK()
      69. PERCENTRANK.EXC()
      70. PERCENTRANK.INC()
      71. PERMUT()
      72. POISSON.DIST()/POISSON()
      73. PROB()
      74. QUARTILE()
      75. QUARTILE.EXC()
      76. QUARTILE.INC()
      77. RANK()
      78. RANK.AVG()
      79. RANK.EQ()
      80. RSQ()
      81. SKEW()
      82. SLOPE()
      83. SMALL()
      84. STANDARDIZE()
      85. STDEV.P()/STDEVP()
      86. STDEV.S()/STDEV()
      87. STDEVA()
      88. STDEVPA()
      89. STEYX()
      90. T.DIST()
      91. T.DIST.RT()
      92. T.DIST.2T()/TDIST()
      93. T.INV()
      94. T.INV.2T()/TINV()
      95. T.TEST()/TTEST()
      96. TREND()
      97. TRIMMEAN()
      98. VAR.P()/VARP()
      99. VAR.S()/VAR()
      100. VARA()
      101. VARPA()
      102. WEIBULL.DIST()/WEIBULL()
      103. Z.TEST()/ZTEST()
    15. 13. Database Functions
      1. Arguments in Database Functions
      2. Working with Databases and Records
      3. Using Controls
      4. Overview of the Database Functions
      5. Functions in This Chapter
      6. DAVERAGE()
      7. DCOUNT()
      8. DCOUNTA()
      9. DGET()
      10. DMAX()
      11. DMIN()
      12. DPRODUCT()
      13. DSTDEV()
      14. DSTDEVP()
      15. DSUM()
      16. DVAR()
      17. DVARP()
      18. GETPIVOTDATA()
    16. 14. Cube Functions
      1. CUBEKPIMEMBER()
      2. CUBEMEMBER()
      3. CUBEMEMBERPROPERTY()
      4. CUBERANKEDMEMBER()
      5. CUBESET()
      6. CUBESETCOUNT()
      7. CUBEVALUE()
    17. 15. Financial Functions
      1. ACCRINT()
      2. ACCRINTM()
      3. AMORDEGRC()
      4. AMORLINC()
      5. COUPDAYBS()
      6. COUPDAYS()
      7. COUPDAYSNC()
      8. COUPNCD()
      9. COUPNUM()
      10. COUPPCD()
      11. CUMIPMT()
      12. CUMPRINC()
      13. DB()
      14. DDB()
      15. DISC()
      16. DOLLARDE()
      17. DOLLARFR()
      18. DURATION()
      19. EFFECT()
      20. FV()
      21. FVSCHEDULE()
      22. INTRATE()
      23. IPMT()
      24. IRR()
      25. ISPMT()
      26. MDURATION()
      27. MIRR()
      28. NOMINAL()
      29. NPER()
      30. NPV()
      31. ODDFPRICE()
      32. ODDFYIELD()
      33. ODDLPRICE()
      34. ODDLYIELD()
      35. PMT()
      36. PPMT()
      37. PRICE()
      38. PRICEDISC()
      39. PRICEMAT()
      40. PV()
      41. RATE()
      42. RECEIVED()
      43. SLN()
      44. SYD()
      45. TBILLEQ()
      46. TBILLPRICE()
      47. TBILLYIELD()
      48. VDB()
      49. XIRR()
      50. XNPV()
      51. YIELD()
      52. YIELDDISC()
      53. YIELDMAT()
    18. 16. Mathematical and Trigonometry Functions
      1. Functions for Mathematical Calculations
      2. Functions for Trigonometry Calculations
      3. Other Functions
      4. ABS()
      5. ACOS()
      6. ACOSH()
      7. AGGREGATE()
      8. ASIN()
      9. ASINH()
      10. ATAN()
      11. ATAN2()
      12. ATANH()
      13. CEILING()
      14. CEILING.PRECISE()
      15. COMBIN()
      16. COS()
      17. COSH()
      18. DEGREES()
      19. EVEN()
      20. EXP()
      21. FACT()
      22. FACTDOUBLE()
      23. FLOOR()
      24. FLOOR.PRECISE()
      25. GCD()
      26. INT()
      27. LCM()
      28. LN()
      29. LOG()
      30. LOG10()
      31. MDETERM()
      32. MINVERSE()
      33. MMULT()
      34. MOD()
      35. MROUND()
      36. MULTINOMIAL()
      37. ODD()
      38. PI()
      39. POWER()
      40. PRODUCT()
      41. QUOTIENT()
      42. RADIANS()
      43. RAND()
      44. RANDBETWEEN()
      45. ROMAN()
      46. ROUND()
      47. ROUNDDOWN()
      48. ROUNDUP()
      49. SERIESSUM()
      50. SIGN()
      51. SIN()
      52. SINH()
      53. SQRT()
      54. SQRTPI()
      55. SUBTOTAL()
      56. SUM()
      57. SUMIF()
      58. SUMIFS()
      59. SUMPRODUCT()
      60. SUMSQ()
      61. SUMX2MY2()
      62. SUMX2PY2()
      63. SUMXMY2()
      64. TAN()
      65. TANH()
      66. TRUNC()
    19. 17. Engineering Functions
      1. How Engineering Functions Are Organized
        1. Conversion Functions
        2. Functions for Complex Numbers
        3. Functions for Higher Mathematics
        4. Saltus Functions
      2. Conversion Functions
        1. Number Systems
        2. Binary System
          1. One’s Complement
          2. Two’s Complement
        3. BIN2DEC()
        4. BIN2HEX()
        5. BIN2OCT()
        6. DEC2BIN()
        7. DEC2HEX()
        8. DEC2OCT()
        9. HEX2BIN()
        10. HEX2DEC()
        11. HEX2OCT()
        12. OCT2BIN()
        13. OCT2DEC()
        14. OCT2HEX()
        15. CONVERT()
      3. Functions for Complex Numbers
        1. The Imaginary Part
        2. COMPLEX()
        3. IMABS()
        4. IMAGINARY()
        5. IMARGUMENT()
        6. IMCONJUGATE()
        7. IMCOS()
        8. IMDIV()
        9. IMEXP()
        10. IMLN()
        11. IMLOG10()
        12. IMLOG2()
        13. IMPOWER()
        14. IMPRODUCT()
        15. IMREAL()
        16. IMSIN()
        17. IMSQRT()
        18. IMSUB()
        19. IMSUM()
      4. Functions for Higher Mathematics
        1. Bessel Functions
        2. Error Integrals
        3. BESSELI()
        4. BESSELJ()
        5. BESSELK()
        6. BESSELY()
        7. ERF.PRECISE()/ERF()
        8. ERFC.PRECISE()/ERFC()
      5. Saltus Functions
        1. DELTA()
        2. GESTEP()
    20. A. Excel Functions (in Alphabetical Order)
    21. B. Excel Functions (by Category)
      1. Date and Time Functions
      2. Text and Data Functions
      3. Logical Functions
      4. Lookup and Reference Functions
      5. Information Functions
      6. Statistical Functions
      7. Database Functions
      8. Cube Functions
      9. Financial Functions
      10. Mathematical and Trigonometry Functions
      11. Engineering Functions
    22. C. What’s New in Excel 2007 and Excel 2010
      1. New in Excel 2007
        1. New Limits
      2. New in Excel 2010
        1. New Functions
        2. Support for Calculation Clusters
        3. New Solver
        4. PowerPivot
    23. D. About the Authors
    24. Index
    25. About the Authors
    26. Copyright

    Product information

    • Title: Microsoft® Excel® 2010 Formulas & Functions Inside Out
    • Author(s): Helmut Reinke Egbert Jeschke Sara Unverhau, Eckehard Pfeifer, Bodo Fienitz, and Jens Bock
    • Release date: December 2011
    • Publisher(s): Microsoft Press
    • ISBN: 9780735665064