2021 Microsoft Formulas and Functions A Simplified Guide With Examples on how to take advantage of built-in Excel Formulas and… (Sibley, Kelvin) (Z-Library)

📄 File Format: PDF

💾 File Size: 5.4 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

(This page has no text content)

📄 Page 2

2021 Microsoft Excel Formulas and Functions A Simplified Guide with Examples on How to Take Advantage of Built-in Excel Formulas and Functions Kelvin Sibley

📄 Page 3

Copyright Kelvin Sibley ChurchGate Publishing House USA | UK | Canada © Churchgate Publishing House 2021 All rights reserved. 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, and scanning without permission in writing by the author. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Printed on acid-free paper. Printed in the United States of America © 2021 by Kelvin Sibley

📄 Page 4

Table of Contents CHAPTER ONE BRIEF INTRODUCTION TO SOME EXCEL BASIC How does the formula in Excel look? Define and use names in Excel formula Naming a cell (For Windows users) Define names from a selected range Managing names in your workbook with Name Manager Naming a cell (For macOS users) Define names from a selected range Use names in formulas Deleting or removing a formula in your Excel workbook How to delete a formula but keep the result of the formula (for Windows users) Delete an array formula Delete a formula but keep the results (for web users) Avoiding broken formula in Excel CHAPTER TWO EXCEL FORMULA COMMONLY USED IN BUSINESS ANALYTICS Calculating Percent of Goal Formula: Calculating Percent Variance Alternative: Simplified percent variance calculation Formula: Calculating Percent Variance with Negative Values Formula: Calculating a Percent Distribution Alternative: Percent distribution without a dedicated Total cell Formula: Calculating a Running Total Formula: Applying a Percent Increase or Decrease to Values Formula: Dealing with Divide-by-Zero Errors The “IF” function The “IF” syntax Simple IF cases Using the “IF” function to confirm if a cell is blank Formula: Basic Rounding of Numbers Formula: Rounding to the Nearest Penny Formula: Rounding to Significant Digits Formula: Counting Values in a Range Formula: Creating a Conversion Table CHAPTER THREE MANIPULATING TEXTS WITH FORMULA

📄 Page 5

Formula: Joining Text Strings Formula: Setting Text to Sentence Case What is the TRIM Function? How to use the TRIM Function in Excel? What is the FVSCHEDULE Function? How to use the FVSCHEDULE Function in Excel? Few things to know about the FVSCHEDULE Function Using functions and Nested functions in Excel formula Excel function syntax Nesting Excel functions What is the ACCRINT Function? Formula How to employ the ACCRINT Function in Excel What is the CUMIPMT Function? How to use the CUMIPMT Function in Excel What Is the Excel FV Function? How to use the FV Function in Excel What is the IPMT Function? How to use the IPMT Function in Excel What is the NPER Function? How to use the NPER Function in Excel What is the PDURATION Function? How to use the PDURATION Function in Excel VLOOK UP VLOOKUP Formula How to use VLOOKUP in Excel What is the CELL Function? How to use the CELL function in Excel Things to remember What is Excel ISBLANK Function? How to use the Excel ISBLANK Function How to carry out conditional formatting What is the ISERROR Excel Function? How to use the ISERROR Excel Function What is the ISEVEN Function? How to deploy the ISEVEN Function in Excel How to create conditional formatting Things to remember about the ISEVEN Function What is the ISTEXT Function?

📄 Page 6

How to deploy the ISTEXT Function in Excel What is the TYPE Function? How to use the TYPE Function in Excel A few things to remember about the TYPE Function What is the AND Function? How to employ the AND Function in Excel Things to Remember about the AND Function Using the “IFS” Function Simple syntax What is the NOT Function? How to use the NOT Function in Excel Calculating Gross Profit Margin and Gross Profit Margin Alternative: Calculating Markup Calculating the EBIT and EBITDA Calculating Cost of Goods Sold Calculating Return on Assets Calculating Break Even Calculating Customer Churn ABOUT AUTHOR

📄 Page 7

CHAPTER ONE BRIEF INTRODUCTION TO SOME EXCEL BASIC Before we dabble into the main aspect of this guide – which is the Excel tips, tricks and formulas – let us examine some basic Excel stuff. How does the formula in Excel look? When you take a cursory look at the image above, you will obviously observe some numbers ranging from 1 to 4. That is the basic Excel formula, and you might be interested in knowing what the numbers stand for; - The number 1 represents Functions: The number (circled 1) is the pi function and its function is to always return the value of pi, which is always equal to 22/7, or 3.142… - References: The number A2 will normally return the exact value in the cell A2. - Constants: The number (circled as 3) always stands for constant, which are numbers that are just inherently there in the formula. - Operators: The caret ^ operator in the formula above will usually raise a certain number to a particular power and indicate its value. Define and use names in Excel formula Deploying names in your formula makes the formula look neater, explicable and very easy to maintain. You can define names for your constant, table, function or cell range. Once you adopt the act of using names in your Excel workbook, the names then become especially easier to update, audit and manage. Naming a cell (For Windows users) - Select a cell.

📄 Page 8

- Input a name in the Name box. - Click Enter. Define names from a selected range - Select the range that you plan to name, including the column or row labels. - Choose Formulas, and tap Create from Selection. - From the dialog box of the Create Names from Selection, assign the location containing the labels by clicking on the Left column, Top row, Bottom row, or the Right column check box. - Click OK. Excel will assign names to the cells based on the labels in the designated range. Managing names in your workbook with Name Manager On the Ribbon, navigate to Formulas, click on Defined Names and select Name Manager. From there, you can create, delete, edit and locate all the names deployed in the workbook. Naming a cell (For macOS users) 1. Pick a cell. 2. Fill in a name inside the Name Box. 3. Tap Enter. Define names from a selected range - Click on the range that you plan to name, including the row label or column.

📄 Page 9

- Click on Formulas, and click on Create from Selection. - From the Create Names from Selection dialog box, assign the location containing the labels by tapping on the Left column, Top row, Bottom row, or the Right column check box. - Select OK. The Microsoft Excel will give names to the cells based on the labels in the designated range. Use names in formulas - Select a cell and enter your formula. - Place the cursor of your computer at the exact place or point where you want the name to be used in the formula. - Input the first letter of the name, and select the name from the list shown. - Alternatively, select Formulas, click on Use in Formula and tap or select the name that you plan to use. - Tap Enter. Deleting or removing a formula in your Excel workbook When you delete a particular formula, you should know that the result of that particular formula is also deleted automatically. To avoid this, you can instead just remove the formula instead of deleting it in its entirety. This way, the result of the formula is not deleted. However, if you still want to delete a formula, you can use the steps below; - Select the cell or choose the range of cells that contain the said formula. - Click on Delete. How to delete a formula but keep the result of the formula (for Windows users) To delete a formula while keeping the result of the formula, you will need to copy the formula and then paste the formula in the same cell using the Paste

📄 Page 10

Values option. Follow the prompts below; 1. Select the cell or choose the range of cells that contains the said formula. In the instance where the said formula is actually an array formula, then you will have to select all of the cells in the range of cells containing the array formula. a. Click on a cell from the array formula. b. From the Home tab, under the Editing group, click on Find & Select, and then click on Go To. c. Click on Special. d. Select Current array. 2. From the Home tab, in the Clipboard group, choose Copy . 3. From the Home tab, in the Clipboard group, click on the arrow below Paste , and then select Paste Values. Delete an array formula If you plan to delete a certain array formula, you will need to make sure that all the cells in the cell range that contain the array formula are chosen. To do this; 1. Choose a cell from the array formula. 2. From the Home tab, under the Editing group, click on Find & Select, and then click on Go To. 3. Click on Special. 4. Select Current array. 5. Tap DELETE. Delete a formula but keep the results (for web users) 1. Click on the cell or the range of cells where the formula is located. 2. Click on Home, and select Copy (or simply use the Ctrl + C

📄 Page 11

shortcut). 3. Choose Home, click on the arrow below Paste and choose Paste Values. Avoiding broken formula in Excel When the Microsoft Excel is not able to resolve any formula you are trying to create, an error prompt will most times result. See below; When you get something that looks like the screenshot above, what it means is that the Excel program is unable to understand what you are trying to

📄 Page 12

achieve, and you should endeavor to start over again, albeit more carefully. Start by tapping OK or simply tap on the ESC button to take the error prompt away. Once you have done this, you can navigate back to the cell that contains the broken formula (in fact you will see the formula in the Edit mode ready for editing), and the Excel program will subsequently highlight the exact spot where the problem comes from. If you don’t really know where to go from here, and you don’t want to mess up your program, you can start over again by clicking on the ESC again, or select the Cancel button inside the formula bar, which will take you out of the edit mode. If you want to move forward, then you can consider the following troubleshooting techniques to help you in figuring out what actually went wrong; - Is the formula unable to calculate: This is usually the common problem faced by most users. When you see that your formula is not computing as expected, you should try to check whether you have actually enabled Automatic calculation. If you had, instead, enabled the manual calculation, the formula will not calculate. To confirm the automatic selection , use the steps below; - Click on the File tab, select Options, and then tap on the Formulas category. - From the Calculation options section, under Workbook Calculation, ensure that you select the Automatic option.

📄 Page 13

- Does your function actually start with an equal to sign? One key thing you need to check is whether your formula actually starts with an equal to (=) symbol, because if the formula does not start with an equal to symbol it is not a formula and the Excel will not be able to compute the formula. This is often a common mistake, which you should try as much as possible to avoid. When you input working such as SUM(B2:B10), the Excel will rather display the text string SUM(B2:B10) and will not bring forth a formula. Also, if you input 13/4, Excel will bring a date, such as 14-April or 14/04/2021, instead of actually dividing 13 by 3. To avoid this kind of obvious misrepresentation, always endeavor to begin your function with an equal to sign. For instance, type: =SUM(B2:B10) and =13/4. - Is the opening parentheses matching with the closing parentheses? When you use a particular function in your formula, each of the opening parentheses will need a closing parenthesis for that particular function to work well. You will need to, then, ensure that the pair of parentheses that you are using is a matching pair of parentheses. For example, the formula =IF(B5<0),"Not valid",B5*1.05) will not work because the closing parentheses there are two, but there is only one opening parentheses. This formula will work if it actually looks like: =IF (B5<0,"Not valid",B5*1.05). - Does your formula contain multiplication without the asterisk symbol?

📄 Page 14

It is often an error to imagine that Excel will accept x as a multiplication operator instead of an asterisk (*). The only multiplication that Excel understands is the asterisks. If you have a constant in your formula, you will receive an error prompt from Excel, and the program will help fix the formula by substituting the asterisk symbol in place of an x. However, when you use cell references, the program will output a #NAME? error. - Ensure that you are evaluating a nested formula one after the other By evaluating a formula, you will be able to visualize how a nested or complex formula computes the final result. 1. Click on the formula that you want to evaluate. 2. Tap Formulas, and click on Evaluate Formula.

📄 Page 15

3. Click on Evaluate to confirm the exact value of the underlined expression. The result of the evaluation will be shown in italics. 4. If the underlined part of your formula is actually a reference to another formula, tap Step In to show the other formula in the Evaluation box. Tap on Step Out to go back to the previous formula and cell. You will not see the Step In button the second time the reference shows in the formula—or if the formula refers to a cell in another workbook. 5. Keep doing what you are doing until every area of your formula has been completely evaluated. You need to know that the Evaluate Formula tool will not especially tell why your formula is actually broken. What it can do is to help indicate where the formula is actually broken. This can be a very useful tool when you are working with very large formulas where it often seems complicated to

📄 Page 16

troubleshoot the problem.

📄 Page 17

CHAPTER TWO EXCEL FORMULA COMMONLY USED IN BUSINESS ANALYTICS Calculating Percent of Goal When anyone tells you to compute a percent of goal, they are simply telling you to compare the actual performance to a stated goal. The computation here is not that involved; as you are only dividing the goal by the actual value. This will give you a percentage of value that depicts how much of the goal has been achieved. For example, if your goal (target) is to sell 100 lamps, and you sell just 70, your percentage of goal is 70 percent (70/100). Percent of Goal can equally be referred to as percent of budget or percent of forecast; they are all referring to the same thing. In the figure below, you will see a list of regions with a column for goals and a column for actuals. Be aware that the formula in cell E5 simply divides the value in the Actual column by the value in the Goal column. =D5/C5 How it works This formula is not one of rocket science, as it is not that exacting. What you are doing simply is to use the cell reference to divide a value by another value. The formula should be entered one time inside the first row (cell E5 in this case) and then copy the same formula down to every other row in your table.

📄 Page 18

Formula: Calculating Percent Variance When you are looking for a discrepancy between one value and another, you are most times looking for the variance. To comprehend this, let us imagine that you sell 175 lamps on a particular day, and the second day, you sold only 195 lamps. The difference in the unit sold is very blatant. Removing 175 from 195 will give you a unit variance of 20 lamps. Hence, the percentage variance is the percentage difference between the unit sold last time and the unit sold today (current unit). This variance is usually calculated by removing the benchmark value from the current value. In actual sense, the calculation should look like; (195 – 175)/175 = 11%. This percentage variance indicates that you actually sold 11% more lamps than yesterday. The figure below helps you translate this into a formula. The formula in the E4 column computes the percent variance between current year sales and that of the previous year sales. =(D4-C4)/C4 How it works One key thing you should observe about this formula is the deployment of parentheses. By default, Excel's order of operations commands that the division operation must be performed before the subtraction operation. However, if you follow this command, you would be getting an error prompt. Putting the first part of that formula inside the parentheses will make sure that Excel carries out the subtraction before the division operation. The formula can simply be entered one time inside the first row (cell E4 in this case) and then copy that formula down to every other row in your table. Alternative: Simplified percent variance calculation An alternative way of computing the percent variance is to actually divide the

📄 Page 19

sale for the current year by the one for the previous year and then remove 1. Since Excel carry out division operations before the subtraction operation, parentheses need not be used in this formula; =D4/C4-1 Formula: Calculating Percent Variance with Negative Values You would observe from the earlier section “Formula 2: Calculating Percent Variance,” that it is actually very simple to compute percent variance. The formula works pretty well in most instances. However, when you have a negative value as the benchmark, the formula will break down and won’t work. For instance, let us imagine that you want to start a business and you are expecting to assume a loss the very first year. Therefore, you give your business a budget of negative $10,000. Now assuming that after the first year, you actually earned profit, say $12,000. When you compute the percent variance between your actual revenue and the revenue you budgeted, you will obtain -220%. This can be done on a calculator. 12,000 minus -10,000 divided by -10,000 equals -220%. However, is it plausible to say that your percent variance is negative 220% when it is obvious that you actually earned profit? Well, the issue is that when your benchmark value is a negative value, the result is inverted automatically, making the numbers look somehow wacky. This is a very big issue in the corporate system where budgets can most times be negative values. The solution is to actually leverage the ABS function to negate the negative benchmark value: = (C4-B4)/ABS (B4). The Figure below deploys this formula in cell E4, depicting the different results you get when using the standard percent variance formula and the improved percent variance formula. The Figure below: deploying the ABS function provides you with an accurate percent variance when you are dealing with negative values. How it works The Excel’s ABS function will return the absolute value for whatever number that you pass to it. Putting =ABS(-100) into the cell A1 would give 100. Essentially, the ABS function makes any number a non-negative number. Using the ABS in this formula will negate the effect of the negative

📄 Page 20

benchmark (the negative 10,000 budget in the example) and then returns the correct percent variance. Formula: Calculating a Percent Distribution The Percent distribution is actually a measure of how a metric (like the total revenue) is shared among the various parts that constitute the total. As it is obvious in the figure below, the calculation is actually very simple. Each component is divided by the total. The example below has a cell that has Total revenue (cell C9). You can then divide the revenue for each region by the total to obtain a percent distribution for each region. How it works The formula is not one of rocket science. What you are doing simply is to divide each component by the total. The one thing to take cognizance of is that the cell reference to the Total is entered as an absolute reference ($C$9). The dollar sign is there to lock the reference in its place, making sure that the cell reference pointing to Total does not adjust as the formula is copied down. Alternative: Percent distribution without a dedicated Total cell You do not necessarily need to dedicate a separate cell to the exact total value. The Total can simply be calculated as you go within the percent distribution formula. The figure below shows how you can deploy the SUM function in place of a cell dedicated to holding a Total. Any number you pass to the SUM function is added by the SUM function. You can ensure that the SUM values stay locked as you copy the formula by using absolute references; =C3/SUM($C$3:$C$6)