Page 299 - Excel Data Analysis
P. 299
18 537547 Index.qxd 3/4/03 11:54 AM Page 285
Excel Data Analysis:
Your visual blueprint for creating and
analyzing data, charts, and PivotTables
J M merged worksheets. See
consolidation, data
joined text, 162, 270 Macro Recorder, 156 message boxes, display, 184–185
justification, cell data, 4 macros. See also formulas; Microsoft Word Document
functions; VBA Object, 85
K absolute cell references, 164 military time, 5
assign to menus, 188–189
keyboard shortcuts, 169, 189, 234–238 MIN function, 81
call forms, 206–207
KeyDown event, 211 Min function, 37, 59
kurtosis, 227 cell references, 164 minimum values, find, 37
certification, 174–177 minimum/maximum values. See
L create, 3 highest/lowest values
create add-ins, 215 minus sign (-), hidden record
Label button, 196–197 create with Visual Basic Editor, indicator, 40–41
LARGE function, 81
170–171 minutes, format, 17
legends (chart), 118, 154 customized forms controls, 202–203 modes, 227
less than (<), comparison operator
digital signatures, 174–177 month names, sort, 32
comparison operator, Solver disable, 174 months, format, 17
constraints, 67
display in Macro dialog box, 167 MouseDown event, 211
data list filters, 53 Excel functions in, 192–193 moved formulas, 62–63
line charts, 106–107
Excel/VBA equivalent functions, moving average trendlines, 115
linear trendlines, 115 192–193 moving averages, 230–231
link operation
record, 164–165 Moving Averages Tool, 230–231
cells to forms controls, 200 relative cell references, 164 MsgBox function, 184–185
Hyperlink option, 85
run automatically, 190–191 MultiPage button, 196–197
OLE (Object Linking and run on demand, 166–167
Embedding), 74–75 multiply values, 37, 103
security settings, 174–177
worksheets to other document N
types, 84–85 store in workbooks, 165
trusted sources, 177
worksheets to worksheets, 39 #N/A message, 273
list boxes, 197 update, 172–173 #NAME? message, 9, 273
user input, 186–187
ListBox button, 196–197 named arguments, 187
lists of values. See data lists MATCH function, 76–77 named ranges. See ranges, named
math functions (Excel), quick
loan amortization, 82–83 reference, 249–251 named styles, 20–21. See also
locating. See find operation formats; templates
math functions (VBA), quick
locked worksheets. See protected reference, 264–265 negative numbers, format, 16
worksheets nested IF functions, 69
MAX function, 81
logarithmic trendlines, 115 Max function, 37, 59 New Web Query dialog box, 92–93
logical functions (Excel), quick 1900 date system, 5
reference, 247 maximum values, find, 37 1904 date system, 5
logical operators, 163 maximum/minimum values. See nodes, 168
highest/lowest values; MAX
Long data type, 158 function; MIN function #NULL! message, 273
looking for. See find operation #NUM! message, 273
means, 227, 229
lookup functions, 74–75, 248 MEDIAN function, 81 numbers
Lookup Wizard, 79 formats, 16–17, 19
medians, 81, 227
loops, VBA, 182–183 menus precision, 4
lowest/highest values. See assign macros to, 188–189 separators in imported data, 89
highest/lowest values
create, 189 as text, 4
truncation, 4
285
