| Introduction |
|
1 | (10) |
|
|
|
1 | (1) |
|
|
|
2 | (1) |
|
|
|
2 | (2) |
|
|
|
2 | (2) |
|
|
|
4 | (1) |
|
|
|
4 | (1) |
|
How This Book is Organized |
|
|
4 | (2) |
|
|
|
4 | (1) |
|
Part II Using Functions in Your Formulas |
|
|
5 | (1) |
|
Part III Financial Formulas |
|
|
5 | (1) |
|
|
|
5 | (1) |
|
Part V Miscellaneous Formula Techniques |
|
|
5 | (1) |
|
Part VI Developing Custom Worksheet Functions |
|
|
5 | (1) |
|
|
|
6 | (1) |
|
|
|
6 | (1) |
|
About the Companion CD-ROM |
|
|
6 | (1) |
|
About the Power Utility Pak Offer |
|
|
6 | (1) |
|
|
|
7 | (4) |
|
|
|
|
Chapter 1 Excel in a Nutshell |
|
|
11 | (28) |
|
|
|
12 | (3) |
|
|
|
12 | (1) |
|
|
|
12 | (1) |
|
Microsoft enters the picture |
|
|
12 | (1) |
|
|
|
13 | (2) |
|
|
|
15 | (2) |
|
|
|
17 | (2) |
|
|
|
17 | (1) |
|
|
|
18 | (1) |
|
Macro sheets and dialog sheets |
|
|
18 | (1) |
|
|
|
19 | (9) |
|
|
|
19 | (1) |
|
|
|
19 | (2) |
|
|
|
21 | (1) |
|
Shortcut menus and the Mini Toolbar |
|
|
22 | (1) |
|
|
|
23 | (1) |
|
|
|
24 | (1) |
|
|
|
25 | (1) |
|
|
|
25 | (1) |
|
|
|
25 | (1) |
|
Customized on-screen display |
|
|
26 | (1) |
|
|
|
26 | (1) |
|
Object and cell selecting |
|
|
27 | (1) |
|
|
|
28 | (1) |
|
|
|
29 | (1) |
|
|
|
29 | (1) |
|
|
|
29 | (1) |
|
|
|
30 | (1) |
|
Worksheet Formulas and Functions |
|
|
30 | (1) |
|
Objects on the Drawing Layer |
|
|
31 | (2) |
|
|
|
31 | (1) |
|
|
|
31 | (1) |
|
|
|
31 | (1) |
|
|
|
32 | (1) |
|
|
|
32 | (1) |
|
|
|
33 | (1) |
|
|
|
33 | (1) |
|
|
|
33 | (1) |
|
|
|
34 | (1) |
|
|
|
34 | (1) |
|
|
|
34 | (3) |
|
|
|
34 | (1) |
|
|
|
35 | (1) |
|
|
|
36 | (1) |
|
|
|
36 | (1) |
|
|
|
36 | (1) |
|
|
|
37 | (1) |
|
|
|
37 | (2) |
|
Protecting formulas from being overwritten |
|
|
37 | (1) |
|
Protecting a workbook's structure |
|
|
38 | (1) |
|
Password-protecting a workbook |
|
|
38 | (1) |
|
Chapter 2 Basic Facts about Formulas |
|
|
39 | (26) |
|
Entering and Editing Formulas |
|
|
39 | (6) |
|
|
|
39 | (1) |
|
|
|
40 | (1) |
|
|
|
41 | (1) |
|
|
|
42 | (1) |
|
|
|
42 | (1) |
|
|
|
43 | (1) |
|
|
|
43 | (2) |
|
Using Operators in Formulas |
|
|
45 | (5) |
|
|
|
45 | (1) |
|
Sample formulas that use operators |
|
|
46 | (1) |
|
|
|
47 | (2) |
|
|
|
49 | (1) |
|
|
|
50 | (1) |
|
Cell and Range References |
|
|
51 | (4) |
|
Creating an absolute or a mixed reference |
|
|
52 | (2) |
|
Referencing other sheets or workbooks |
|
|
54 | (1) |
|
Making an Exact Copy of a Formula |
|
|
55 | (1) |
|
Converting Formulas to Values |
|
|
56 | (2) |
|
|
|
58 | (1) |
|
|
|
59 | (1) |
|
Dealing with Circular References |
|
|
60 | (1) |
|
|
|
61 | (4) |
|
|
|
62 | (1) |
|
|
|
63 | (2) |
|
Chapter 3 Working with Names |
|
|
65 | (38) |
|
|
|
65 | (1) |
|
|
|
66 | (2) |
|
|
|
67 | (1) |
|
Referencing names from another workbook |
|
|
67 | (1) |
|
|
|
68 | (1) |
|
|
|
68 | (2) |
|
|
|
69 | (1) |
|
|
|
69 | (1) |
|
|
|
70 | (1) |
|
Shortcuts for Creating Cell and Range Names |
|
|
70 | (6) |
|
|
|
70 | (1) |
|
Creating names using the Name box |
|
|
71 | (1) |
|
Creating names automatically |
|
|
72 | (2) |
|
Naming entire rows and columns |
|
|
74 | (1) |
|
|
|
75 | (1) |
|
Creating Multisheet Names |
|
|
76 | (2) |
|
Working with Range and Cell Names |
|
|
78 | (6) |
|
|
|
78 | (1) |
|
|
|
78 | (1) |
|
Using the intersection operators with names |
|
|
79 | (2) |
|
Using the range operator with names |
|
|
81 | (1) |
|
Referencing a single cell in a multicell named range |
|
|
81 | (1) |
|
Applying names to existing formulas |
|
|
82 | (1) |
|
Applying names automatically when creating a formula |
|
|
83 | (1) |
|
|
|
83 | (1) |
|
|
|
83 | (1) |
|
|
|
84 | (1) |
|
|
|
84 | (1) |
|
How Excel Maintains Cell and Range Names |
|
|
84 | (1) |
|
Inserting a row or column |
|
|
85 | (1) |
|
|
|
85 | (1) |
|
|
|
85 | (1) |
|
Potential Problems with Names |
|
|
85 | (3) |
|
Name problems when copying sheets |
|
|
85 | (2) |
|
Name problems when deleting sheets |
|
|
87 | (1) |
|
The Secret to Understanding Names |
|
|
88 | (8) |
|
|
|
89 | (1) |
|
|
|
90 | (1) |
|
Using worksheet functions in named formulas |
|
|
90 | (1) |
|
Using cell and range references in named formulas |
|
|
91 | (1) |
|
Using named formulas with relative references |
|
|
92 | (4) |
|
Advanced Techniques That Use Names |
|
|
96 | (7) |
|
Using the INDIRECT function with a named range |
|
|
96 | (1) |
|
Using the INDIRECT function to create a named range with a fixed address |
|
|
97 | (1) |
|
Using arrays in named formulas |
|
|
98 | (1) |
|
Creating a dynamic named formula |
|
|
99 | (4) |
|
Part II Using Functions in Your Formulas |
|
|
|
Chapter 4 Introducting Worksheet Functions |
|
|
103 | (16) |
|
|
|
103 | (3) |
|
|
|
104 | (1) |
|
Perform otherwise impossible calculations |
|
|
104 | (1) |
|
|
|
104 | (1) |
|
Provide decision-making capability |
|
|
105 | (1) |
|
|
|
105 | (1) |
|
|
|
106 | (3) |
|
|
|
106 | (1) |
|
Full-column or full-row as arguments |
|
|
107 | (1) |
|
Literal values as arguments |
|
|
108 | (1) |
|
|
|
108 | (1) |
|
Other functions as arguments |
|
|
108 | (1) |
|
|
|
109 | (1) |
|
Ways to Enter a Function into a Formula |
|
|
109 | (5) |
|
Entering a function manually |
|
|
109 | (2) |
|
Using the Function Library commands |
|
|
111 | (1) |
|
Using the Insert Function dialog box |
|
|
111 | (2) |
|
More tips for entering functions |
|
|
113 | (1) |
|
|
|
114 | (5) |
|
|
|
115 | (1) |
|
|
|
115 | (1) |
|
|
|
115 | (1) |
|
|
|
115 | (1) |
|
Lookup and reference functions |
|
|
115 | (1) |
|
|
|
115 | (1) |
|
|
|
116 | (1) |
|
|
|
116 | (1) |
|
|
|
116 | (1) |
|
|
|
116 | (1) |
|
|
|
116 | (1) |
|
|
|
116 | (1) |
|
|
|
116 | (1) |
|
Other function categories |
|
|
117 | (2) |
|
Chapter 5 Manipulating Text |
|
|
119 | (24) |
|
|
|
119 | (2) |
|
How many characters in a cell? |
|
|
119 | (1) |
|
|
|
120 | (1) |
|
|
|
121 | (14) |
|
Determining whether a cell contains text |
|
|
121 | (1) |
|
Working with character codes |
|
|
122 | (2) |
|
Determining whether two strings are identical |
|
|
124 | (1) |
|
Joining two or more cells |
|
|
125 | (1) |
|
Displaying formatted values as text |
|
|
126 | (1) |
|
Displaying formatted currency values as text |
|
|
127 | (1) |
|
Removing excess spaces and nonprinting characters |
|
|
128 | (1) |
|
Counting characters in a string |
|
|
128 | (1) |
|
Repeating a characters or string |
|
|
129 | (1) |
|
Creating a text histogram |
|
|
130 | (1) |
|
|
|
130 | (1) |
|
Changing the case of text |
|
|
131 | (1) |
|
Extracting characters from a string |
|
|
132 | (1) |
|
Replacing text with other text |
|
|
133 | (1) |
|
Finding and searching within a string |
|
|
134 | (1) |
|
Searching and replacing within a string |
|
|
134 | (1) |
|
|
|
135 | (8) |
|
Counting specific characters in a cell |
|
|
135 | (1) |
|
Counting the occurrences of a substring in a cell |
|
|
135 | (1) |
|
Removing trailing minus signs |
|
|
136 | (1) |
|
Expressing a number as an ordinal |
|
|
136 | (1) |
|
Determining a column letter for a column number |
|
|
137 | (1) |
|
Extracting a filename from a path specification |
|
|
138 | (1) |
|
Extracting the first word of a string |
|
|
138 | (1) |
|
Extracting the last word of a string |
|
|
138 | (1) |
|
Extracting all but the first word of a string |
|
|
139 | (1) |
|
Extracting first names, middle names, and last names |
|
|
139 | (2) |
|
Removing titles from names |
|
|
141 | (1) |
|
Counting the number of words in a cell |
|
|
142 | (1) |
|
Chapter 6 Working with Dates and Times |
|
|
143 | (38) |
|
How Excel Handles Dates and Times |
|
|
143 | (9) |
|
Understanding date serial numbers |
|
|
144 | (1) |
|
|
|
145 | (1) |
|
Understanding time serial numbers |
|
|
146 | (1) |
|
|
|
147 | (2) |
|
Formatting dates and times |
|
|
149 | (1) |
|
|
|
150 | (2) |
|
|
|
152 | (17) |
|
Displaying the current date |
|
|
152 | (1) |
|
|
|
153 | (1) |
|
Generating a series of dates |
|
|
154 | (1) |
|
Converting a non-date string to a date |
|
|
155 | (1) |
|
Calculating the number of days between two dates |
|
|
156 | (1) |
|
Calculating the number of work days between two dates |
|
|
157 | (1) |
|
Offsetting a date using only work days |
|
|
158 | (1) |
|
Calculating the number of years between two dates |
|
|
158 | (1) |
|
Calculating a person's age |
|
|
159 | (1) |
|
Determining the day of the year |
|
|
159 | (2) |
|
Determining the day of the week |
|
|
161 | (1) |
|
Determining the date of the most recent Sunday |
|
|
161 | (1) |
|
Determining the first day of the week after a date |
|
|
162 | (1) |
|
Determining the nth occurrence of a day of the week in a month |
|
|
162 | (1) |
|
Counting the occurrences of a day of the week |
|
|
163 | (1) |
|
Expressing a date as an ordinal number |
|
|
164 | (1) |
|
Calculating dates of holidays |
|
|
164 | (4) |
|
Determining the last day of a month |
|
|
168 | (1) |
|
Determining whether a year is a leap year |
|
|
168 | (1) |
|
Determining a date's quarter |
|
|
168 | (1) |
|
Converting a year to roman numerals |
|
|
169 | (1) |
|
|
|
169 | (12) |
|
Displaying the current time |
|
|
169 | (1) |
|
|
|
170 | (1) |
|
Summing times that exceed 24 hours |
|
|
171 | (2) |
|
Calculating the difference between two times |
|
|
173 | (1) |
|
Converting from military time |
|
|
174 | (1) |
|
Converting decimal hours, minutes, or seconds to a time |
|
|
175 | (1) |
|
Adding hours, minutes, or seconds to a time |
|
|
175 | (1) |
|
Converting between time zones |
|
|
176 | (1) |
|
|
|
177 | (1) |
|
Working with non-time-of-day values |
|
|
178 | (3) |
|
Chapter 7 Counting and Summing Techniques |
|
|
181 | (32) |
|
Counting and Summing Worksheet Cells |
|
|
181 | (2) |
|
Counting or Summing Records in Databases and Pivot Tables |
|
|
183 | (1) |
|
|
|
184 | (3) |
|
Counting the total number of cells |
|
|
185 | (1) |
|
|
|
185 | (1) |
|
|
|
186 | (1) |
|
|
|
186 | (1) |
|
|
|
186 | (1) |
|
|
|
186 | (1) |
|
|
|
187 | (1) |
|
Counting error values in a range |
|
|
187 | (1) |
|
Advanced Counting Formulas |
|
|
187 | (15) |
|
Counting cells with the COUNTIF function |
|
|
188 | (1) |
|
Counting cells that meet multiple criteria |
|
|
189 | (3) |
|
Counting the most frequently occurring entry |
|
|
192 | (1) |
|
Counting the occurrences of specific text |
|
|
193 | (2) |
|
Counting the number of unique values |
|
|
195 | (1) |
|
Creating a frequency distribution |
|
|
196 | (6) |
|
|
|
202 | (4) |
|
Summing all cells in a range |
|
|
203 | (1) |
|
Computing a cumulative sum |
|
|
204 | (1) |
|
Summing the "top n" values |
|
|
205 | (1) |
|
Conditional Sums Using a Single Criterion |
|
|
206 | (2) |
|
Summing only negative values |
|
|
207 | (1) |
|
Summing values based on a different range |
|
|
207 | (1) |
|
Summing values based on a text comparison |
|
|
208 | (1) |
|
Summing values based on a date comparison |
|
|
208 | (1) |
|
Conditional Sums Using Multiple Criteria |
|
|
208 | (5) |
|
|
|
209 | (1) |
|
|
|
210 | (1) |
|
Using And and Or criteria |
|
|
211 | (2) |
|
Chapter 8 Using Lookup Functions |
|
|
213 | (22) |
|
What is a Lookup Formula? |
|
|
213 | (1) |
|
Functions Relevant to Lookups |
|
|
214 | (1) |
|
|
|
214 | (6) |
|
|
|
215 | (1) |
|
|
|
216 | (1) |
|
|
|
217 | (1) |
|
Combining the MATCH and INDEX functions |
|
|
218 | (2) |
|
Specialized Lookup Formulas |
|
|
220 | (15) |
|
Looking up an exact value |
|
|
220 | (2) |
|
Looking up a value to the left |
|
|
222 | (1) |
|
Performing a case-sensitive lookup |
|
|
223 | (1) |
|
Choosing among multiple lookup tables |
|
|
223 | (1) |
|
Determining letter grades for test scores |
|
|
224 | (1) |
|
Calculating a grade point average |
|
|
225 | (1) |
|
Performing a two-way lookup |
|
|
226 | (1) |
|
Performing a two-column lookup |
|
|
227 | (1) |
|
Determining the address of a value within a range |
|
|
228 | (1) |
|
Looking up a value by using the closest match |
|
|
229 | (1) |
|
Looking up a value using linear interpolation |
|
|
230 | (5) |
|
Chapter 9 Tables and Worksheet Databases |
|
|
235 | (40) |
|
|
|
235 | (4) |
|
A worksheet database example |
|
|
236 | (1) |
|
|
|
237 | (1) |
|
Uses for worksheet databases and tables |
|
|
238 | (1) |
|
|
|
239 | (19) |
|
|
|
240 | (1) |
|
Changing the look of a table |
|
|
240 | (1) |
|
Navigating and selecting in a table |
|
|
241 | (1) |
|
Adding new rows or columns |
|
|
242 | (1) |
|
|
|
242 | (1) |
|
|
|
243 | (1) |
|
Setting table style options |
|
|
243 | (1) |
|
Removing duplicate rows from a table |
|
|
244 | (1) |
|
Sorting and filtering a table |
|
|
245 | (4) |
|
Working with the Total row |
|
|
249 | (3) |
|
Using formulas within a table |
|
|
252 | (2) |
|
Referencing data in a table |
|
|
254 | (3) |
|
Converting a table to a worksheet database |
|
|
257 | (1) |
|
|
|
258 | (4) |
|
Setting up a criteria range |
|
|
259 | (1) |
|
Applying an advanced filter |
|
|
260 | (2) |
|
Clearing an advanced filter |
|
|
262 | (1) |
|
Specifying Advanced Filter Criteria |
|
|
262 | (6) |
|
Specifying a single criterion |
|
|
262 | (2) |
|
Specifying multiple criteria |
|
|
264 | (3) |
|
Specifying computed criteria |
|
|
267 | (1) |
|
|
|
268 | (2) |
|
|
|
270 | (5) |
|
Chapter 10 Miscellaneous Calculations |
|
|
275 | (18) |
|
|
|
275 | (2) |
|
|
|
277 | (3) |
|
Area, Surface, Circumference, and Volume Calculations |
|
|
280 | (3) |
|
Calculating the area and perimeter of a square |
|
|
280 | (1) |
|
Calculating the area and perimeter of a rectangle |
|
|
280 | (1) |
|
Calculating the area and perimeter of a circle |
|
|
280 | (1) |
|
Calculating the area of a trapezoid |
|
|
281 | (1) |
|
Calculating the area of a triangle |
|
|
281 | (1) |
|
Calculating the surface and volume of a sphere |
|
|
281 | (1) |
|
Calculating the surface and volume of a cube |
|
|
282 | (1) |
|
Calculating the surface and volume of a cone |
|
|
282 | (1) |
|
Calculating the volume of a cylinder |
|
|
282 | (1) |
|
Calculating the volume of a pyramid |
|
|
283 | (1) |
|
Solving Simultaneous Equations |
|
|
283 | (1) |
|
|
|
284 | (9) |
|
|
|
285 | (1) |
|
Rounding to the nearest multiple |
|
|
286 | (1) |
|
|
|
286 | (2) |
|
Working with fractional dollars |
|
|
288 | (1) |
|
Using the INT and TRUNC functions |
|
|
288 | (1) |
|
Rounding to an even or odd integer |
|
|
289 | (1) |
|
Rounding to n significant digits |
|
|
290 | (3) |
|
Part III Financial Formulas |
|
|
|
Chapter 11 Borrowing and Investing Formulas |
|
|
293 | (24) |
|
|
|
293 | (2) |
|
|
|
294 | (1) |
|
|
|
294 | (1) |
|
|
|
295 | (1) |
|
Timing of the first payment |
|
|
295 | (1) |
|
The Basic Excel Financial Functions |
|
|
295 | (12) |
|
Calculating present value |
|
|
295 | (4) |
|
|
|
299 | (3) |
|
|
|
302 | (1) |
|
|
|
303 | (3) |
|
|
|
306 | (1) |
|
Calculating the Interest and Principal Components |
|
|
307 | (3) |
|
Using the IPMT and PPMT functions |
|
|
308 | (1) |
|
Using the CUMIPMT and CUMPRINC functions |
|
|
309 | (1) |
|
Converting Interest Rates |
|
|
310 | (2) |
|
Methods of quoting interest rates |
|
|
310 | (1) |
|
|
|
311 | (1) |
|
Limitations of Excel's Financial Functions |
|
|
312 | (2) |
|
Deferred start to a series of regular payments |
|
|
312 | (1) |
|
Valuing a series of variable payments |
|
|
313 | (1) |
|
|
|
314 | (3) |
|
|
|
314 | (2) |
|
|
|
316 | (1) |
|
Chapter 12 Discounting and Depreciation Formulas |
|
|
317 | (24) |
|
|
|
317 | (10) |
|
|
|
318 | (1) |
|
|
|
319 | (6) |
|
Using the NPV function to calculate accumulated amounts |
|
|
325 | (2) |
|
|
|
327 | (4) |
|
|
|
328 | (1) |
|
|
|
329 | (1) |
|
|
|
330 | (1) |
|
Multiple Rates of IRR and the MIRR Function |
|
|
331 | (3) |
|
|
|
331 | (1) |
|
|
|
332 | (1) |
|
Using balances instead of flows |
|
|
333 | (1) |
|
|
|
334 | (2) |
|
|
|
334 | (1) |
|
|
|
335 | (1) |
|
Using the FVSCHEDULE Function |
|
|
336 | (1) |
|
Calculating an annual return |
|
|
336 | (1) |
|
Depreciation Calculations |
|
|
337 | (4) |
|
Chapter 13 Financial Schedules |
|
|
341 | (26) |
|
Creating Financial Schedules |
|
|
341 | (1) |
|
Creating Amortization Schedules |
|
|
342 | (9) |
|
A simple amortization schedule |
|
|
342 | (3) |
|
A dynamic amortization schedule |
|
|
345 | (3) |
|
Using payment and interest tables |
|
|
348 | (2) |
|
|
|
350 | (1) |
|
Summarizing Loan Options Using a Data Table |
|
|
351 | (4) |
|
Creating a one-way data table |
|
|
351 | (2) |
|
Creating a two-way data table |
|
|
353 | (2) |
|
Financial Statements and Rations |
|
|
355 | (7) |
|
Basic financial statements |
|
|
355 | (4) |
|
|
|
359 | (3) |
|
|
|
362 | (5) |
|
|
|
|
Chapter 14 Introducing Arrays |
|
|
367 | (24) |
|
Introducing Array Formulas |
|
|
367 | (5) |
|
A multicell array formula |
|
|
368 | (1) |
|
A single-cell array formula |
|
|
369 | (1) |
|
Creating an array constant |
|
|
370 | (1) |
|
|
|
371 | (1) |
|
Understanding the Dimensions of an Array |
|
|
372 | (2) |
|
One-dimensional horizontal arrays |
|
|
372 | (1) |
|
One-dimensional vertical arrays |
|
|
372 | (1) |
|
|
|
373 | (1) |
|
|
|
374 | (2) |
|
Working with Array Formulas |
|
|
376 | (2) |
|
Entering an array formula |
|
|
376 | (1) |
|
Selecting an array formula range |
|
|
376 | (1) |
|
|
|
376 | (1) |
|
Expanding or contracting a multicell array formula |
|
|
377 | (1) |
|
Using Multicell Array Formulas |
|
|
378 | (5) |
|
Creating an array from values in a range |
|
|
378 | (1) |
|
Creating an array constant from values in a range |
|
|
379 | (1) |
|
Performing operations on an array |
|
|
379 | (2) |
|
Using functions with an array |
|
|
381 | (1) |
|
|
|
381 | (1) |
|
Generating an array of consecutive integers |
|
|
382 | (1) |
|
Using Single-Cell Array Formulas |
|
|
383 | (8) |
|
Counting characters in a range |
|
|
383 | (1) |
|
Summing the three smallest values in a range |
|
|
384 | (1) |
|
Counting text cells in a range |
|
|
385 | (2) |
|
Eliminating intermediate formulas |
|
|
387 | (2) |
|
Using an array in lieu of a range reference |
|
|
389 | (2) |
|
Chapter 15 Performing Magic with Array Formulas |
|
|
391 | (24) |
|
Working with Single-Cell Array Formulas |
|
|
391 | (15) |
|
Summing a range that contains errors |
|
|
391 | (2) |
|
Counting the number of error values in a range |
|
|
393 | (1) |
|
Summing the n largest values in a range |
|
|
394 | (1) |
|
Computing an average that excludes zeros |
|
|
394 | (1) |
|
Determining whether a particular value appears in a range |
|
|
395 | (1) |
|
Counting the number of differences in two ranges |
|
|
396 | (1) |
|
Returning the location of the maximum value in a range |
|
|
397 | (1) |
|
Finding the row of a value's nth occurrence in a range |
|
|
397 | (1) |
|
Returning the longest text in a range |
|
|
398 | (1) |
|
Determining whether a range contains valid values |
|
|
398 | (1) |
|
Summing the digits of an integer |
|
|
399 | (1) |
|
|
|
400 | (1) |
|
Summing every nth value in a range |
|
|
401 | (1) |
|
Removing nonnumeric characters from a string |
|
|
402 | (1) |
|
Determining the closest value in a range |
|
|
402 | (2) |
|
Returning the last value in a column |
|
|
404 | (1) |
|
Returning the last value in a row |
|
|
404 | (1) |
|
Ranking data with an array formula |
|
|
404 | (2) |
|
Working with Multicell Array Formulas |
|
|
406 | (9) |
|
Returning only positive values from a range |
|
|
406 | (1) |
|
Returning nonblank cells from a range |
|
|
407 | (1) |
|
Reversing the order of cells in a range |
|
|
407 | (1) |
|
Sorting a range of values dynamically |
|
|
408 | (1) |
|
Returning a list of unique items in a range |
|
|
408 | (2) |
|
Displaying a calendar in a range |
|
|
410 | (5) |
|
Part V Miscellaneous Formula Techniques |
|
|
|
Chapter 16 Intentional Circular References |
|
|
415 | (14) |
|
What are Circular References? |
|
|
415 | (2) |
|
Correcting an accidental circular reference |
|
|
416 | (1) |
|
Understanding indirect circular references |
|
|
417 | (1) |
|
Intentional Circular References |
|
|
417 | (3) |
|
How Excel Determines Calculation and Iteration Settings |
|
|
420 | (1) |
|
Circular Reference Examples |
|
|
421 | (6) |
|
Generating unique random integers |
|
|
421 | (2) |
|
Solving a recursive equation |
|
|
423 | (1) |
|
Solving simultaneous equations using a circular reference |
|
|
424 | (2) |
|
Animating a chart using iteration |
|
|
426 | (1) |
|
Potential Problems with Intentional Circular References |
|
|
427 | (2) |
|
Chapter 17 Charting Techniques |
|
|
429 | (44) |
|
Understanding the SERIES Formula |
|
|
429 | (5) |
|
Using names in a SERIES formula |
|
|
431 | (1) |
|
Unlinking a chart series from its data range |
|
|
432 | (2) |
|
|
|
434 | (2) |
|
Adding a chart title link |
|
|
434 | (1) |
|
|
|
435 | (1) |
|
Adding links to data labels |
|
|
435 | (1) |
|
|
|
435 | (1) |
|
Adding a linked picture to a chart |
|
|
436 | (1) |
|
|
|
436 | (25) |
|
Charting progress toward a goal |
|
|
436 | (2) |
|
|
|
438 | (1) |
|
Displaying conditional colors in a column chart |
|
|
439 | (1) |
|
Creating a comparative histogram |
|
|
440 | (1) |
|
|
|
441 | (2) |
|
|
|
443 | (3) |
|
Plotting every nth data point |
|
|
446 | (1) |
|
Plotting the last n data points |
|
|
447 | (1) |
|
Selecting a series from a combo box |
|
|
448 | (2) |
|
Plotting mathematical functions |
|
|
450 | (5) |
|
|
|
455 | (2) |
|
|
|
457 | (3) |
|
|
|
460 | (1) |
|
|
|
461 | (12) |
|
|
|
462 | (4) |
|
Working with nonlinear trendlines |
|
|
466 | (7) |
|
|
|
473 | (46) |
|
|
|
473 | (1) |
|
|
|
474 | (2) |
|
Data Appropriate for a Pivot Table |
|
|
476 | (3) |
|
|
|
479 | (8) |
|
|
|
480 | (1) |
|
Specifying the location for the pivot table |
|
|
480 | (1) |
|
Laying out the pivot table |
|
|
481 | (3) |
|
Formatting the pivot table |
|
|
484 | (1) |
|
Modifying the pivot table |
|
|
485 | (2) |
|
More Pivot Table Examples |
|
|
487 | (6) |
|
|
|
487 | (1) |
|
|
|
488 | (1) |
|
|
|
489 | (1) |
|
|
|
489 | (1) |
|
|
|
490 | (1) |
|
|
|
491 | (1) |
|
|
|
492 | (1) |
|
Grouping Pivot Table Items |
|
|
493 | (7) |
|
A manual grouping example |
|
|
493 | (2) |
|
|
|
495 | (1) |
|
Automatic grouping examples |
|
|
496 | (4) |
|
Creating a Frequency Distribution |
|
|
500 | (2) |
|
Creating a Calculated Field or Calculated Item |
|
|
502 | (7) |
|
Creating a calculated field |
|
|
504 | (2) |
|
Inserting a calculated item |
|
|
506 | (3) |
|
Filtering Pivot Tables with Slicers |
|
|
509 | (1) |
|
Referencing Cells within a Pivot Table |
|
|
510 | (2) |
|
Another Pivot Table Example |
|
|
512 | (3) |
|
Producing a Report with a Pivot Table |
|
|
515 | (4) |
|
Chapter 19 Conditional Formatting and Data Validation |
|
|
519 | (36) |
|
|
|
519 | (26) |
|
Specifying conditional formatting |
|
|
520 | (4) |
|
Conditional formats that use graphics |
|
|
524 | (8) |
|
Working with conditional formats |
|
|
532 | (2) |
|
Creating formula-based rules |
|
|
534 | (11) |
|
|
|
545 | (10) |
|
Specifying validation criteria |
|
|
545 | (1) |
|
Types of validation criteria you can apply |
|
|
546 | (2) |
|
Creating a drop-down list |
|
|
548 | (1) |
|
Using formulas for data validation rules |
|
|
549 | (4) |
|
Creating a dependent list |
|
|
553 | (2) |
|
Chapter 20 Creating Megaformulas |
|
|
555 | (18) |
|
|
|
555 | (1) |
|
Creating a Megaformula: A Simple Example |
|
|
556 | (2) |
|
|
|
558 | (14) |
|
Using a megaformula to remove middle names |
|
|
558 | (4) |
|
Using a megaformula to return a string's last space character position |
|
|
562 | (4) |
|
Using a megaformula to determine the validity of a credit card number |
|
|
566 | (4) |
|
|
|
570 | (2) |
|
The Pros and Cons of Megaformulas |
|
|
572 | (1) |
|
Chapter 21 Tools and Methods for Debugging Formulas |
|
|
573 | (26) |
|
|
|
573 | (1) |
|
Formula Problems and Solutions |
|
|
574 | (14) |
|
|
|
575 | (1) |
|
Cells are filled with hash marks |
|
|
575 | (1) |
|
Blank cells are not blank |
|
|
576 | (1) |
|
|
|
577 | (1) |
|
Formulas returning an error |
|
|
578 | (4) |
|
Absolute/relative reference problems |
|
|
582 | (1) |
|
Operator precedence problems |
|
|
582 | (2) |
|
Formulas are not calculated |
|
|
584 | (1) |
|
Actual versus displayed values |
|
|
584 | (1) |
|
Floating-point number errors |
|
|
585 | (1) |
|
|
|
586 | (1) |
|
|
|
587 | (1) |
|
Circular reference errors |
|
|
588 | (1) |
|
|
|
588 | (11) |
|
Identifying cells of a particular type |
|
|
588 | (2) |
|
|
|
590 | (1) |
|
Tracing cell relationships |
|
|
591 | (1) |
|
|
|
592 | (1) |
|
Fixing circular reference errors |
|
|
592 | (1) |
|
Using background error checking |
|
|
593 | (2) |
|
Using Excel's Formula Evaluator |
|
|
595 | (4) |
|
Part VI Developing Custom Worksheet Functions |
|
|
|
Chapter 22 Introducing VBA |
|
|
599 | (14) |
|
|
|
599 | (1) |
|
Displaying the Developer Tab |
|
|
600 | (1) |
|
|
|
600 | (2) |
|
Saving Workbooks That Contain Macros |
|
|
602 | (1) |
|
Introducing the Visual Basic Editor |
|
|
603 | (10) |
|
|
|
603 | (1) |
|
|
|
604 | (1) |
|
|
|
605 | (2) |
|
|
|
607 | (2) |
|
|
|
609 | (3) |
|
|
|
612 | (1) |
|
Chapter 23 Function Procedure Basics |
|
|
613 | (22) |
|
Why Create Custom Functions? |
|
|
613 | (1) |
|
An Introductory VBA Function Example |
|
|
614 | (2) |
|
About Function Procedures |
|
|
616 | (4) |
|
|
|
616 | (1) |
|
Choosing a name for your function |
|
|
617 | (1) |
|
Using functions in formulas |
|
|
618 | (1) |
|
Using functions arguments |
|
|
619 | (1) |
|
Using the Insert Function Dialog Box |
|
|
620 | (4) |
|
Adding a function description |
|
|
620 | (1) |
|
Specifying a function category |
|
|
621 | (2) |
|
Adding argument descriptions |
|
|
623 | (1) |
|
Testing and Debugging Your Functions |
|
|
624 | (8) |
|
Using the VBA MsgBox statement |
|
|
625 | (2) |
|
Using Debug.Print statements in your code |
|
|
627 | (1) |
|
Calling the function from a Sub procedure |
|
|
628 | (3) |
|
Setting a breakpoint in the function |
|
|
631 | (1) |
|
|
|
632 | (3) |
|
Chapter 24 VBA Programming Concepts |
|
|
635 | (34) |
|
An Introductory Example Function Procedure |
|
|
636 | (2) |
|
Using Comments in Your Code |
|
|
638 | (1) |
|
Using Variables, Data Types, and Constants |
|
|
638 | (6) |
|
|
|
639 | (1) |
|
|
|
640 | (1) |
|
|
|
641 | (2) |
|
|
|
643 | (1) |
|
|
|
643 | (1) |
|
Using Assignment Expressions |
|
|
644 | (1) |
|
|
|
645 | (2) |
|
|
|
645 | (1) |
|
Declaring multidimensional arrays |
|
|
646 | (1) |
|
Using Built-in VBA Functions |
|
|
647 | (1) |
|
|
|
648 | (10) |
|
|
|
649 | (2) |
|
The Select Case construct |
|
|
651 | (1) |
|
Looping blocks of instructions |
|
|
652 | (4) |
|
|
|
656 | (2) |
|
|
|
658 | (11) |
|
The For Each-Next construct |
|
|
658 | (1) |
|
|
|
659 | (3) |
|
Some useful properties of ranges |
|
|
662 | (4) |
|
|
|
666 | (1) |
|
|
|
666 | (1) |
|
|
|
667 | (1) |
|
|
|
667 | (2) |
|
Chapter 25 VBA Custom Function Examples |
|
|
669 | (48) |
|
|
|
670 | (5) |
|
Does a cell contain a formula? |
|
|
670 | (1) |
|
Returning a cell's formula |
|
|
670 | (1) |
|
|
|
671 | (1) |
|
Returning a worksheet name |
|
|
671 | (1) |
|
Returning a workbook name |
|
|
672 | (1) |
|
Returning the application's name |
|
|
672 | (1) |
|
Returning Excel's version number |
|
|
673 | (1) |
|
Returning cell formatting information |
|
|
673 | (2) |
|
Determining a Cell's Data Type |
|
|
675 | (2) |
|
A Multifunctional Function |
|
|
677 | (2) |
|
Generating Random Numbers |
|
|
679 | (3) |
|
Generating random numbers that don't change |
|
|
680 | (1) |
|
Selecting a cell at random |
|
|
680 | (2) |
|
Calculating Sales Commissions |
|
|
682 | (3) |
|
A function for a simple commission structure |
|
|
683 | (1) |
|
A function for a more complex commission structure |
|
|
684 | (1) |
|
Text Manipulation Functions |
|
|
685 | (6) |
|
|
|
685 | (1) |
|
|
|
685 | (1) |
|
|
|
686 | (1) |
|
Does the text match a pattern? |
|
|
687 | (1) |
|
Does a cell contain a particular word |
|
|
688 | (1) |
|
Does a cell contain text? |
|
|
689 | (1) |
|
Extracting the nth Element from a String |
|
|
690 | (1) |
|
|
|
691 | (1) |
|
|
|
691 | (3) |
|
Counting pattern-matched cells |
|
|
692 | (1) |
|
Counting sheets in a workbook |
|
|
692 | (1) |
|
Counting words in a range |
|
|
692 | (1) |
|
|
|
693 | (1) |
|
|
|
694 | (3) |
|
Calculating the next Monday |
|
|
694 | (1) |
|
Calculating the next day of the week |
|
|
695 | (1) |
|
|
|
695 | (1) |
|
Working with dates before 1900 |
|
|
696 | (1) |
|
Returning the Last Nonempty Cell in a Column or Row |
|
|
697 | (2) |
|
The LASTINCOLUMN function |
|
|
697 | (1) |
|
|
|
698 | (1) |
|
|
|
699 | (2) |
|
Returning the maximum value across all worksheets |
|
|
699 | (1) |
|
|
|
700 | (1) |
|
Advanced Function Techniques |
|
|
701 | (16) |
|
|
|
701 | (2) |
|
Returning an array from a function |
|
|
703 | (2) |
|
Returning an array of nonduplicated random integers |
|
|
705 | (1) |
|
|
|
706 | (2) |
|
|
|
708 | (2) |
|
Using an indefinite number of arguments |
|
|
710 | (7) |
|
|
|
|
Appendix A Excel Function Reference |
|
|
717 | (16) |
|
Appendix B Using Custom Number Formats |
|
|
733 | (20) |
|
|
|
733 | (1) |
|
Automatic number formatting |
|
|
734 | (1) |
|
Formatting numbers by using the Ribbon |
|
|
734 | (1) |
|
Using shortcut keys to format numbers |
|
|
735 | (1) |
|
Using the Format Cells dialog box to format numbers |
|
|
735 | (2) |
|
Creating a Custom Number Format |
|
|
737 | (1) |
|
Parts of a number format string |
|
|
738 | (1) |
|
Custom number format codes |
|
|
739 | (2) |
|
Custom Number Format Examples |
|
|
741 | (1) |
|
|
|
741 | (3) |
|
|
|
744 | (1) |
|
|
|
745 | (1) |
|
|
|
745 | (1) |
|
|
|
746 | (1) |
|
Displaying text in quotes |
|
|
746 | (1) |
|
|
|
746 | (1) |
|
Displaying a negative sign on the right |
|
|
747 | (1) |
|
Conditional number formatting |
|
|
747 | (1) |
|
|
|
748 | (1) |
|
Formatting dates and times |
|
|
749 | (1) |
|
Displaying text with numbers |
|
|
749 | (1) |
|
Displaying a zero with dashes |
|
|
750 | (1) |
|
|
|
751 | (1) |
|
Suppressing certain types of entries |
|
|
751 | (1) |
|
Filling a cell with a repeating character |
|
|
751 | (1) |
|
|
|
752 | (1) |
|
Appendix C Additional Excel Resources |
|
|
753 | (6) |
|
|
|
753 | (1) |
|
Microsoft Technical Support |
|
|
753 | (1) |
|
|
|
754 | (1) |
|
|
|
754 | (1) |
|
Microsoft Excel home page |
|
|
754 | (1) |
|
Microsoft Office home page |
|
|
754 | (1) |
|
|
|
755 | (1) |
|
Accessing newsgroups by using a newsreader |
|
|
755 | (1) |
|
Accessing newsgroups by using a Web browser |
|
|
755 | (1) |
|
|
|
756 | (1) |
|
|
|
757 | (1) |
|
|
|
757 | (1) |
|
|
|
757 | (1) |
|
|
|
758 | (1) |
|
Pearson Software consulting |
|
|
758 | (1) |
|
|
|
758 | (1) |
|
David McRitchie's Excel pages |
|
|
758 | (1) |
|
|
|
758 | (1) |
|
|
|
758 | (1) |
|
Appendix D What's on the CD-ROM? |
|
|
759 | (10) |
|
|
|
759 | (1) |
|
|
|
759 | (1) |
|
Files and Software on the CD |
|
|
760 | (1) |
|
eBook version of Excel 2010 Formulas |
|
|
760 | (1) |
|
Examples files for Excel 2010 Formulas |
|
|
760 | (7) |
|
|
|
767 | (2) |
| Index |
|
769 | |
Rohkem infot Wiley Online kohta