Details

Excel Formulas & Functions For Dummies


Excel Formulas & Functions For Dummies


6. Aufl.

von: Ken Bluttman

28,99 €

Verlag: Wiley
Format: PDF
Veröffentl.: 10.11.2021
ISBN/EAN: 9781119839125
Sprache: englisch
Anzahl Seiten: 416

DRM-geschütztes eBook, Sie benötigen z.B. Adobe Digital Editions und eine Adobe ID zum Lesen.

Beschreibungen

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

Diese Produkte könnten Sie auch interessieren:

AutoCAD 2013 For Dummies
AutoCAD 2013 For Dummies
von: Bill Fane, David Byrnes
PDF ebook
23,99 €
IBM Workplace Services Express For Dummies
IBM Workplace Services Express For Dummies
von: Stephen R. Londergan, Michael Loria
PDF ebook
16,99 €
Building Flash Web Sites For Dummies
Building Flash Web Sites For Dummies
von: Doug Sahlin
PDF ebook
18,99 €