Vijay™s first software is undergoing testing prior to its release in Jan

2003. The software ” titled “Word Usability Enhancer” ” will

revolutionize the way users interact with Microsoft Word, providing

users with a more intuitive interface, readily accessible tutorials, and

numerous timesaving and annoyance-removing macros and utilities.

He plans to create a similar tool for Microsoft Excel, and, depending

on resource constraints and demand, for PowerPoint, Star Office, etc.

4

Stage 3: Construction of the first “feedback-designed” Office and Statistics

software

Vijay™s eventual goal is the construction of productivity software

that will provide stiff competition to Microsoft Office. His hope is

that the success of the software tools and the books will convince

financiers to provide enough capital so that a successful software

development and marketing endeavor can take a chunk of the multi-

billion dollar Office Suite market.

Prior to the construction of the Office software, Vijay plans to

construct the “Definitive” statistics software. Years of working on

and teaching the current statistical software has made Vijay a

master at picking out the weaknesses, limitations, annoyances, and,

sometimes, pure inaccessibility of existing software. This 1.5 billion

dollar market needs a new visionary tool, one that is appealing and

inviting to users, and not forbidding, as are several of the current

software. Mr. Gupta wants to create integrated software that will

encompass the features of SPSS, STATA, LIMDEP, EViews,

STATISTICA, MINITAB, etc.

Other

He has plans for writing books on the “learning process.” The books

will teach how to understand one™s approach to problem solving and

learning and provide methods for learning new techniques for self-

learning.

5

CONTENTS

CHAPTER 1 WRITING FORMULAS 25

1.1 The Basics Of Writing Formulae 26

1.2 Tool for using this chapter effectively: Viewing the formula instead of the end

result 26

1.2.a The “A1” vs. the “R1C1“ style of cell references 28

1.2.b Writing a simple formula that references cells 29

1.3 Types Of References Allowed In A Formula 30

1.3.a Referencing cells from another worksheet 30

1.3.b Referencing a block of cells 30

1.3.c Referencing non“adjacent cells 31

1.3.d Referencing entire rows 32

1.3.e Referencing entire columns 32

1.3.f Referencing corresponding blocks of cells/rows/columns from a set of

worksheets 33

CHAPTER 2 COPYING/CUTTING AND PASTING FORMULAE 35

2.1 Copying And Pasting A Formula To Other Cells In The Same Column 36

2.2 Copying And Pasting A Formula To Other Cells In The Same Row 37

2.3 Copying And Pasting A Formula To Other Cells In A Different Row And Column

38

2.4 Controlling Cell Reference Behavior When Copying And Pasting Formulae (Use

Of The “$” Key) 39

2.4.a Using the “$” sign in different permutations and computations in a

formula 41

2.5 Copying And Pasting Formulas From One Worksheet To Another 42

2.6 Pasting One Formula To Many Cells, Columns, Rows 43

2.7 Pasting Several Formulas To A Symmetric But Larger Range 43

2.8 Defining And Referencing A “Named Range” 43

Adding several named ranges in one step 46

Using a named range 47

2.9 Selecting All Cells With Formulas That Evaluate To A Similar Number Type 48

2.10 Special Paste Options 48

2.10.a Pasting only the formula (but not the formatting and comments) 48

2.10.b Pasting the result of a formula, but not the formula itself 48

2.11 Cutting And Pasting Formulae 49

Intoduction & Contents

2.11.a The difference between “copying and pasting” formulas and “cutting and

pasting” formulas 49

2.12 Creating A Table Of Formulas Using Data/Table 50

2.13 Saving Time By Writing, Copying And Pasting Formulas On Several Worksheets

Simultaneously 50

CHAPTER 3 PASTE SPECIAL 52

3.1 Pasting The Result Of A Formula, But Not The Formula 53

3.2 Other Selective Pasting Options 56

3.2.a Pasting only the formula (but not the formatting and comments) 56

3.2.b Pasting only formats 56

3.2.c Pasting data validation schemes 57

3.2.d Pasting all but the borders 57

3.2.e Pasting comments only 57

3.3 Performing An Algebraic “Operation” When Pasting One Column/Row/Range On

To Another 58

3.3.a Multiplying/dividing/subtracting/adding all cells in a range by a number

58

3.3.b Multiplying/dividing the cell values in cells in several “pasted on”

columns with the values of the copied range 59

3.4 Switching Rows To Columns 59

CHAPTER 4 INSERTING FUNCTIONS 61

4.1 Basics 61

4.2 A Simple Function 64

4.3 Functions That Need Multiple Range References 67

4.4 Writing A “Function Within A Function” 69

4.5 New Function-Related Features In The XP Version Of Excel 73

Searching for a function 73

4.5.a Enhanced Formula Bar 73

4.5.b Error Checking and Debugging 74

CHAPTER 5 TRACING CELL REFERENCES & DEBUGGING FORMULA

ERRORS 76

5.1 Tracing the cell references used in a formula 76

5.2 Tracing the formulas in which a particular cell is referenced 78

5.3 The Auditing Toolbar 79

5.4 Watch window (only available in the XP version of Excel) 80

7

Statistical Analysis with Excel