I teach the writing of formulas and associated topics in Volume 3: Excel“

Beyond The Basics. I show, in a step-by-step exposition, the proper way

for writing cell references in a formula. The book describe tricks for

copying/cutting and pasting in several examples. In addition, I discuss

special pasting options.

Finally, different types of functions are classified under logical categories

and discussed within the optimal category. The categories include

financial, Statistical, Text, Information, Logical, and “Smart” Logical.

22

Intoduction & Contents

MANAGING & TABULATING DATA

Excel has extremely powerful data entry, data management, and

tabulation tools. The combination of tools provide almost database like

power to Excel. Unfortunately, the poor quality of the menu layout and

the help preclude the possibility of the user self-learning these features.

These features are taught in Volume 4: Managing & Tabulating Data in

Excel

CHARTING

Please refer to book two in this series. The book title is Charting in Excel.

Sample data

Most of the tutorials use publicly available data from the International

labor Organization (ILO). I used a simple data set with only a few

columns and observations. All the sample data files are included in the

zipped file.

The samples for functions use several small data sets that are more suited

to illustrating the power and usefulness of the functions.

I have not included the data set for conducting statistical procedures.

This is intentional; often, readers fail to internalize the few key concepts

of hypothesis testing because they do not subject themselves to a “sink-or-

swim” inference-drawing thinking and imbibing process when

interpreting the results of statistical procedures.

23

Page for Notes

CHAPTER 1

WRITING FORMULAS

This chapter discusses the following topics:

” THE BASICS OF WRITING FORMULAE

” TOOL FOR USING THIS CHAPTER EFFECTIVELY: VIEWING

THE FORMULA INSTEAD OF THE END RESULT

” The A1 VS THE R1C1 STYLE OF CELL REFERENCES

” TYPES OF REFERENCES ALLOWED IN A FORMULA

” REFERENCING CELLS FROM ANOTHER WORKSHEET

” REFERENCING A BLOCK OF CELLS

” REFERENCING NON“ADJACENT CELLS

” REFERENCING ENTIRE ROWS

” REFERENCING ENTIRE COLUMNS

” REFERENCING CORRESPONDING BLOCKS OF

CELLS/ROWS/COLUMNS FROM A SET OF WORKSHEETS

The most important functionality offered by a spreadsheet application is

the ease and flexibility of writing formulae. In this chapter, I start by

showing how to write simple formula and then build up the level of

complexity of the formulae.

Within the sections of this chapter, you will find tips and notes on

commonly encountered problems or issues in formula writing.

Chapter 1: Writing Formulas

THE BASICS OF WRITING FORMULAE

1.1

This section teaches the basics of writing functions.

TOOL FOR USING THIS CHAPTER EFFECTIVELY:

1.2

VIEWING THE FORMULA INSTEAD OF THE END

RESULT

For ease of understanding this chapter, I suggest you use a viewing option

that shows, in each cell on a worksheet, the formula instead of the result.

Follow the menu path TOOLS/OPTIONS/VIEW. In the area “Window

Options” select the option “Formulas” as shown in Figure 1.

Execute the dialog by clicking on the button OK. Go back to the

worksheet. The formula will be shown instead of the calculated value.

Eventually you will want to return to the default of seeing the results

instead of the formula. Deselect “formula” in the area “Windows Options”

in TOOLS/OPTIONS/VIEW.

25

Statistical Analysis with Excel

Figure 1: Viewing the formulas instead of the formula result