976
Index
Formulas option
defined, 85
Go to Special dialog box, 75, 661
Paste Special dialog box, 87
Formulas tab
Excel Options dialog box, 665–666, 764
Function Library group, 206
For-Next loop
looping through ranges, 891
modifying chart properties, 898
VBA macros, 818
Fourier Analysis tool, Analysis ToolPak add-in, 787
Fraction format category, 554
Fraction format, Format Cells dialog box, 46–47
fractions
displaying, 564–565
entering numbers with, 40
Frame control, 844
free-floating text, 445
FreeForm Shape, 519, 523
freeware programs, 934
Freeze First Column option, 63
Freeze Panes option, 63
Freeze Top Row option, 63
freezing panes, 62–64
frequency distributions
creating, 722–724
FREQUENCY function, 294–295
using a pivot table, 298–299
using formulas, 296–297
using the Analysis ToolPak, 297–298
frequency distribution.xlsx sample file, 937
FREQUENCY function
arrays and, 371
counting formulas, 294–295
function of, 930
overview, 282
Frontline Systems company, 775
FTEST function, 918
F.TEST function, 930
F-Test tool, Analysis ToolPak add-in, 786–787
Full Screen mode, Quick Access toolbar, 541
Function Arguments dialog box
displaying, 208
inserting custom function, 835–836
inserting functions into formulas, 206
specifying arguments, 825
Function drop-down list, Consolidate dialog box, 600
Function keys, 954–955
Function Library group, 206formulas, lookup (continued)
determining cell address of values within range, 326
determining letter grades for test scores, 322
exact values, 318–319
HLOOKUP function, 313–314
looking up values by using closest match, 327–328
looking up values from multiple lookup tables, 321
LOOKUP function, 314–315
lookup relevant functions, 310–312
overview, 309–310
performing two-way lookup, 323–326
values to left, 319–320
VLOOKUP function, 312–313
making exact copies of, 227–228
naming techniques
applying names to existing references, 226
names for constants, 222–223
names for formulas, 223–224
range intersections, 224–226
overview, 30–31, 195–196, 281–282
selecting array formula range, 364
summing
all cells in a range, 299–300
computing a cumulative sum, 300–302
“top n” values, 302
worksheet cells, 281–283
syntax of, linking workbooks, 590–591
that use functions, 200–201
transforming data, 240
using cell references
changing types of, 212
from other workbooks, 213–214
from other worksheets, 212–213
overview, 209–210
relative, absolute, and mixed, 210–212
using custom functions, 828
using functions in, 199–202
using in tables
referencing data, 217–218
summarizing data, 214–216
within a table, 216–217
using operators in
operator precedence, 197–199
overview, 196–199
values
converting to, 228
hard-code, 227
viewing using Excel Auditing tools, 662–663
Formulas & Number Formatting option, 85, 8860_474877-bindex.indd 97660_474877-bindex.indd 976 3/29/10 11:48 PM3/29/10 11:48 PM