John Walkenbach's Favorite Excel 2010 Tips and Tricks

Chapter 5. Useful Formula Examples

In this part, you'll find many formula examples. Some are useful as is. Others can be adapted to your own needs.

Tips and Where to Find Them

• Tip 95 Calculating Holidays 205

• Tip 96 Calculating a Weighted Average 208

• Tip 97 Calculating a Person's Age 209

• Tip 98 Ranking Values 211

• Tip 99 Converting Inches to Feet and Inches 213

• Tip 100 Using the DATEDIF Function 214

• Tip 101 Counting Characters in a Cell 216

• Tip 102 Numbering Weeks 218

• Tip 103 Using a Pivot Table Instead of Formulas 220

• Tip 104 Expressing a Number as an Ordinal 224

• Tip 105 Extracting Words from a String 226

• Tip 106 Parsing Names 228

• Tip 107 Removing Titles from Names 230

• Tip 108 Generating a Series of Dates 231

• Tip 109 Determining Specific Dates 233

• Tip 110 Displaying a Calendar in a Range 236

• Tip 111 Various Methods of Rounding Numbers 237

• Tip 112 Rounding Time Values 240

• Tip 113 Using the New AGGREGATE Function 241

• Tip 114 Returning the Last Nonblank Cell in a Column or Row 244

• Tip 115 Using the COUNTIF Function 246

• Tip 116 Counting Cells That Meet Multiple Criteria 247

• Tip 117 Counting Nonduplicated Entries in a Range 251

• Tip 118 Calculating Single-Criterion Conditional Sums 252

• Tip 119 Calculating Multiple-Criterion Conditional Sums 254

• Tip 120 Looking Up an Exact Value 256

• Tip 121 Performing a Two-Way Lookup 258

• Tip 122 Performing a Two-Column Lookup 260

• Tip 123 Performing a Lookup by Using an Array 262

• Tip 124 Using the INDIRECT Function 264

• Tip 125 Creating Megaformulas 267

Calculating Holidays

Determining the ...