101 Ready-to-Use Excel Formulas E-Book

Michael Alexander

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2014

Seitenzahl: 262

Beschreibung

Mr. Spreadsheet has done it again with 101 easy-to-apply Excel formulas 101 Ready-to-Use Excel Formulas is filled with the most commonly-used, real-world Excel formulas that can be repurposed and put into action, saving you time and increasing your productivity. Each segment of this book outlines a common business or analysis problem that needs to be solved and provides the actual Excel formulas to solve the problem--along with detailed explanation of how the formulas work. Written in a user-friendly style that relies on a tips and tricks approach, the book details how to perform everyday Excel tasks with confidence. 101 Ready-to-Use Excel Formulas is sure to become your well-thumbed reference to solve your workplace problems. The recipes in the book are structured to first present the problem, then provide the formula solution, and finally show how it works so that it can be customized to fit your needs. The companion website to the book allows readers to easily test the formulas and provides visual confirmation of the concepts presented. * Teaches you how to implement the required Excel formula * Explains and details how the formulas work * Lets you reuse or customize the given formula to address your particular needs * Helps you make the formulas a regular part of your new, more efficient workflow Specific real-world scenarios are used to demonstrate how to most effectively apply Excel and its powerful formulas to complete tasks faster and with greater accuracy than ever before. Now you can save time, automate, and be more efficient and productive with 101 Ready-to-Use Excel Formulas.

Leseprobe

101 Ready-to-Use Excel® Formulas

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright © 2014 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries and may not be used without written permission. Microsoft and Excel are registered trademarks of the Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone]. For technical support, please visit www.wiley.com/techsupport.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2014935514

ISBN 978-1-118-90268-4 (pbk); ISBN 978-1-118-90259-2 (ebk); ISBN 978-1-118-90289-9 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

101 Ready-to-Use Excel Formulas

Table of Contents

Introduction

What You Need to Know

What You Need to Have

How This Book Is Organized

Chapter 1: Introducing Excel Formulas

Chapter 2: Common Mathematical Operations

Chapter 3: Manipulating Text with Formulas

Chapter 4: Working with Dates and Times

Chapter 5: Performing Conditional Analysis

Chapter 6: Using Lookup Formulas

Chapter 7: Common Business and Financial Formulas

Chapter 8: Common Statistical Analysis

Chapter 9: Using Formulas with Conditional Formatting

Conventions in This Book

What the icons mean

About the Sample Files

Chapter 1: Introducing Excel Formulas

Creating and Editing Excel Formulas

Methods for entering formulas

Editing a formula

Using Formula Operators

Understanding the order of operator precedence

Using nested parentheses

Relative versus Absolute Cell References

Using External Cell References

Formula Calculation Modes

Leveraging Excel Functions

Why to use Excel functions

Understanding function arguments

Getting Help from the Insert Function Wizard

Understanding Formula Errors

Using Named Ranges in Formulas

Creating a named range

Working with the Name Box

Chapter 2: Common Mathematical Operations

Formula 1: Calculating Percent of Goal

How it works

Alternative: Using a common goal

Formula 2: Calculating Percent Variance

How it works

Alternative: Simplified percent variance calculation

Formula 3: Calculating Percent Variance with Negative Values

How it works

Formula 4: Calculating a Percent Distribution

How it works

Alternative: Percent distribution without a dedicated Total cell

Formula 5: Calculating a Running Total

How it works

Formula 6: Applying a Percent Increase or Decrease to Values

How it works

Formula 7: Dealing with Divide-by-Zero Errors

How it works

Formula 8: Basic Rounding of Numbers

How it works

Formula 9: Rounding to the Nearest Penny

How it works

Formula 10: Rounding to Significant Digits

How it works

Formula 11: Counting Values in a Range

How it works

Formula 12: Creating a Conversion Table

How it works

Chapter 3: Manipulating Text with Formulas

Formula 13: Joining Text Strings

How it works

Formula 14: Setting Text to Sentence Case

How it works

Formula 15: Removing Spaces from a Text String

How it works

Formula 16: Extract Parts of a Text String

How it works

Formula 17: Finding a Particular Character in a Text String

How it works

Alternative: Finding the second instance of a character

Formula 18: Substituting Text Strings

How it works

Formula 19: Counting Specific Characters in a Cell

How it works

Formula 20: Adding a Line Break within a Formula

How it works

Formula 21: Cleaning Strange Characters from Text Fields