Formula Excel : Adding a Line Break within a Formula

When creating charts in Excel, it’s sometimes useful to force line breaks for the purpose of composing better visualizations. Take the chart shown in Image 3-9, for example. Here, the X-axis labels in the chart include the data value in addition to the sales rep. This setup works well when you don’t want to inundate your chart with data labels. Image 3-9: The X-axis labels...

Read More!

Formula Excel : Counting Specific Characters in a Cell

A useful trick is to be able to count the number of times a specific character exists in a text string. The technique for doing this in Excel is a bit clever. To figure out, for example, how many times the letter s appears in the word Mississippi, you can count them by hand, of course, but systematically, you can follow these general steps: Measure the character length of...

Read More!

Formula Excel : Substituting Text Strings

In some situations, it’s helpful to substitute some text with other text. One such case is when you encounter the annoying apostrophe S (’S) quirk that you get with the PROPER function. To see what we mean, enter this formula into Excel: =PROPER("STAR'S COFFEE") This formula is meant to convert the given text into title case (where the first letter of every word is capitalized)....

Read More!

Formula Excel : Finding a Particular Character in a Text String

Excel’s LEFT, RIGHT, and MID functions work great for extracting text, but only if you know the exact position of the characters you are targeting. What do you do when you don’t know exactly where to start the extraction? For example, if you had the following list of Product codes, how would you go about extracting all the text after the hyphen? PRT-432 COPR-6758 SVCCALL-58574 The...

Read More!

Formula Excel : Extract Parts of a Text String

One of the most important techniques for manipulating text in Excel is the capability to extract specific portions of text. Using Excel’s LEFT, RIGHT, and MID functions, you can perform tasks such as: Convert nine-digit postal codes into five-digit postal codes Extract phone numbers without the area code Extract parts of employee or job codes for use somewhere else Image...

Read More!

Formula Excel : Removing Spaces from a Text String

If you pull data in from external databases and legacy systems, you will no doubt encounter text that contains extra spaces. Sometimes these extra spaces are found at the beginning of the text, whereas at other times, they show up at the end. Extra spaces are generally evil because they can cause problems in lookup formulas, charting, column sizing, and printing. Image 3-3...

Read More!

Formula Excel : Setting Text to Sentence Case

Excel provides three useful functions to change the text to upper-, lower-, or proper case. As you can see in rows 6, 7, and 8 illustrated in Figure 3-2, these functions require nothing more than a pointer to the text you want converted. As you might guess, the UPPER function converts text to all uppercase, the LOWER function converts text to all lowercase, and the PROPER...

Read More!

Formula Excel : Joining Text Strings

One of the more basic text manipulation actions you can perform is joining text strings together. In the example shown in Image 3-1, you create a full-name column by joining together first and last names. Image 3-1: Joining first and last names. How it works This example illustrates the use of the ampersand (&) operator. The ampersand operator tells Excel to concatenate...

Read More!

Formula Excel : Creating a Conversion Table

You may work at a company where you need to know how many cubic yards can be covered by a gallon of material, or how many cups are needed to fill an Imperial Gallon. You can use Excel’s CONVERT function to produce a conversion table containing every possible type of conversion that you need for a set of measures. Image 12-14 illustrates a conversion table created using nothing...

Read More!

Formula Excel : Counting Values in a Range

Excel provides several functions to count the values in a range: COUNT, COUNTA, and COUNTBLANK. Each of these functions provides a different method of counting based on whether the values in your range are numbers, numbers and text, or blank. Image 2-13 illustrates the different kinds of counting you can perform. In row 12, the COUNT function is counting only exams where students...

Read More!

Formula Excel : Rounding to Significant Digits

In some financial reports, figures are presented in significant digits. The idea is that when you’re dealing with numbers in the millions, you don’t need to inundate a report with superfluous numbers for the sake of showing precision down to the tens, hundreds, and thousands places. For instance, instead of showing the number 883,788, you could choose to round the number to...

Read More!

Formula Excel : Rounding to the Nearest Penny

In some industries, it is common practice to round a dollar amount to the nearest penny. Image 2-11 demonstrates how rounding a dollar amount up or down to the nearest penny can affect the resulting number. Image 2-11: Rounding to the nearest penny. How it works You can round to the nearest penny by using the CEILING or FLOOR functions.The CEILING function rounds a number...

Read More!

Formula Excel : Basic Rounding of Numbers

Often, your customers want to look at clean, round numbers. Inundating a user with decimal values and unnecessary digits for the sake of precision can actually make your reports harder to read. For this reason, you may want to consider using Excel’s rounding functions. Image 2-10 illustrates how the number 9.45 is affected by the use of the ROUND, ROUNDUP, and ROUNDDOWN functions. Image...

Read More!

Formula Excel : Dealing with Divide-by-Zero Errors

In mathematics, division by zero is impossible. One way to understand why it’s impossible is to consider what happens when you divide a number by another. Division is really nothing more than fancy subtraction. For example, 10 divided by 2 is the same as starting with 10 and continuously subtracting 2 as many times as needed to get to zero. In this case, you would need to...

Read More!

Formula Excel : Applying a Percent Increase or Decrease to Values

A common task for an Excel analyst is to apply a percentage increase or decrease to a given number. For instance, when applying a price increase to a product, you would typically raise the original price by a certain percent. When giving a customer a discount, you would decrease that customer’s rate by a certain percent. Image 2-8 illustrates how to apply a percent increase...

Read More!

Formula Excel : Calculating a Running Total

Some organizations like to see a running total as a mechanism to analyze the changes in a metric as a period of time progresses. Image 2-7 illustrates a running total of units sold for January through December. The formula used in cell D3 is copied down for each month: =SUM($C$3:C3) Image 2-7: Calculating a running total How it works In this formula, you use the SUM...

Read More!

Formula Excel : Calculating a Percent Distribution

Percent distribution is a measure of how a metric (such as total revenue) is distributed among the component parts that make up the total. As you can see in Image 2-5, the calculation is relatively simple. You divide each component part by the total. This example has a cell that contains Total revenue (cell C9). You then divide each region’s revenue by the total to get a percent...

Read More!

Formula Excel : Calculating Percent Variance with Negative Values

In the previous section, “Formula 2: Calculating Percent Variance,” you discovered how to calculate a percent variance. That formula works beautifully in most cases. However, when the benchmark value is a negative value, the formula breaks down. For example, imagine that you’re starting a business and expect to take a loss the first year. So you give yourself a budget of negative...

Read More!

Formula Excel : Calculating Percent Variance

A variance is an indicator of the difference between one number and another. To understand this, imagine that you sold 120 widgets one day, and on the next day, you sold 150. The difference in sales in actual terms is easy to see; you sold 30 more widgets. Subtracting 120 widgets from 150 widgets gives you a unit variance of +30. So what is a percent variance? This is essentially...

Read More!

Formula Excel : Calculating Percent of Goal

When someone asks you to calculate a percent of goal, she is simply saying tocompare actual performance to a stated goal. The math involved in this calculation issimple: Divide the goal by the actual. This gives you a percentage value thatrepresents how much of the goal has been achieved. For instance, if your goal is tosell 100 widgets, and you sell 80, your percent of goal...

Read More!

Tutorial Excel : Using Named Ranges in Formulas

A named range is nothing more than a cell or range of cells that has been given a friendly, descriptive name. Naming your ranges allows you use easily recognizable names in your formulas instead of cell addresses. For instance, say that you have lineitem sales in cells A1:A25 and you have a percent tax in cell B1. You could calculate a total sale amount with tax using this...

Read More!

Tips Excel : Understanding Formula Errors

It’s not always smooth sailing when you’re working with formulas. Sometimes a formula returns an error value instead of the value you were expecting. Excel helps you identify what the problem may be by returning one of seven error values: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!, explained in the following list: Image : Error formula excel #DIV/0!: This...

Read More!

Tutorial Excel : Getting Help from the Insert Function Wizard

If you find that you’re stuck on which function to use, or aren’t sure of the syntax needed for a particular function, you can use Excel’s Insert Function feature. Place your cursor in the cell you want to enter a function in and click the Insert Function command, found on the Formulas tab. Alternatively, you can press Shift+F3 to call up the Insert Function dialog box, shown...

Read More!

Tutorial Excel : Leveraging Excel Functions

Functions are essentially canned formulas that Excel provides as a way to accomplish common tasks. Some Excel functions perform simple calculations, saving you from having to enter the formula yourself. Other functions perform complex actions that would be impossible to perform with simple formulas alone. As you go through the chapters in this book, you explore many of the...

Read More!

Tutorial Excel : Formula Calculation Modes

By default, Excel is set to recalculate automatically. If you change any of the cells referenced in a particular formula, Excel automatically recalculates that formula so that it returns a correct result based on the changes in its cell references. Also, if the formula that it recalculates is used as a cell reference in other formulas, every formula that is dependent on the...

Read More!

Tutorial Excel : Using External Cell References

You may find that you have data in one workbook that you want to reference in a formula within another workbook. In such a situation, you can create a link between the workbooks using an external cell reference. An external cell reference is nothing more than a cell reference that resides in an outside workbook. The benefit of using an external cell reference is that when...

Read More!

Tips Excel : Relative versus Absolute Cell References

Imagine that you go to C1 and enter the formula =A1+B1. Your human eyes will define that as the value in A1 added to the value in B1. However, Excel, doesn’t see it that way. Because you entered the formula in cell C1, Excel reads the formula like this:Take the value in the cell two spaces to the left and add it to the value in the cell onespace to the left. If you copy the...

Read More!

Tutorial Excel : Using Formula Operators

As mentioned earlier in this chapter, you can use symbols known as operators to define the operation your formula will accomplish. Some of these operators are mathematical operators that simply add, subtract, and multiply. Other operators allow you to perform more complex actions such as comparing values. For example, you can determine whether an employee has met his or her...

Read More!