S

Sachs, Jonathon, 4

sales commissions calculation, 692-695

Salvage argument, 344

Sample box, Format Cells dialog box, 760

Save As command, 621

Save As dialog box, VB Editor, 642

Save as Template option, Chart Tools, 446, 459

Save tool, Quick Access toolbar (QAT), 14

saving projects, Visual Basic (VB) editor, 621

scaling values, 755-758

scenario management, 29

Scenario Manager program, 27

Scientific number format, Options dialog box, 749

scrambling text, 696

ScreenTips, 12, 14, 16-17, 104

Search box, Help system, 19

SEARCH function, 127-128, 190, 743

searching within strings, 129-130

SECOND function, 164, 729

seconds

adding to a time, 172-173

decimal, converting to a time, 172

security, macro, 609-610

Security Warning, 609-610, 776

Select a Function list box, 105

Select Arguments dialog box, 108

Select Case construct, 661-662

Select Data Source, Chart Tools, 456

selecting in tables, 241

Semantic errors, 582

serial numbers

date, 140-141

time, 143-144

SERIES formula

unlinking chart series from its data range, 447

using names in, 445-447

series of dates, generating, 151-153

Series Options tab, Chart Tools, 454, 457

series_name argument, SERIES formula, 444

SERIESSUM function, 738

Set keyword, 676

Set Precision as Displayed check box, Options dialog box, 592

Settings tab, Data Validation dialog box, 555-557

shading

checkerboard, 548

displaying alternate-row shading, 547-548

groups of rows, 548-549

shapes, 22, 24

Sheet tab, 7

SHEETOFFSET function, 709-710

shortcut keys, number formatting using, 747-748

shortcut letters, Ribbon component, 11

shortcut menus and the mini toolbar, 15-16

shortcuts, for creating cell and range names, 65-70

Show a Zero in Cells That Have Zero Value option, Display options, 180

Show Bars Only option, 534

Show Formulas option, 597

Show Icon Only option, Trend column, 540-541

Show Values As tab, Data Field Settings dialog box, 494-495

SIGN function, 738

simple amortization schedules, 348-351

simultaneous equations, solving, 284-286, 439-440

SIN function, 103, 108, 464, 738

Single data type, 650

single-cell array formulas

computing average that excludes zeros, 405-406

counting characters in a range, 391-392

counting number of differences in two ranges, 407-408

counting number of error values in a range, 401-402

counting text cells in a range, 393-395

creating dynamic crosstab table, 417-418

determining closest value in a range, 414

determining whether a range contains valid values, 409

determining whether particular value appears in a range, 406-407

eliminating intermediate formulas, 395-396

finding row of value's nth occurrence in a range, 408-409

ranking data with an array formula, 416-417

removing non-numeric characters from strings, 413-414

returning last value in a column, 414-416

returning location of maximum value in a range, 408

returning longest text in a range, 409

returning the last value in a row, 416

summing based on conditions, 402-404

summing digits of an integer, 410-411

summing every nth value in a range, 412-413

summing n largest values in a range, 405

summing range that contains errors, 400-401

summing rounded values, 411-412

summing three smallest values in a range, 392-393

using array in lieu of range reference, 396-397

single-cell goal seeking, 53-54

SINH function, 738

Size and Properties dialog box, Chart Tools, 446

sizes argument, SERIES formula, 445

SKEW function, 741

SLN function, 344, 345, 733

slope, calculating, 476-477

SLOPE function, 741

SMALL function, 201-202, 392-393, 405, 409, 421, 741

Smart Icon, 51

smart tags, 16-17

SmartArt, 15, 22

SmartTag, 14-15, 48, 115, 255, 602

Smoothed Line option, Format Data Series dialog box, 468

smoothing payments, 331-332

solvency ratios, 368-369

Solver add-in, 28, 30, 55

Sort By Color option, Tables menu, 246

Sort command, 26

Sort Largest to Smallest option, Tables menu, 246

Sort Newest to Oldest option, Tables menu, 246

Sort Oldest to Newest option, Tables menu, 246

Sort Smallest to Largest option, Tables menu, 246

sorting tables, 246-247

Source data, 490

spaces, excess, 126-127, 585

Special number format, Options dialog box, 749

special symbols, 764

spheres, surface and volume of, 283

Spreadsheet Page web site, 772

Spreadsheet Research (SSR) website, 582

SQL Server, 25

SQRT function, 102

SQRT worksheet function, 659, 670, 738

SQRTPI function, 738

squares, area and perimeter of, 282

Stacked Bar Chart, Chart Tools, 457

STANDARDIZE function, 741

static chart, 447

static value, 165

statistical functions, 112

status bar, 17

StdDev formula, Tables menu, 250

STDEV function, 687, 741

STDEVA function, 741

STDEVP function, 741

STDEVPA function, 741

Stephen Bullen's Excel Page web site, 772

STEYX function, 741

Stop Automatically Creating Calculated Columns option, 255

storing VBA code, in VB editor, 617-618

String (fixed length) data type, 650

String (variable length) data type, 650

strings

converting non-date string to date, 153

counting characters in, 123-124

counting occurrences of substrings in cells, 131

determining whether identical, 120-121

extracting all but first word of, 134-135

extracting characters from, 128

extracting first word of, 133-134

extracting last word of, 134

extracting nth element from, 699-700

finding and searching within, 129-130

overview, 653-654

removing non-numeric characters from, 413-414

repeating, 124

returning last space character position, 570-574

reversing, 695

searching and replacing within, 130

Style dialog box, 752

Styles gallery, 12

stylistic formatting, 20, 22

SUBSTITUTE function, 127-129, 135, 648, 743

substrings, counting occurrences in cells, 131

Subtotal command, 27, 107

Subtotal dialog box, 273-274

SUBTOTAL function, 107, 177, 250-252, 259, 273, 276, 738

subtotals, 26-27, 490

Sum formula, Tables menu, 250

SUM function, 97, 378-379, 392-393, 687, 721-723, 738

SUMIF function, 177, 202, 204-205, 206, 352, 402-403, 738

SUMIFS function, 177, 206-208, 356, 402-403, 738

Summarize By tab, Data Field Settings dialog box, 494-495

Summary below Data option, Subtotal dialog box, 275

summing

conditional sums using multiple criteria, 208-210

formulas, 201-204

only negative values, 205-207

records in databases and pivot tables, 180-181

values based on a date comparison, 207-208

values based on a different range, 207

values based on a text comparison, 207

worksheet cells, 178-180

SUMPRODUCT function, 177, 186, 207, 378, 738

SUMSQ function, 177, 738

SUMX2MY2 function, 177, 738

SUMX2PY2 function, 177, 738

SUMXMY2 function, 177, 738

Sunday, determining date of most recent, 159

support, technical, 768-769

suppressing entries, 764-765

surface

of cones, 284

of cubes, 283

of spheres, 283

Switch Row/Column option, Chart Tools, 459

SYD function, 344, 733

Symbol command, Insert Symbol Dialog box, 764

Symbol dialog box, 118

symbols, special, 764

Syntax errors, 582-583, 634