Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Microsoft® Excel® 2010 Formulas & Functions Inside Out
Introduction
Who This Book Is For
Assumptions About You
How This Book Is Organized
Introducing Formulas and Functions in Excel
Creating Your Own Solutions in Excel
Functions
Appendices
Features and Conventions Used in This Book
Text Conventions
Design Conventions
Your Companion eBook
Using the Sample Files
Acknowledgments
Support and Feedback
Errata & Support
We Want to Hear from You
Stay in Touch
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
Creating the Month Data Series
Creating an Individual Data Series
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
Creating a Column Chart
Creating a Pie Chart
Formatting a Chart
Working with PivotTables
Creating a PivotTable
Using a PivotTable
Using PivotCharts: Graphical Pivot
Creating a PivotChart
Changing the Original Data
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
Calculating Profitability
Using Search and Reference Functions
Searching Through a Cross Table
Using Information Functions
Using Statistical Functions
Overview
The Path to a Happy Medium
Correlation and Regression
Symmetry
Excel and Spreads
Trends and Forecasts
One Plus One
Statistical Functions in Practice
Finding the Number of Answers
Average Rating
Who Gives Better Ratings—Men or Women?
Using Database Functions
Dynamic Database Names
Database Functions in Practice
How Many Excel Seminars Took Place?
Calculating Revenue
Finding the Average Rating of the Seminars
Using Cube Functions
Using Financial Math Functions
An Annuity Calculation Example
Calculating Repayment
Calculating Exchange Rates
Investment Appraisal Example
Amortization Calculation Example
Using Math and Trigonometry Functions
Generating Random Test Data
PowerPivot
Using PowerPivot in Excel 2010
3. From Numbers to Formulas
Entering Formulas
Operators
Arithmetic Operators
Specifying the Priority of Operations by Using Parentheses
Priority of Arithmetic Operators
Comparison Operators
The & Text Operator
Reference Operators
The Range Operator
References to Entire Columns or Rows
The Connection Operator
The Intersection Operator
References in Formulas
Relative References
Absolute References
Mixed References
What Is a Circular Reference?
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
Using the Keyboard to Make a Selection
Using the Mouse to Make a Selection
Moving Formula Cells
Using the Clipboard to Move a Formula Cell
Using the Mouse to Move a Formula Cell
Copying Formula Cells
Using the Clipboard to Copy
Using the Mouse to Copy
Using the Fill Function to Copy
Using the Mouse to Fill Cells
Filling with a Double-Click
Converting Formula Results into Fixed Values
Converting Existing Values
Protecting Formulas
Hiding Formulas
Specifying the Formula Calculation Type
Analyzing Formulas
Formula Auditing
Tracing the Data and Formula Flow
The Formula Auditing Toolbar in Excel 2003
Troubleshooting: Example 1
Troubleshooting: Example 2
4. Formulas and Functions
What Is a Worksheet Function?
The Syntax of a Function
Arguments as Arithmetic Data in a Function
Options for Passing Arguments
Option 1: Values as Constants
Option 2: Values as Cell References
Option 3: Values as Addition Expressions
Option 4: Values as Range References
Including Calculations in a Function
Entering Functions
Using the Function Wizard
Hands-On Practice
Searching for Functions
Entering Cell References
Getting Excel Help for Functions
Specifying Nested Functions
Editing Formulas
Tips and Tricks
Viewing Arguments
Using a Complex Formula in Several Workbooks
Partial Calculations in Formulas
5. Functions in Special Operations
Functions in Names
Querying Current Information
Querying the Path of the Current Workbook
Querying the File Name of the Current Workbook
Querying the Current Sheet Name
Payment Targets as “Text Modules”
Dynamic Range Names
Functions for Conditional Formatting
Highlighting Weekends in Color
Highlighting Weekends and Holidays
Using the MATCH() Function
Highlighting Identical Values
When Everything Is the Same
When Only One Matches
When Two or More Are the Same
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
Highlighting the Amount or Duration
Highlighting the Result Cell and the Leader Line
Formatting Data Groups
Formatting Subtotal Results
Tips for Conditional Formatting in Excel 2003
Changing and Deleting Conditional Formats
Finding Cells Containing Conditional Formats
Using an Icon to Apply Conditional Formatting
Tips and Troubleshooting for Conditional Formatting
Functions for Validation
Cell Protection with Validation
Variable List Areas
Limiting Input with Formulas
Avoiding Duplicate Entries
Displaying Messages upon Field Completion
6. Custom Functions
Creating a Custom Function
The AreaCircle() Function
The AreaQuad() Function
Functions with Several Arguments
Functions with Optional Arguments
Branches with Logical Conditions
The AreaSect() Function
Optional Arguments with Default Values
The DigitSum() Function
Programming Loops
The AreaCircle1() Function
Using Built-in Functions in Custom Functions
The Functions in the Function Wizard
Using Your Own Functions
Saving Functions in Add-Ins
7. Date and Time Functions
DATE()
DATEDIF()
DATEVALUE()
DAY()
DAYS360()
EDATE()
EOMONTH()
HOUR()
MINUTE()
MONTH()
NETWORKDAYS()
NETWORKDAYS.INTL()
NOW()
SECOND()
TIME()
TIMEVALUE()
TODAY()
WEEKDAY()
WEEKNUM()
WORKDAY()
WORKDAY.INTL()
YEAR()
YEARFRAC()
8. Text and Data Functions
ASC()
BAHTTEXT()
CHAR()
CLEAN()
CODE()
CONCATENATE()
DOLLAR()
EXACT()
FIND(), FINDB()
FIXED()
LEFT(), LEFTB()
LEN(), LENB()
LOWER()
MID(), MIDB()
PHONETIC()
PROPER()
REPLACE(), REPLACEB()
REPT()
RIGHT(), RIGHTB()
SEARCH(), SEARCHB()
SUBSTITUTE()
T()
TEXT()
TRIM()
UPPER()
VALUE()
9. Logical Functions
AND()
FALSE()
IF()
IFERROR()
NOT()
OR()
TRUE()
10. Lookup and Reference Functions
ADDRESS()
AREAS()
CHOOSE()
COLUMN()
COLUMNS()
GETPIVOTDATA()
HLOOKUP()
HYPERLINK()
INDEX()
INDIRECT()
LOOKUP()
MATCH()
OFFSET()
ROW()
ROWS()
RTD()
TRANSPOSE()
VLOOKUP()
11. Information Functions
CELL()
COUNTBLANK()
ERROR.TYPE()
INFO()
ISBLANK()
ISERR()
ISERROR()
ISEVEN()
ISLOGICAL()
ISNA()
ISNONTEXT()
ISNUMBER()
ISODD()
ISREF()
ISTEXT()
N()
NA()
TYPE()
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
Arguments in Database Functions
Working with Databases and Records
Using Controls
Overview of the Database Functions
Functions in This Chapter
DAVERAGE()
DCOUNT()
DCOUNTA()
DGET()
DMAX()
DMIN()
DPRODUCT()
DSTDEV()
DSTDEVP()
DSUM()
DVAR()
DVARP()
GETPIVOTDATA()
14. Cube Functions
CUBEKPIMEMBER()
CUBEMEMBER()
CUBEMEMBERPROPERTY()
CUBERANKEDMEMBER()
CUBESET()
CUBESETCOUNT()
CUBEVALUE()
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
How Engineering Functions Are Organized
Conversion Functions
Functions for Complex Numbers
Functions for Higher Mathematics
Saltus Functions
Conversion Functions
Number Systems
Binary System
One’s Complement
Two’s Complement
BIN2DEC()
BIN2HEX()
BIN2OCT()
DEC2BIN()
DEC2HEX()
DEC2OCT()
HEX2BIN()
HEX2DEC()
HEX2OCT()
OCT2BIN()
OCT2DEC()
OCT2HEX()
CONVERT()
Functions for Complex Numbers
The Imaginary Part
COMPLEX()
IMABS()
IMAGINARY()
IMARGUMENT()
IMCONJUGATE()
IMCOS()
IMDIV()
IMEXP()
IMLN()
IMLOG10()
IMLOG2()
IMPOWER()
IMPRODUCT()
IMREAL()
IMSIN()
IMSQRT()
IMSUB()
IMSUM()
Functions for Higher Mathematics
Bessel Functions
Error Integrals
BESSELI()
BESSELJ()
BESSELK()
BESSELY()
ERF.PRECISE()/ERF()
ERFC.PRECISE()/ERFC()
Saltus Functions
DELTA()
GESTEP()
A. Excel Functions (in Alphabetical Order)
B. Excel Functions (by Category)
Date and Time Functions
Text and Data Functions
Logical Functions
Lookup and Reference Functions
Information Functions
Statistical Functions
Database Functions
Cube Functions
Financial Functions
Mathematical and Trigonometry Functions
Engineering Functions
C. What’s New in Excel 2007 and Excel 2010
New in Excel 2007
New Limits
New in Excel 2010
New Functions
Support for Calculation Clusters
New Solver
PowerPivot
D. About the Authors
Index
About the Authors
Copyright
← Prev
Back
Next →
← Prev
Back
Next →