| Chapter 1 Working With Formulas |
1 |
| Inserting, Editing, & Copying Formulas |
2 |
| Inserting/Editing Formulas |
2 |
| Nesting Formulas |
3 |
| Changing an Absolute Reference to a Relative Reference or Vice Versa |
4 |
| Copying a Formula from a Cell While Keeping the Absolute Reference or Relative Reference |
5 |
| Copying Formulas from a Range of Cells without Changing the Absolute or Relative References |
6 |
| Selecting, Displaying, Printing, & Pasting Formulas |
7 |
| Displaying Formula Syntax |
7 |
| Displaying Both Formulas and Values for Cells |
8 |
| Selecting Cells That Contain Formulas |
9 |
| Pasting Values |
10 |
| Adding a Comment to a Formula |
11 |
| Printing Formula Syntax |
11 |
| Array Formulas |
12 |
| Understanding Arrays |
12 |
| Using Arrays in Formulas |
12 |
| Using Range Names in Formulas |
13 |
| Range Name Syntax |
13 |
| Defining a Range Name |
14 |
| Deleting a Range Name |
15 |
| Using a Range Name in a Formula |
15 |
| Auditing Formulas |
16 |
| Moving Between Precedent and Dependent Cells |
16 |
| Stepping into a Formula |
19 |
| Tracing Errors in Formula Results |
21 |
| Protecting Formulas |
22 |
| Protecting Cells Containing Formulas in a Protected Sheet |
22 |
| Protecting Cells Containing Formulas in an Unprotected Sheet |
24 |
| |
| Chapter 2 Text |
25 |
| Entering Text |
27 |
| Restricting Cell Entries to Text Only |
27 |
| Restricting the Number of Characters Entered into a Cell |
28 |
| Preventing Duplicates When Entering Data |
29 |
| Combining Text, Date, & Number |
30 |
| Combining Text and Formatted Numbers into a Single Entity |
30 |
| Combining Text and a Formatted Date into a Single Entity |
31 |
| Combining Numbers that Have Leading Zeros |
32 |
| Combining the Contents of Every N Number of Cells into One Value |
33 |
| Encoding a Sequence of Letters |
34 |
| Combining Text and Numerically Calculated Results into One Output String |
35 |
| Text Formatting – Troubleshooting |
36 |
| Formulas Erroneously Appearing as Text |
36 |
| Avoiding Problems when Numeric Values are used as Text |
37 |
| Adjusting a Formula to Return a Blank, Rather than a Zero, When Operating on Empty Cells |
39 |
| Checking Whether Cells in a Range are Blank, and Counting the Blank Cells |
40 |
| Creating Formulas that Only Return Results from Non-Blank Cells |
41 |
| Removing Redundant Characters from a Range of Cells and Resetting the Format |
43 |
| Removing Hidden Apostrophes from Imported Numbers |
44 |
| Subdividing Text |
45 |
| Subdividing a Mixed String into Separate Cells Containing only Numbers or Characters |
45 |
| Splitting a Full Address into Three Separate Cells |
47 |
| Separating First and Last Names |
48 |
| Extracting the First N Number of Words from a String |
49 |
| Replacing Characters |
50 |
| Creating New IP Addresses |
50 |
| Converting Numbers with Trailing Minus Signs to Normal Negative Numbers |
51 |
| Replacing Substrings with Numbers from Adjacent Cells |
53 |
| Performing Numeric Operations on Substrings |
54 |
| Searching a String |
55 |
| Searching a String for a Matching Word from another String |
55 |
| Find the Alphabetically Lowest Letter in a List |
56 |
| Identifying Numeric Values within a Range Formatted as Text |
58 |
| Identifying the Case of Letters within Strings |
59 |
| Finding the Relative Position of Numbers within a String |
60 |
| Searching a String for a Specific Substring |
61 |
| Determining Long Distance Phone Calls, Based on the Number Dialed |
63 |
| Rearranging & Sorting Text |
64 |
| Rearranging a String in Alphabetic Order |
64 |
| Reversing the Word Order within a String |
65 |
| Retrieving Cell Address, Row Number |
66 |
| Retrieving the Cell Address of the Largest Value in a Range |
66 |
| Retrieving Each Row Number that Corresponds with Successful Matches in a Look Up |
67 |
| Retrieving the Row Number that Corresponds with a Matched Value in a Look Up |
68 |
| |
| Chapter 3 Date & Time |
71 |
| Date |
72 |
| Entering Dates Quickly |
72 |
| Calculating Number of Days, Weeks, Months and Years between Dates |
73 |
| Calculating a Date Based on Year, Week Number and Day of the Week |
74 |
| Finding the Last Day of a Given Month |
75 |
| Calculating the Number of Business Days in a Specified Period |
77 |
| Calculating a Project's End Date |
78 |
| Calculating a Required Date According to Two Criteria |
79 |
| Indicating Due Payments, Based on the Current Date |
80 |
| Calculating the Date of the Nth Specific Day of the Month |
81 |
| Eliminating Negative Values from Date Subtractions |
83 |
| Avoiding Negative Values when Calculating the Number of Days between Two Dates |
84 |
| Avoiding False Results when Counting Dates Matching Specified Criteria |
85 |
| Calculating the Week Number Corresponding with a Specific Date |
87 |
| Determining whether Two Given Dates Occur within the Same Week |
88 |
| Finding the Number of Days in a Given Month |
89 |
| Finding the Serial Number of the Last Day of the Month |
90 |
| Adding a Specified Number of Months to a Date |
92 |
| Converting a Month's Serial Number into Its Corresponding Name |
94 |
| Calculating the Quarter Number for Calendar and Fiscal Year |
95 |
| Converting a Date into a String, Indicating the Quarter Number and Year |
97 |
| Determining Which Quarter a Specified Date Corresponds With |
98 |
| Converting Older Dates to the Current Year |
99 |
| Converting a Julian Date to a Calendar Date |
100 |
| Time |
102 |
| Entering Times Quickly (1) |
102 |
| Entering Times Quickly (2) |
103 |
| Adding Time Values |
106 |
| Adding Text Values Representing Time |
106 |
| Adding Time Values from Separate Hours and Minutes Columns |
107 |
| Adding a Number to a Formatted Time Value |
108 |
| Calculating Absolute Difference between Two Time Values |
109 |
| Subtracting Times |
111 |
| Converting a Decimal Value to a Time Value |
112 |
| Converting Text Representing Minutes and Seconds into Numerical Values |
113 |
| Converting Times to Decimal Values |
114 |
| Calculating Military Time Intervals |
115 |
| Converting Time Values from One Time Zone to Another |
117 |
| Creating a World Time Converter |
118 |
| Rounding Times Down to the Nearest 30 Seconds |
119 |
| Rounding Times Down to a Specified Time Increment |
120 |
| Rounding Hours Up |
121 |
| Rounding Time Intervals to the Nearest Specified Time Increment |
122 |
| Calculating a Building's Occupancy Rate at Specified Times |
123 |
| Date & Time |
125 |
| Creating Date and Time Stamp |
125 |
| Convert Date and Time from GMT (Greenwich Mean Time) to CST (Central Standard Time) |
126 |
| Combining Data from Separate Columns into a Single Date and Time Value |
127 |
| Converting Text, Which Represents Date and Time, Into Proper Date and Time Values |
128 |
| Calculating the Number of Weekday Hours between Two Dates |
130 |
| Separating Dates and Times |
131 |
| Creating a Date and Time Matrix |
132 |
| Wages, Shifts & Time Worked |
135 |
| Creating a Timesheet |
135 |
| Finding the First Login and Last Logout Times of Employees |
136 |
| Calculating Total Time Worked on a Specific Day |
139 |
| Calculating Hours Worked |
140 |
| Calculating the Number of Hours Worked Based on a System of Letter Codes |
143 |
| Calculating Total Pay, Based on Hours Worked Per Day and Hourly Rates |
144 |
| Calculating Daily Pay, Incorporating Variable Hourly Rates |
147 |
| Determining Whether a Person Worked a Full or Partial Shift |
149 |
| Determining Whether a Worked Shift Was Morning, Afternoon or Night |
151 |
| Counting the Number of Shifts in a Specified Week that an Employee Worked Overtime |
152 |
| Calculating the Number of Hours per Month, Allowing for Daylight-Saving Shifts |
154 |
| Counting Vacation or Sick Days within a Specified Time Period |
156 |
| Calculating Hourly Productivity |
157 |
| |
| Chapter 4 Lookup |
159 |
| Retrieving Data Using Text References |
160 |
| Retrieving Values Using References Based on Row and Column Numbers |
160 |
| Retrieving Each Second Number from Two Lists |
161 |
| Retrieving Values Using Range Names as References (1) |
162 |
| Retrieving Values Using Range Names as References (2) |
163 |
| Retrieving Values from Different Sheets Using the Sheet Name as a Reference |
164 |
| Retrieving Values from another Sheet Using Date as Sheet Name Reference |
165 |
| Retrieving a Specific Cell from Multiple Sheets |
166 |
| Retrieving Values from another Excel Workbook Using Text References |
167 |
| Retrieving the Price for a Specified Item and a Specified Brand |
168 |
| Retrieving Data Using One Criterion |
170 |
| Retrieving from List by Position |
170 |
| Ranking Salespeople According to Sales Figure |
170 |
| Retrieving the Player Who Scored the Highest Number of Points |
172 |
| Retrieving File Name by Matching a Given Page Number in a Book |
173 |
| Retrieving Grade Value for Corresponding Mark |
174 |
| Retrieving the Most Frequent Occurrence for Each Category |
175 |
| Retrieving the Last Value and the Last Character in a Row |
177 |
| Retrieving the Last Value in a Dynamic List |
178 |
| Retrieving the Value of the First Non-blank Cell in a List |
179 |
| Retrieving the Most Recent Payment Made by Client |
180 |
| Retrieving the Closest Larger / Closest Smaller Values from a List when there is No Exact Match |
181 |
| Retrieving the First Value in a List that is Greater / Smaller than a Specified Number |
183 |
| Finding the Maximal / Minimal String, Based on Alphabetic Order |
184 |
| Retrieve Value Using Case-Sensitive Lookups |
186 |
| Retrieving the Minimal / Maximal Values from a Filtered List |
187 |
| Retrieving Smallest Nth Value Match within a List |
189 |
| Finding Every Third Number and Returning the Largest of Them |
190 |
| Retrieving the Nth Value in a Row / Column |
192 |
| Retrieving Every Nth Value in a Range |
193 |
| Retrieve the Distance between Any Two Cities from Distance Table |
194 |
| Retrieving Matching Values from Not Adjacent List |
195 |
| Retrieving Data Using Two Criteria |
196 |
| Retrieving an Item from a List that Meets Multiple Criteria (1) |
196 |
| Retrieving an Item from a List that Meets Multiple Criteria (2) |
198 |
| Retrieving Sale Price Based on Item and Color Criteria |
199 |
| Retrieving a Price from a List that Matches both Category and Item Criteria |
200 |
| Retrieving an Entire Row of Data |
201 |
| Retrieving the Entire Row of a Matched Value |
201 |
| Returning the Entire Contents of the Row Containing the Highest Math Grade |
203 |
| Retrieving Data from Two Different Lists |
204 |
| Retrieving a Price Value by Looking at Two Difference Data Tables |
204 |
| Complex Retrieval Problems |
206 |
| Retrieving the Column Header of the Next Non-Blank Cell in a Row |
206 |
| Finding the First Value in a Row and Retrieving the Header for That Column |
207 |
| Retrieving the Column Header that Corresponds with a Matched Value |
208 |
| Retrieving the Column Header of the Largest Value in a Row |
210 |
| Retrieving a Value from a Reference Grid, Using Indexes Listed in another Range |
211 |
| |
| Chapter 5 Logical & Errors |
215 |
| Reducing Complexity of IF Functions |
216 |
| Simplifying Formulas by Reducing the Complexity of IF Functions (1) |
216 |
| Simplifying Formulas by Reducing the Complexity of IF Functions (2) |
218 |
| Create Conditional Formula by Reducing the Complexity of IF Functions (3) |
219 |
| Using OR, AND Functions |
221 |
| Nesting OR & AND Functions to Meet Multiple Criteria (1) |
221 |
| Nesting OR & AND Functions to Meet Multiple Criteria (2) |
222 |
| Checking for the Presence of Specified Values within a Range. |
223 |
| Handling Errors |
224 |
| Ignoring Blank Cells when Performing Calculations |
224 |
| Avoiding the #VALUE! Error when Adding Cells that May Contain Blanks |
225 |
| Avoiding #DIV/0! Errors when Dividing by Zero |
226 |
| Ignoring #DIV/0! Errors when Performing Calculations |
227 |
| Eliminating Errors Resulting from If Functions with Multiple Conditions |
229 |
| Avoiding Errors when Using the And Function to Combine Multiple Conditions in Array Formulas |
230 |
| Avoiding Errors when Counting Date Values that Meet Specified Criteria |
231 |
| Avoiding Errors when Subtracting Times |
233 |
| Handling Errors with the VLOOKUP Function |
234 |
| Preventing a VLOOKUP Function from Returning an Error when an Exact Match is Not Found |
234 |
| Avoiding the Occurrence of Unexpected VLOOKUP #N/A Errors |
235 |
| Using a VLOOKUP Formula to Check If a Value Exists |
237 |
| Checking If Matching Numbers Exist in Different Ranges |
238 |
| |
| Chapter 6 Counting |
241 |
| The COUNT Functions |
242 |
| Using the COUNT Functions |
242 |
| Counting Using One Criterion |
244 |
| Counting Unique Numeric Values or Unique Data in a List |
244 |
| Counting the Number of Values Below or Above Average |
246 |
| Counting Values that Appear Only Once in a List |
247 |
| Counting the Number of Times a Specified Substring Appears within a Range of Strings |
248 |
| Counting the Number of Cells Containing(1) |
250 |
| Counting the Number of Cells Containing(2) |
251 |
| Counting the Number of Combined First and Last Names Matching Criteria in a Dynamic Range |
252 |
| Counting the Number of Times a Substring Appears Within a String |
255 |
| Counting the Number of Strings which Contain Numbers Meeting Criteria |
256 |
| Counting Date Values by Specified Date Criteria |
257 |
| Counting the Number of Dates with Matching Month Criteria |
258 |
| Counting Time Values In Terms of Hourly Increments |
260 |
| Counting the Number of Values Meeting Criteria, but Only for Every 3rd Cell in a Range |
261 |
| Counting Rows of Even Numbers in Two Parallel Ranges |
263 |
| Counting the Number of Values from Multiple Worksheets that Match Criteria |
264 |
| Counting Using Two Criteria |
265 |
| Counting Rows that Match Specific Criteria for Each Column |
265 |
| Counting the Number of Values between Two Specified Values in a List |
266 |
| Counting the Number of Values between Upper and Lower Limits |
268 |
| Counting the Number of Unique Items Sold by Each Salesperson |
269 |
| Counting the Number of Players According to Their Score in a Particular Quarter |
271 |
| Counting the Number of Students who Answered "A" to the Second Question |
272 |
| Counting the Number of Excel Files in a List According to File Type and Date Criteria |
273 |
| Using Date & Time Criteria to Count Entries in a Combined Date & Time List |
275 |
| Counting the Number of Items Sold Every Half Hour |
276 |
| Complex Counting Problems |
277 |
| Counting the Number of Times Any of the Numbers in First List Appears within Second List |
277 |
| Creating Progressively Incremented Counting Criteria |
278 |
| Counting the Values that Match Criteria in a Filtered List |
280 |
| Creating a Summary Table of a Student Questionnaire |
281 |
| Counting the Number of YES Responses in a Student Questionnaire |
283 |
| Counting Unique "X" and "O" Occurrences in a Matrix |
285 |
| Creating a Counting Grid |
286 |
| |
| Chapter 7 Summing |
289 |
| Summing Techniques & Tips |
290 |
| Summing the Values of Cells Occurring at the Intersection of Ranges |
290 |
| Summing Values from Different Sheets for the Same Cell Address |
291 |
| Creating a Summing Formula that Automatically Adjusts to a Dynamic Range |
292 |
| Summing the Absolute Values of All Numbers in a Range |
293 |
| Summing Subtotals Only from Range |
294 |
| Modifying a SUMPRODUCT function to Return a Sum Rather than a Count |
295 |
| Summing Rounded Numbers |
296 |
| Summing Values Based on Text, Text & Numbers, & Blank Cell Criteria |
298 |
| Summing Values Based on Text Criteria |
298 |
| Summing Values from a List Containing Numerical and Textual Substrings |
299 |
| Summing Numerical Substrings |
300 |
| Summing Values in a Range Containing Redundant Spaces |
301 |
| Ignoring Blank Cells Containing Invisible Spaces when Using Array Formulas |
302 |
| Summing Values Based on the Criteria of a Non-Empty Adjacent Cell |
304 |
| Summing Using Indirect Cell References |
305 |
| Summing Values Using Indirect Cell References |
305 |
| Summing Across Multiple Sheets |
306 |
| Summing from Closed Workbooks |
308 |
| Summing Using One or More Criteria |
310 |
| Summing Positive and Negative Numbers Separately |
310 |
| Sum Total Sales Except for One Specified Item |
311 |
| Summing Corresponding Values in Multiple Columns |
312 |
| Summing Sales Amounts According to Product Category and Customer Name |
313 |
| Summing Total Sales, Based on Quantity & Price |
314 |
| Summing Values from Two Ranges, Based on Two Criteria |
315 |
| Summing How Many Points Scored by a Specified Team in Specified Games |
317 |
| Summing Expenses Accumulated to a Specified Month |
319 |
| Calculating Monthly and Accumulated Income, Based on Specified Month |
320 |
| Summing Annual Expenses by Respective Quarters |
322 |
| Summing the Number of Hours an Employee Worked During a Two Week Period |
323 |
| Summing Last N Values |
324 |
| Summing Groups of Every N Values in a Row |
324 |
| Summing the Last N Values in a Column |
326 |
| Summing the N Largest Values in a Range |
327 |
| Creating a List in which Each Number is the Sum of the Previous N Numbers |
328 |
| |
| Chapter 8 List |
331 |
| Sorting, Reversing, & Retrieving Values |
332 |
| Sorting Numbers in Ascending or Descending Order |
332 |
| Sorting Numbers Based on Their Frequency within a List |
333 |
| Reversing the Order of Values in a List |
335 |
| Shuffling a List of Values |
336 |
| Creating a New List Using Criteria |
338 |
| Retrieving Unique Values from a List |
338 |
| Retrieving List Values that Do Not Appear in a Second List |
340 |
| Retrieving Values that are Common to Two Lists |
342 |
| Creating a List of All Non-Blank Cells in a Column |
344 |
| Retrieving Values from a List that are Greater than a Specified Number |
345 |
| Checking for Sequences within a Sorted List |
347 |
| Identifying Duplicate Values |
348 |
| Checking for Duplicate Values within a Range |
348 |
| Identifying Duplicate Rows within a Range |
350 |
| Determining and Indicating the Number of Times Each Value Has Been Entered in a List |
351 |
| Transposing a List |
352 |
| Transposing an Entire Range of Data into a Single Column |
352 |
| Transposing a Column into Successive Rows of a Specified Length |
355 |
| Transposing a Range in Ascending Order |
356 |
| Transposing Values from Columns into Rows, and Vice Versa |
357 |
| |
| Chapter 9 Miscellaneous Calculation & Math |
361 |
| Averaging Values |
363 |
| Calculating Average Annual Growth |
363 |
| Calculating the Average Growth of a Child |
364 |
| Averaging Using Criteria |
365 |
| Calculating the Average for Numbers Meeting Specified Criteria |
365 |
| Calculating the Average Difference between List Price and Sale Price |
366 |
| Finding Specified Items in a List and Averaging their Associated Values |
368 |
| Excluding Exceptional Values when Calculating an Average |
369 |
| Calculating an Average, Only Including Prices on Which There Was no Discount |
371 |
| Averaging Sales Totals by Day of the Week |
372 |
| Averaging Values that Correspond with the X Largest Values in a Dynamic Range |
374 |
| Calculating the Average of a Range of Numbers, Excluding the Minimal Value |
376 |
| Calculating the Average of Every Nth Value |
377 |
| Calculating the Average of Every Nth Value, Excluding Zeros |
378 |
| Calculating the Average Value in Every Nth Column, Excluding Zeros |
380 |
| Finding Minimum / Maximum Values |
382 |
| Finding the Minimum Value in a Referenced Range |
382 |
| Calculating the Lowest Common Multiple (LCM) |
383 |
| Returning the Nth Largest / Smallest Values in a Range |
385 |
| Finding the Largest Number among Those Meeting Specified Criteria |
386 |
| Finding the Score that Was the Nth Highest for a Specified Team |
387 |
| Finding the Largest Value from Two Different Lists, Subject to Specified Criteria |
388 |
| Rounding Values |
390 |
| Rounding Prices to the Nearest Nickel, Dime, Quarter and Dollar |
390 |
| Dividing an Amount into Equal Payments While Avoiding Division / Rounding Errors |
391 |
| Rounding the Calculation of Retail Price and Sales Tax |
393 |
| Rounding Up / Down to the Nearest Multiple of a Specific Number |
394 |
| Rounding Numbers that Meet Specified Criteria |
395 |
| Rounding Numerical Substrings |
396 |
| Rounding a Value to Make It Divisible by a Specified Number |
397 |
| Ranking Values |
398 |
| Ranking a List of Numbers |
398 |
| Ranking Numbers in a Dynamic List |
399 |
| Calculating Commissions Based on Sales Rank |
401 |
| Random Calculation |
402 |
| Selecting a Set of Random Numbers, Avoiding Duplicates |
402 |
| Adjusting Values Returned by the RAND Function |
404 |
| Randomly Selecting a Value from a Range |
405 |
| Random Selection from a List |
406 |
| Miscellaneous Calculation |
407 |
| Creating a Dynamic Inventory List Based on Daily Sales |
407 |
| Calculating Net Sale Price According to Text Criteria |
409 |
| Calculating the Proportion of Characters from One String that Appears in another String |
410 |
| Calculating the Remaining Credit after Each Purchase |
411 |
| Calculating Total Annual Payment Based on Cumulative Monthly Payments |
413 |
| Placing the Previous Payment Date beside Each Pay Date in a List |
414 |
| Restricting the Automatic Recalculation of Volatile Functions |
416 |
| Calculating the Percentage of Cells in a Range that Contain a Specified String |
418 |
| Calculating the Absolute Difference between Each List Value and the Minimum Value in the List |
419 |
| Determining Divisibility |
420 |
| Multiplying Values from Two Matrixes that Occupy Corresponding Addresses |
421 |
| Converting Decimal Fractions to Fractions of a Specified Number |
422 |
| Simplifying Formulas by Defining Names for Often-Repeated Parts |
424 |
| Converting Units |
426 |
| Converting Units |
426 |
| |
| Chapter 10 Income Tax & Financial |
429 |
| Calculating Income Tax |
430 |
| Calculating Total Income Tax |
430 |
| Financial Formulas |
432 |
| Using Financial Functions |
432 |
| Calculating Canadian Mortgage Payments |
433 |
| Amortization Tables |
434 |
| Creating an Amortization Schedule |
434 |
| Creating an Amortization Schedule with a Grace Period |
435 |
| Creating an Amortization Schedule for Random Loan Payments |
436 |
|
|
| Appendix, List of Functions |
437 |
| Index |
455 |
460 pages, 323 Screenshots
