Excel Formulas & Functions for Dummies

Put the power of Excel functions to work in your formulas There are more than 400 built-in functions in Excel 2019, from AGGREGATE to Z.TEST. The question is which ones will make your work easier? How and why should you use a particular function in your formulas? Excel Formulas and Functions For Dum...

Descripción completa

Detalles Bibliográficos
Formato:	Libro electrónico
Idioma:	Inglés
Publicado:	Hoboken, N.J.: John Wiley & Sons c2019. Hoboken, New Jersey : 2019.
Edición:	Fifth edition
Colección:	--For dummies.
Materias:	Microsoft Excel (Computer file) Electronic spreadsheets > Computer programs. Business > Computer programs.
Ver en Biblioteca Universitat Ramon Llull:	https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009630532106719

Tabla de Contenidos:

Intro
Title Page
Copyright Page
Table of Contents
Introduction
About This Book
Foolish Assumptions
How to Use This Book
Icons Used in This Book
Where to Go from Here
Part 1 Getting Started with Formulas and Functions
Chapter 1 Tapping Into Formula and Function Fundamentals
Working with Excel Fundamentals
Understanding workbooks and worksheets
Introducing the Formulas Tab
Working with rows, column, cells, ranges, and tables
Formatting your data
Getting help
Gaining the Upper Hand on Formulas
Entering your first formula
Understanding references
Copying formulas with the fill handle
Assembling formulas the right way
Using Functions in Formulas
Looking at what goes into a function
Arguing with a function
Nesting functions
Chapter 2 Saving Time with Function Tools
Getting Familiar with the Insert Function Dialog Box
Finding the Correct Function
Entering Functions Using the Insert Function Dialog Box
Selecting a function that takes no arguments
Selecting a function that uses arguments
Entering cells, ranges, named areas, and tables as function arguments
Getting help in the Insert Function dialog box
Using the Function Arguments dialog box to edit functions
Directly Entering Formulas and Functions
Entering formulas and functions in the Formula Bar
Entering formulas and functions directly in worksheet cells
Chapter 3 Saying "Array!" for Formulas and Functions
Discovering Arrays
Using Arrays in Formulas
Working with Functions That Return Arrays
Chapter 4 Fixing Formula Boo-Boos
Catching Errors As You Enter Them
Getting parentheses to match
Avoiding circular references
Mending broken links
Using the Formula Error Checker
Auditing Formulas
Watching the Watch Window
Evaluating and Checking Errors.
Making an Error Behave the Way You Want
Part 2 Doing the Math
Chapter 5 Calculating Loan Payments and Interest Rates
Understanding How Excel Handles Money
Going with the cash flow
Formatting for currency
Choosing separators
Figuring Loan Calculations
Calculating the payment amount
Calculating interest payments
Calculating payments toward principal
Calculating the number of payments
Calculating the number of payments with PDURATION
Calculating the interest rate
Calculating the principal
Chapter 6 Appreciating What You'll Get, Depreciating What You've Got
Looking into the Future
Depreciating the Finer Things in Life
Calculating straight-line depreciation
Creating an accelerated depreciation schedule
Creating an even faster accelerated depreciation schedule
Calculating a midyear depreciation schedule
Measuring Your Internals
Chapter 7 Using Basic Math Functions
Adding It All Together with the SUM Function
Rounding Out Your Knowledge
Just plain old rounding
Rounding in one direction
Leaving All Decimals Behind with INT
Leaving Some Decimals Behind with TRUNC
Looking for a Sign
Ignoring Signs
Chapter 8 Advancing Your Math
Using PI to Calculate Circumference and Diameter
Generating and Using Random Numbers
The all-purpose RAND function
Precise randomness with RANDBETWEEN
Ordering Items
Combining
Raising Numbers to New Heights
Multiplying Multiple Numbers
Using What Remains with the MOD Function
Summing Things Up
Using SUBTOTAL
Using SUMPRODUCT
Using SUMIF and SUMIFS
Getting an Angle on Trigonometry
Three basic trigonometry functions
Degrees and radians
Part 3 Solving with Statistics
Chapter 9 Throwing Statistics a Curve
Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE
Deviating from the Middle.
Measuring variance
Analyzing deviations
Looking for normal distribution
Skewing from the norm
Comparing data sets
Analyzing Data with Percentiles and Bins
QUARTILE.INC and QUARTILE.EXC
PERCENTILE.INC and PERCENTILE.EXC
RANK
PERCENTRANK
FREQUENCY
MIN and MAX
LARGE and SMALL
Going for the Count
COUNT and COUNTA
COUNTIF
Chapter 10 Using Significance Tests
Testing to the T
Comparing Results with an Estimate
Chapter 11 Rolling the Dice on Predictions and Probability
Modeling
Linear model
Exponential model
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data
What's Ahead: Using FORECAST, TREND, and GROWTH to Make Predictions
FORECAST
TREND
GROWTH
Using NORM.DIST and POISSON.DIST to Determine Probabilities
NORM.DIST
POISSON.DIST
Part 4 Dancing with Data
Chapter 12 Dressing Up for Date Functions
Understanding How Excel Handles Dates
Formatting Dates
Making a Date with DATE
Breaking a Date with DAY, MONTH, and YEAR
Isolating the day
Isolating the month
Isolating the year
Converting a Date from Text
Finding Out What TODAY Is
Counting the days until your birthday
Counting your age in days
Determining the Day of the Week
Working with Workdays
Determining workdays in a range of dates
Workdays in the future
Calculating Time Between Two Dates with the DATEDIF Function
Chapter 13 Keeping Well-Timed Functions
Understanding How Excel Handles Time
Formatting Time
Keeping TIME
Converting Text to Time with TIMEVALUE
Deconstructing Time with HOUR, MINUTE, and SECOND
Isolating the hour
Isolating the minute
Isolating the second
Finding the Time NOW
Calculating Elapsed Time Over Days
Chapter 14 Using Lookup, Logical, and Reference Functions
Testing on One Condition.
Choosing the Right Value
Let's Be Logical
NOT
AND and OR
XOR
Finding Where the Data Is
ADDRESS
ROW, ROWS, COLUMN, and COLUMNS
OFFSET
Looking It Up
HLOOKUP and VLOOKUP
MATCH and INDEX
FORMULATEXT
NUMBERVALUE
Chapter 15 Digging Up the Facts
Getting Informed with the CELL Function
Getting Information About Excel and Your Computer System
Finding What IS and What IS Not
ISERR, ISNA, and ISERROR
ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER
Getting to Know Your Type
Chapter 16 Writing Home About Text Functions
Breaking Apart Text
Bearing to the LEFT
Swinging to the RIGHT
Staying in the MIDdle
Finding the long of it with LEN
Putting Text Together with CONCATENATE
Changing Text
Making money
Turning numbers into text
Repeating text
Swapping text
Giving text a trim
Making a case
Comparing, Finding, and Measuring Text
Going for perfection with EXACT
Finding and searching
Chapter 17 Playing Records with Database Functions
Putting Your Data into a Database Structure
Working with Database Functions
Establishing your database
Establishing the criteria area
Fine-Tuning Criteria with AND and OR
Adding Only What Matters with DSUM
Going for the Middle with DAVERAGE
Counting Only What Matters with DCOUNT
Finding Highest and Lowest with DMIN and DMAX
Finding Duplicate Values with DGET
Being Productive with DPRODUCT
Part 5 The Part of Tens
Chapter 18 Ten Tips for Working with Formulas
Master Operator Precedence
Display Formulas
Fix Formulas
Use Absolute References
Turn Calc On/Turn Calc Off
Use Named Areas
Use Formula Auditing
Use Conditional Formatting
Use Data Validation
Create Your Own Functions
Chapter 19 Ten Functions You Really Should Know
SUM
AVERAGE
COUNT
INT and ROUND.
INT
ROUND
IF
NOW and TODAY
HLOOKUP and VLOOKUP
ISNUMBER
MIN and MAX
SUMIF and COUNTIF
Chapter 20 Ten Really Cool Functions
Work with Hexadecimal, Octal, Decimal, and Binary Numbers
Convert Units of Measurement
Find the Greatest Common Divisor and the Least Common Multiple
Easily Generate a Random Number
Convert to Roman Numerals
Factor in a Factorial
Determine Part of a Year with YEARFRAC
Find the Data TYPE
Find the LENgth of Your Text
Just in CASE
Index
EULA.

Excel Formulas & Functions for Dummies

Ejemplares similares