Excel Formulas Free E-book

ExcelFormulas Free E-book 

About Author

John Walkenbach is a leading authority on spreadsheet software, and principal of

JWalk and Associates Inc., a Southern California–based consulting firm that spe-

cializes in spreadsheet application development. John is the author of about 30

spreadsheet books, and has written more than 300 articles and reviews for a variety

of publications, including PC World, InfoWorld, PC Magazine, Windows, and

PC/Computing. He also maintains a popular Internet Web site (The Spreadsheet

Page, www.j-walk.com/ss), and is the developer of the Power Utility Pak, an

award-winning add-in for Microsoft Excel. John graduated from the University of

Missouri, and earned a Masters and PhD from the University of Montana.

Preface

 Thanks for buying my book. If you’re interested in developing killer formulas and

taking Excel to a new level, this book is as good as it gets. I’m confident that you’ll

agree that your money was invested wisely.

Why I Wrote This Book

 I approached this project with one goal in mind: To write the ultimate Excel book

that would appeal to a broad base of users. That’s a fairly ambitious goal. But based

on the feedback I received from the first two editions, I think I’ve accomplished it.

I’ve been using Excel for nearly a decade, and I also spend a lot of time partici-

pating in the Excel newsgroups on the Internet. As a result, I’m very familiar with

the types of questions that come up time and time again. Much of the material in

this book was inspired by questions on the Excel newsgroups. This book provides

the answers to those questions — along with answers to questions that probably

never occurred to you!

As you probably know, most bookstores offer dozens of Excel books. The vast

majority of these books are general-purpose user guides that explain how to use the

features available in Excel (often by simply rewording the text in the help files). A

few others focus on advanced issues such as macro programming or scientific

applications. None (that’s right, none!) hones in on the one fundamental compo-

nent of Excel that is critically important to every user: formulas. Fact is, formulas

are what make a spreadsheet a spreadsheet. The more you know about formulas, the

better your spreadsheets will be. It’s that simple.

Excel is the spreadsheet market leader, by a long shot. This is the case not only

because of Microsoft’s enormous marketing clout, but because it is truly the best

spreadsheet available. One area in which Excel’s superiority is most apparent is for-

mulas. Excel has some special tricks up its sleeve in the formulas department. As

you’ll see, Excel lets you do things with formulas that are impossible with other

spreadsheets.

It’s a safe bet that only about ten percent of Excel users really understand how

to get the most out of worksheet formulas. In this book, I attempt to nudge you into

that elite group. Are you up to it?

 

What You Should Know

This is not a book for beginning Excel users. If you have absolutely no experience

with Excel, this may not be the best book for you — unless you’re one of a rare

breed who can learn a new software product almost instantaneously. vii

01540734 FM.qxd 9/11/03 8:05 PM Page vii

To get the most out of this book, you should have some background using Excel.

Specifically, I assume that you know how to

◆ Create workbooks, insert sheets, save files, and other basic tasks

◆ Navigate through a workbook

◆ Use Excel’s menus, toolbars, and dialog boxes

◆ Use basic Windows features, such as file management and copy and paste techniques

What You Should Have

To make the best use of this book, you need a copy of Microsoft Excel. When I

wrote the current edition of the book, I was using Excel 2003 (which is part of

Microsoft Office 2003). With a few exceptions (noted in the text), the material in

this book also applies to all earlier versions of Excel that are still in use.

To use the examples on the companion CD-ROM, you’ll need a CD-ROM drive.

The examples on the CD-ROM are discussed further in the “About the Companion

CD-ROM” section, later in this preface.

Conventions in This Book

  Take a minute to skim this section and learn some of the typographic conventions
used throughout this book.

Keyboard Conventions

You need to use the keyboard to enter formulas. In addition, you can work with
menus and dialog boxes directly from the keyboard — a method you may find eas-
ier if your hands are already positioned over the keys.

FORMULA LISTINGS

Formulas usually appear on a separate line in monospace font. For example, I may

list the following formula:
=VLOOKUP(StockNumber,PriceList,2,False)
Excel supports a special type of formula known as an array formula. When you
enter an array formula, press Ctrl+Shift+Enter (not just Enter). Excel encloses an
array formula in brackets in order to remind you that it’s an array formula. When I
list an array formula, I include the brackets to make it clear that it is, in fact, an
array formula. For example:

{=SUM(LEN(A1:A10))}

VBA CODE LISTINGS

This book also contains examples of VBA code. Each listing appears in a mono-
space font; each line of code occupies a separate line. To make the code easier to
read, I usually use one or more tabs to create indentations. Indentation is optional,
but it does help to delineate statements that go together.
If a line of code doesn’t fit on a single line in this book, I use the standard VBA
line continuation sequence: a space followed by an underscore character. This indi-
cates that the line of code extends to the next line. For example, the following two
lines comprise a single VBA statement:

If Right(cell.Value, 1) = “!” Then cell.Value _

= Left(cell.Value, Len(cell.Value) - 1)

You can enter this code either exactly as shown on two lines, or on a single line
without the trailing underscore character.

 

and many importance rule waitingfor you. Just Download this book