www.it-ebooks.info
,Microsoft Excel 2010:
® ®
Data Analysis and
Business Modeling
Wayne L. Winston
www.it-ebooks.info
,PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2011 by Wayne L. Winston
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any
means without the written permission of the publisher.
Library of Congress Control Number: 2010934987
ISBN: 978-0-7356-4336-9
Printed and bound in the United States of America.
Microsoft Press books are available through booksellers and distributors worldwide. For further information
about international editions, contact your local Microsoft Corporation office or contact Microsoft Press
International directly at fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to
.
Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/IntellectualProperty/
Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of
their respective owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and
events depicted herein are fictitious. No association with any real company, organization, product, domain name,
e-mail address, logo, person, place, or event is intended or should be inferred.
This book expresses the author’s views and opinions. The information contained in this book is provided without
any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or
distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by
this book.
Acquisitions Editor: Rosemary Caperton
Developmental Editor: Devon Musgrave
Project Editor: Rosemary Caperton
Editorial and Production: John Pierce and Waypoint Press
Technical Reviewer: Mitch Tulloch; Technical Review services provided by Content Master,
a member of CM Group, Ltd.
Cover: Twist
Body Part No. X17-37446
www.it-ebooks.info
, Table of Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Lx
1 What’s New in Excel 2010 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2 Range Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
3 Lookup Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
4 The INDEX Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
5 The MATCH Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
6 Text Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
7 Dates and Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
8 Evaluating Investments by Using Net Present Value
Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
9 Internal Rate of Return . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
10 More Excel Financial Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
11 Circular References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
12 IF Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
13 Time and Time Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
14 The Paste Special Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books and learning
resources for you. To participate in a brief online survey, please visit:
www.microsoft.com/learning/booksurvey/
iii
www.it-ebooks.info
,Microsoft Excel 2010:
® ®
Data Analysis and
Business Modeling
Wayne L. Winston
www.it-ebooks.info
,PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2011 by Wayne L. Winston
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any
means without the written permission of the publisher.
Library of Congress Control Number: 2010934987
ISBN: 978-0-7356-4336-9
Printed and bound in the United States of America.
Microsoft Press books are available through booksellers and distributors worldwide. For further information
about international editions, contact your local Microsoft Corporation office or contact Microsoft Press
International directly at fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to
.
Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/IntellectualProperty/
Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. All other marks are property of
their respective owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and
events depicted herein are fictitious. No association with any real company, organization, product, domain name,
e-mail address, logo, person, place, or event is intended or should be inferred.
This book expresses the author’s views and opinions. The information contained in this book is provided without
any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or
distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by
this book.
Acquisitions Editor: Rosemary Caperton
Developmental Editor: Devon Musgrave
Project Editor: Rosemary Caperton
Editorial and Production: John Pierce and Waypoint Press
Technical Reviewer: Mitch Tulloch; Technical Review services provided by Content Master,
a member of CM Group, Ltd.
Cover: Twist
Body Part No. X17-37446
www.it-ebooks.info
, Table of Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Lx
1 What’s New in Excel 2010 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2 Range Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
3 Lookup Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
4 The INDEX Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
5 The MATCH Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
6 Text Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
7 Dates and Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
8 Evaluating Investments by Using Net Present Value
Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
9 Internal Rate of Return . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
10 More Excel Financial Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
11 Circular References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
12 IF Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
13 Time and Time Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
14 The Paste Special Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books and learning
resources for you. To participate in a brief online survey, please visit:
www.microsoft.com/learning/booksurvey/
iii
www.it-ebooks.info
