Excel Formulas & Functions For Dummies
close menu
Bookswagon
search
My Account
Home > Business applications > Spreadsheet software > Excel Formulas & Functions For Dummies
Excel Formulas & Functions For Dummies

Excel Formulas & Functions For Dummies


     0     
5
4
3
2
1



Out of Stock


Notify me when this book is in stock
X
About the Book

Unlock the power of Excel with a step-by-step roadmap to its formulas and functions There's a Swiss Army knife in your digital toolbox that can multiply your productivity and make you the smartest guy or gal in almost any room. It's called Microsoft Excel. If you're like most people, you've barely scratched the surface of what this powerful tool's hundreds of built-in functions can do. But with a little help from Excel Formulas & Functions For Dummies, you'll soon be organizing, analyzing, and interpreting data like a pro. For those who don't know the difference between a spreadsheet and a bedsheet, the book gets you up to speed with formula and function basics first. But you can also skip ahead to the fancy stuff and learn about working with probabilities, significance tests, and lookup functions. This easy-to-use Excel formulas and functions survival guide shows you how to: Work with financial functions like PMT, PPMT, NPER, RATE, and PV Calculate mean, median, mode, standard deviation, and many more statistical functions Troubleshoot formulas for common errors and validate your data to avoid mistakes Work with dates, times, logic operators, conditions, and basic and advanced mathematical functions You don't need a degree in data science or advanced mathematics to take advantage of the full functionality and flexibility of Microsoft Excel. Let Excel Formulas & Functions For Dummies show you how to transform this unassuming program into the most useful tool in your toolbox.

Table of Contents:
Introduction 1 About This Book 1 Foolish Assumptions 2 Icons Used in This Book 2 Beyond the Book 3 Where to Go from Here 3 Part 1: Getting Started with Excel Formulas and Functions 5 Chapter 1: Tapping Into Formula and Function Fundamentals 7 Working with Excel Fundamentals 8 Understanding workbooks and worksheets 8 Introducing the Formulas tab 11 Working with rows, columns, cells, ranges, and tables 13 Formatting your data 18 Getting help 19 Gaining the Upper Hand on Formulas 20 Entering your first formula 20 Understanding references 22 Copying formulas with the fill handle 25 Assembling formulas the right way 26 Using Functions in Formulas 28 Looking at what goes into a function 30 Arguing with a function 31 Nesting functions 34 Chapter 2: Saving Time with Function Tools 39 Getting Familiar with the Insert Function Dialog Box 39 Finding the Correct Function 41 Entering Functions Using the Insert Function Dialog Box 42 Selecting a function that takes no arguments 43 Selecting a function that uses arguments 44 Entering cells, ranges, named areas, and tables as function arguments 47 Getting help in the Insert Function dialog box 50 Using the Function Arguments dialog box to edit functions 50 Directly Entering Formulas and Functions 51 Entering formulas and functions in the Formula Bar 51 Entering formulas and functions directly in worksheet cells 52 Chapter 3: Saying “Array!” for Formulas and Functions 55 Discovering Arrays 56 Using Arrays in Formulas 57 Working with Functions That Return Arrays 61 Chapter 4: Fixing Formula Boo-Boos 65 Catching Errors As You Enter Them 65 Getting parentheses to match 66 Avoiding circular references 68 Mending broken links 70 Using the Formula Error Checker 72 Auditing Formulas 75 Watching the Watch Window 78 Evaluating and Checking Errors 79 Making an Error Behave the Way You Want 81 Part 2: Doing the Math 83 Chapter 5: Calculating Loan Payments and Interest Rates 85 Understanding How Excel Handles Money 86 Going with the cash flow 86 Formatting for currency 86 Choosing separators 88 Figuring Loan Calculations 90 Calculating the payment amount 91 Calculating interest payments 93 Calculating payments toward principal 94 Calculating the number of payments 96 Calculating the number of payments with PDURATION 98 Calculating the interest rate 99 Calculating the principal 101 Chapter 6: Appreciating What You’ll Get, Depreciating What You’ve Got 105 Looking into the Future 106 Depreciating the Finer Things in Life 108 Calculating straight-line depreciation 110 Creating an accelerated depreciation schedule 111 Creating an even faster accelerated depreciation schedule 113 Calculating a midyear depreciation schedule 114 Measuring Your Internals 116 Chapter 7: Using Basic Math Functions 121 Adding It All Together with the SUM Function 121 Rounding Out Your Knowledge 126 Just plain old rounding 126 Rounding in one direction 128 Leaving All Decimals Behind with INT 133 Leaving Some Decimals Behind with TRUNC 134 Looking for a Sign 135 Ignoring Signs 137 Chapter 8: Advancing Your Math 139 Using PI to Calculate Circumference and Diameter 140 Generating and Using Random Numbers 141 The all-purpose RAND function 141 Precise randomness with RANDBETWEEN 143 Ordering Items 145 Combining 147 Raising Numbers to New Heights 147 Multiplying Multiple Numbers 149 Using What Remains with the MOD Function 150 Summing Things Up 152 Using SUBTOTAL 152 Using SUMPRODUCT 154 Using SUMIF and SUMIFS 156 Getting an Angle on Trigonometry 159 Three basic trigonometry functions 159 Degrees and radians 160 Part 3: Solving with Statistics 161 Chapter 9: Throwing Statistics a Curve 163 Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE 164 Deviating from the Middle 169 Measuring variance 170 Analyzing deviations 172 Looking for normal distribution 174 Skewing from the norm 179 Comparing data sets 181 Analyzing Data with Percentiles and Bins 185 QUARTILE.INC and QUARTILE.EXC 186 PERCENTILE.INC and PERCENTILE.EXC 187 RANK 189 PERCENTRANK 190 FREQUENCY 191 MIN and MAX 195 LARGE and SMALL 196 Going for the Count 198 COUNT and COUNTA 198 COUNTIF 198 Chapter 10: Using Significance Tests 203 Testing to the T 204 Comparing Results with an Estimate 208 Chapter 11: Rolling the Dice on Predictions and Probability 213 Modeling 214 Linear model 214 Exponential model 214 Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data 215 What’s Ahead: Using FORECAST, TREND, and GROWTH to Make Predictions 219 FORECAST 219 TREND 221 GROWTH 223 Using NORM.DIST and POISSON.DIST to Determine Probabilities 225 NORM.DIST 225 POISSON.DIST 227 Part 4: Dancing with Data 231 Chapter 12: Dressing Up for Date Functions 233 Understanding How Excel Handles Dates 233 Formatting Dates 235 Making a Date with DATE 236 Breaking a Date with DAY, MONTH, and YEAR 238 Isolating the day 239 Isolating the month 240 Isolating the year 241 Converting a Date from Text 242 Finding Out What TODAY Is 243 Counting the days until your birthday 244 Counting your age in days 244 Determining the Day of the Week 245 Working with Workdays 246 Determining workdays in a range of dates 246 Workdays in the future 248 Calculating Time Between Two Dates with the DATEDIF Function 249 Chapter 13: Keeping Well-Timed Functions 251 Understanding How Excel Handles Time 251 Formatting Time 252 Keeping TIME 254 Converting Text to Time with TIMEVALUE 254 Deconstructing Time with HOUR, MINUTE, and SECOND 255 Isolating the hour 256 Isolating the minute 257 Isolating the second 258 Finding the Time NOW 258 Calculating Elapsed Time Over Days 259 Chapter 14: Using Lookup, Logical, and Reference Functions 261 Testing on One Condition 262 Choosing the Right Value 267 Let’s Be Logical 269 NOT 270 AND and OR 271 XOR 273 Finding Where the Data Is 275 ADDRESS 275 INDIRECT 279 ROW, ROWS, COLUMN, and COLUMNS 279 OFFSET 282 Looking It Up 284 HLOOKUP and VLOOKUP 284 XLOOKUP 288 MATCH and INDEX 289 FORMULATEXT 294 NUMBERVALUE 295 Chapter 15: Digging Up the Facts 297 Getting Informed with the CELL Function 297 Getting Information About Excel and Your Computer System 302 Finding What IS and What IS Not 304 ISERR, ISNA, and ISERROR 304 ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER 306 Getting to Know Your Type 308 Chapter 16: Writing Home about Text Functions 311 Breaking Apart Text 311 Bearing to the LEFT 312 Swinging to the RIGHT 313 Staying in the MIDdle 314 Finding the long of it with LEN 315 Putting Text Together with CONCATENATE 316 Changing Text 318 Making money 318 Turning numbers into text 320 Repeating text 323 Swapping text 324 Giving text a trim 328 Making a case 329 Comparing, Finding, and Measuring Text 330 Going for perfection with EXACT 331 Finding and searching 332 Chapter 17: Playing Records with Database Functions 337 Putting Your Data into a Database Structure 338 Working with Database Functions 339 Establishing your database 339 Establishing the criteria area 341 Fine-Tuning Criteria with AND and OR 343 Adding Only What Matters with DSUM 345 Going for the Middle with DAVERAGE 345 Counting Only What Matters with DCOUNT 347 Finding Highest and Lowest with DMIN and DMAX 348 Finding Duplicate Values with DGET 349 Being Productive with DPRODUCT 350 Part 5: The Part of Tens 353 Chapter 18: Ten Tips for Working with Formulas 355 Master Operator Precedence 355 Display Formulas 356 Fix Formulas 358 Use Absolute References 359 Turn Calc On/Turn Calc Off 359 Use Named Areas 361 Use Formula Auditing 362 Use Conditional Formatting 363 Use Data Validation 364 Create Your Own Functions 365 Chapter 19: Ten Ways to Get Fancy with Excel 369 Calculating Data from Multiple Sheets 369 Getting Data from the Internet 370 Determining the Needed Number 370 Removing Duplicates 371 Getting to the Last Row of Your Data 372 Freezing Panes 372 Splitting a Worksheet 372 Filling Cells 373 Adding Notes to Cells 373 Getting More Information about a Workbook or Worksheet 374 Chapter 20: Ten Really Cool Functions 375 Work with Hexadecimal, Octal, Decimal, and Binary Numbers 376 Convert Units of Measurement 377 Find the Greatest Common Divisor and the Least Common Multiple 378 Easily Generate a Random Number 379 Convert to Roman Numerals 379 Factor in a Factorial 379 Determine Part of a Year with YEARFRAC 380 Find the Data TYPE 380 Find the LENgth of Your Text 381 Just in CASE 381 Index 383 

About the Author :
Ken Bluttman is a veteran software and web developer specializing in Excel/VBA and database-centric web applications. He has written articles and books on topics like Office/VBA development, XML, SQL Server, and InfoPath. Ken is the author of Excel Charts For Dummies and all previous editions of Excel Formulas & Functions For Dummies.


Best Sellers


Product Details
  • ISBN-13: 9781119839125
  • Publisher: John Wiley & Sons Inc
  • Publisher Imprint: Standards Information Network
  • Edition: Revised edition
  • No of Pages: 416
  • ISBN-10: 1119839122
  • Publisher Date: 10 Nov 2021
  • Binding: Digital (delivered electronically)
  • Language: English


Similar Products

Add Photo
Add Photo

Customer Reviews

REVIEWS      0     
Click Here To Be The First to Review this Product
Excel Formulas & Functions For Dummies
John Wiley & Sons Inc -
Excel Formulas & Functions For Dummies
Writing guidlines
We want to publish your review, so please:
  • keep your review on the product. Review's that defame author's character will be rejected.
  • Keep your review focused on the product.
  • Avoid writing about customer service. contact us instead if you have issue requiring immediate attention.
  • Refrain from mentioning competitors or the specific price you paid for the product.
  • Do not include any personally identifiable information, such as full names.

Excel Formulas & Functions For Dummies

Required fields are marked with *

Review Title*
Review
    Add Photo Add up to 6 photos
    Would you recommend this product to a friend?
    Tag this Book Read more
    Does your review contain spoilers?
    What type of reader best describes you?
    I agree to the terms & conditions
    You may receive emails regarding this submission. Any emails will include the ability to opt-out of future communications.

    CUSTOMER RATINGS AND REVIEWS AND QUESTIONS AND ANSWERS TERMS OF USE

    These Terms of Use govern your conduct associated with the Customer Ratings and Reviews and/or Questions and Answers service offered by Bookswagon (the "CRR Service").


    By submitting any content to Bookswagon, you guarantee that:
    • You are the sole author and owner of the intellectual property rights in the content;
    • All "moral rights" that you may have in such content have been voluntarily waived by you;
    • All content that you post is accurate;
    • You are at least 13 years old;
    • Use of the content you supply does not violate these Terms of Use and will not cause injury to any person or entity.
    You further agree that you may not submit any content:
    • That is known by you to be false, inaccurate or misleading;
    • That infringes any third party's copyright, patent, trademark, trade secret or other proprietary rights or rights of publicity or privacy;
    • That violates any law, statute, ordinance or regulation (including, but not limited to, those governing, consumer protection, unfair competition, anti-discrimination or false advertising);
    • That is, or may reasonably be considered to be, defamatory, libelous, hateful, racially or religiously biased or offensive, unlawfully threatening or unlawfully harassing to any individual, partnership or corporation;
    • For which you were compensated or granted any consideration by any unapproved third party;
    • That includes any information that references other websites, addresses, email addresses, contact information or phone numbers;
    • That contains any computer viruses, worms or other potentially damaging computer programs or files.
    You agree to indemnify and hold Bookswagon (and its officers, directors, agents, subsidiaries, joint ventures, employees and third-party service providers, including but not limited to Bazaarvoice, Inc.), harmless from all claims, demands, and damages (actual and consequential) of every kind and nature, known and unknown including reasonable attorneys' fees, arising out of a breach of your representations and warranties set forth above, or your violation of any law or the rights of a third party.


    For any content that you submit, you grant Bookswagon a perpetual, irrevocable, royalty-free, transferable right and license to use, copy, modify, delete in its entirety, adapt, publish, translate, create derivative works from and/or sell, transfer, and/or distribute such content and/or incorporate such content into any form, medium or technology throughout the world without compensation to you. Additionally,  Bookswagon may transfer or share any personal information that you submit with its third-party service providers, including but not limited to Bazaarvoice, Inc. in accordance with  Privacy Policy


    All content that you submit may be used at Bookswagon's sole discretion. Bookswagon reserves the right to change, condense, withhold publication, remove or delete any content on Bookswagon's website that Bookswagon deems, in its sole discretion, to violate the content guidelines or any other provision of these Terms of Use.  Bookswagon does not guarantee that you will have any recourse through Bookswagon to edit or delete any content you have submitted. Ratings and written comments are generally posted within two to four business days. However, Bookswagon reserves the right to remove or to refuse to post any submission to the extent authorized by law. You acknowledge that you, not Bookswagon, are responsible for the contents of your submission. None of the content that you submit shall be subject to any obligation of confidence on the part of Bookswagon, its agents, subsidiaries, affiliates, partners or third party service providers (including but not limited to Bazaarvoice, Inc.)and their respective directors, officers and employees.

    Accept

    Fresh on the Shelf


    Inspired by your browsing history


    Your review has been submitted!

    You've already reviewed this product!
    Your IP: 216.73.216.196 IN