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 in this chart include a line break and a reference to the
data values.

The secret to this trick is to use the CHAR() function in a formula that makes up your chart labels (see Image 3-10).

Image 3-10: Using the CHAR() function to force a line break between sales rep name
and data value.

How it works

Every character in Excel has an associated ANSI character code. The ANSI character code is a Windows system code set that defines the characters you see on your screen. The ANSI character set consists of 255 characters, numbered from 1 to 255. The uppercase letter A is character number 97. The number 9 is character 57.

Even nonprinting characters have codes. The code for a space is 32. The code for a line break is 10.

You can call up any character in a formula by using the CHAR() function. The example shown in Figure 3-10 calls up the line break character and joins it with the values in cells A3 and C3:

=A3 & CHAR(10) & C3

The cell itself doesn’t show the line break unless you have wrap text applied. But even if you don’t, any chart using this kind of formula will display the data returned by the formula with the line breaks.

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:

1. Measure the character length of the word Mississippi (11 characters).
2. Measure the character length after removing every letter s (6 characters).
3. Subtract the adjusted length from the original length.

You can then accurately conclude that the number of times the letter s appears in the word Mississippi is four.

A real-world use for this technique of counting specific characters is to calculate a word count in Excel. Image 3-8 shows the following formula being used to count the number of words entered in cell B4:

=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1

Image 3-8: Calculating the number of words in a cell.

How it works

This formula essentially follows the steps mentioned earlier in this section. The formula uses the LEN function to first measure the length of the text in cell B4:

LEN(B4)

It then uses the SUBSTITUTE function to remove the spaces from the text:

SUBSTITUTE(B4," ","")

Wrapping that SUBSTITUTE function in a LEN function gives you the length of the text without the spaces. Note that you have to add one to that answer to account for the fact that the last word doesn’t have an associated space.

LEN(SUBSTITUTE(B4," ",""))+1

Subtracting the original length with the adjusted length gives you the word count.

=LEN(B4)-LEN(SUBSTITUTE(B4," ",""))+1

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). The actual result of the formula is the following:

Star'S Coffee
Note how the PROPER function capitalizes the S after the apostrophe. Annoying, to say the least.

However, with a little help from the Excel’s SUBSTITUTE function, you can avoid this annoyance. Image 3-7 shows the fix using the following formula:

=SUBSTITUTE(PROPER(SUBSTITUTE(B4,"'","qzx")),"qzx","'")

Image 3-7: Fixing the apostrophe S issue with the SUBSTITUTE function.

How it works

The formula uses the SUBSTITUTE function, which requires three arguments: the target text; the old text you want replaced; and the new text to use as the replacement. As you look at the full formula, note that it uses two SUBSTITUTE functions. This formula is actually two formulas (one nested in the other). The first formula is the part that reads.

PROPER(SUBSTITUTE(B4,"'","qzx"))

In this part, you use the SUBSTITUTE function to replace the apostrophe (’) with qzx. This may seem like a crazy thing to do, but there is some method here. Essentially, the PROPER function capitalizes any letter coming directly after a symbol. You trick the PROPER function by substituting the apostrophe with a benign set of letters that are unlikely to be strung together in the original text.
The second formula actually wraps the first. This formula substitutes the benign qzx with an apostrophe.

=SUBSTITUTE(PROPER(SUBSTITUTE(B4,"'","qzx")),"qzx","'")

So the entire formula replaces the apostrophe with qzx, performs the PROPER function, and then reverts the qzx back to an apostrophe.

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 LEFT function wouldn’t work because you need the right few characters. The RIGHT function alone won’t work because you need to tell it exactly how many characters to extract from the right of the text string. Any number you give will pull either too many or too few characters from the text. The MID function alone won’t work because you need to tell it exactly where in the text to start extracting. Again, any number you give will pull either too many or too few characters from the text.

The reality is that you often will need to the find specific characters in order to get the appropriate starting position for extraction.

This is where Excel’s FIND function comes in handy. With the FIND function, you can get the position number of a particular character and use that character position in other operations.

In the example shown in Image 3-5, you use the FIND function in conjunction with the MID function to extract the middle numbers from a list of product codes. As you can see from the formula, you find the position of the hyphen and use that position number to feed the MID function.

=MID(B3,FIND("-",B3)+1,2)

Image 3-5: Using the FIND function to extract data based on the position of the hyphen.

How it works

The FIND function has two required arguments. The first argument is the text you want to find. The second argument is the text you want to search. By default, the FIND function returns the position number of the character you are trying to find. If the text you are searching contains more than one of your search characters, the FIND function returns the position number of the first encounter.

For instance, the following formula searches for a hyphen in the text string “PWR-16-Small”. The result will be a number 4, because the first hyphen it encounters is the fourth character in the text string.

=FIND("-","PWR-16-Small")

You can use the FIND function as an argument in a MID function to extract a set number of characters after the position number returned by the FIND function. Entering this formula in a cell will give you the two numbers after the first hyphen found in the text. Note the +1 in the formula. Including +1 ensures that you move over one character to get to the text after the hyphen.

=MID("PWR-16-Small", FIND("-","PWR-16-Small")+1, 2)

Alternative: Finding the second instance of a character 

By default, the FIND function returns the position number of the first instance of the character you are searching for. If you want the position number of the second instance, you can use the optional Start_Num argument. This argument lets you specify the character position in the text string to start the search.

For example, the following formula returns the position number of the second hyphen because you tell the FIND function to start searching at position 5 (after the first hyphen).

=FIND("-","PWR-16-Small", 5)

To use this formula dynamically (that is, without knowing where to start the search) you can nest a FIND function as the Start_Num argument in another FIND function. You can enter this formula into Excel to get the position number of the second hyphen.

=FIND("-","PWR-16-Small", FIND("-","PWR-16-Small")+1)

Figure 3-6 demonstrates a real-world example of this concept. Here, you extract the size attribute from the product code by finding the second instance of the hyphen and using that position number as the starting point in the MID function. The formula shown in cell C3 is as follows:

=MID(B3,FIND("-",B3,FIND("-",B3)+1)+1,10000)

This formula tells Excel to find the position number of the second hyphen, move over one character, and then extract the next 10,000 characters. Of course, there aren’t 10,000 characters, but using a large number like that ensures that everything after the second hyphen is pulled.

Image 3-6: Nesting the FIND function to extract everything after the second hyphen.

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 3-4 demonstrates how using the LEFT, RIGHT, and MID functions can help easily accomplish these tasks.

Image 3-4: Using the LEFT, RIGHT, and MID functions.

How it works

The LEFT function allows you to extract a given number of characters from the left of a given text string. The LEFT function requires two arguments: the text string you are evaluating and the number of characters you need extracted from the left of the text string. In the example, you extract the left five characters from the value in Cell A4.

=LEFT(A4,5)

The RIGHT function allows you to extract a given number of characters from the right of a given text string. The RIGHT function requires two arguments: the text string you are evaluating and the number of characters you need extracted from the right of the text string. In the example, you extract the right eight characters from the value in Cell A9.

=RIGHT(A9,8)

The MID function allows you to extract a given number of characters from the middle of a given text string. The MID function requires three arguments: the text string you are evaluating; the character position in the text string from where to start extracting; and the number of characters you need extracted. In the example, you start at the fourth character in the text string and extract one character.


=MID(A14,4,1)

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 illustrates how you can remove superfluous spaces by using the TRIM function.

Image 3-3: Removing excess spaces from text.

How it works

The TRIM function is relatively straightforward. Simply give it some text and it removes all spaces from the text except for single spaces between words. As with other functions, you can nest the TRIM function in other functions to clean up your text while applying some other manipulation. For instance, the following function trims the text in cell A1 and converts it to uppercase all in one step:

=UPPER(TRIM(A1))

Note : 
The TRIM function was designed to trim only the ASCII space character from text. The ASCII space character has a code value of 32. The Unicode character set, however, has an additional space character called the nonbreaking space character. This character is commonly used in web pages and has the Unicode value of 160.

The TRIM function is designed to handle only CHAR(32) space characters. It cannot, by itself, handle CHAR(160) space characters. To handle this kind of space, you need to use the SUBSTITUTE function to find CHAR(160) space characters and replace them with CHAR(32) space characters so that the TRIM function can fix them. You can accomplish this task all at one time with the following formula:

=TRIM(SUBSTITUTE(A4,CHAR(160),CHAR(32)))

For a detailed look at the SUBSTITUTE function, see Formula 18: Substituting Text Strings.

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 function converts text to title case (the first letter of every word is capitalized).

What Excel lacks is a function to convert text to sentence case (only the first letter of the first word is capitalized). But as you can see in Figure 3-2, you can use the following formula to force text into sentence case:

=UPPER(LEFT(C4,1)) & LOWER(RIGHT(C4,LEN(C4)-1))

Image 3-2: Converting text into uppercase, lowercase, proper case, and sentence
case.

How it works

If you take a look at this formula closely, you can see that it’s made up of two parts that are joined by the ampersand. The first part uses Excel’s LEFT function:

UPPER(LEFT(C4,1))

The LEFT function allows you to extract a given number of characters from the left of a given text string. The LEFT function requires two arguments: the text string you are evaluating and the number of characters you need extracted from the left of the text string. In this example, you extract the left 1 character from the text in cell C4. You then make it uppercase by wrapping it in the UPPER function.
The second part is a bit trickier. Here, you use the Excel RIGHT function:

LOWER(RIGHT(C4,LEN(C4)-1))

Like the LEFT function, the RIGHT function requires two arguments: the text you are evaluating, and the number of characters you need extracted from the right of the text string. In this case, however, you can’t just give the RIGHT function a hard-coded number for the second argument. You have to calculate that number by subtracting 1 from the entire length of the text string. You subtract 1 to account for the first character that is already uppercase thanks to the first part of the formula.

You use the LEN function to get the entire length of the text string. You subtract 1 from that, which gives you the number of characters needed for the RIGHT function. You can finally pass the formula you’ve created so far to the LOWER function to make everything but the first character lowercase.
Joining the two parts together gives results in sentence case:

=UPPER(LEFT(C4,1)) & LOWER(RIGHT(C4,LEN(C4)-1))

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 values with one another. As you can see in Image 3-1, you can join cell values with text of your own. In this example, you join the values in cells B3 and C3, separated by a space (created by entering a space in quotes).

Note : Excel also provides a CONCATENATE function that joins values without the need for the ampersand. In this example, you could enter =CONCATENATE(B3," ",C3). Frankly, it’s better to skip this function and simply use the ampersands. This function is more processing intensive and
requires using more keystrokes.

 

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 but Excel’s CONVERT function.

With this table, you can get a quick view of the conversions from one unit of measure to another. You can see that it takes 48 teaspoons to make a cup, 2.4 cups to make an English pint, and so on.

Image 2-14: Creating a unit-of-measure conversion table.

How it works

The CONVERT function requires three arguments: a number value, the unit you’re converting from, and the unit you’re converting to. For instance, to convert 100 miles into kilometers, you can enter this formula to get the answer 160.93:

=CONVERT(100,"mi", "km")

You can use the following formula to convert 100 gallons into liters. This gives you the result 378.54:

=CONVERT(100,"gal", "l")

Notice the conversion codes for each unit of measure. These codes are very specific and must be entered in exactly the way Excel expects to see them. Entering a CONVERT formula using “gallon” or “GAL” instead of the expected “gal” returns an error.

Luckily, Excel provides a tooltip as you start entering your CONVERT function, letting you pick the correct unit codes from a list.
You can refer to Excel’s Help files on the CONVERT function to get a list of valid unit-of-measure conversion codes.

When you have the codes you are interested in, you can enter them in a matrix-style table like the one you see in Figure 12-14. In the top-leftmost cell in your matrix, enter a formula that points to the appropriate conversion code for the matrix row and matrix column.

Be sure to include the absolute references necessary to lock the references to the conversion codes. For the codes located in the matrix row, lock the column reference. For the codes located in the matrix column, lock the row reference.

=CONVERT(1,$E4,F$3)

At this point, you can simply copy your formula across the entire matrix.

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 have passed. In column H, the COUNTA function is counting all the exams taken by a student. In column I, the COUNTBLANK function is counting only those exams that have not yet been taken.

Image 2-13: A demonstration of counting cells.

How it works

The COUNT function counts only numeric values in a given range. It requires only a single argument in which you pass a range of cells. For example, this formula counts only those cells in range C4:C8 that contain a numeric value:

=COUNT(C4:C8)

The COUNTA function counts any cell that is not blank. You can use this function when you’re counting cells that contain any combination of numbers and text. It requires only a single argument in which you pass a range of cells. For instance, this formula counts all the nonblank cells in range C4:F4:

=COUNTA(C4:F4)

The COUNTBLANK function counts only the blank cells in a given range. It requires only a single argument in which you pass a range of cells. For instance, this formula counts all the blank cells in range C4:F4:

=COUNTBLANK(C4:F4)

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 one significant digit. This would mean displaying the same number as 900,000. Rounding 883,788 to two significant digits would show the number as 880,000.

In essence, you’re deeming that a particular number’s place is significant enough to show. The rest of the number can be replaced with zeros. You might feel as though doing this could introduce problems, but when you’re dealing with large enough numbers, any number below a certain significance is inconsequential.

Image 2-12 demonstrates how you can implement a formula that rounds numbers to a
given number of significant digits.

Image 2-12: Rounding numbers to 1 significant digit.

How it works

You use Excel’s ROUND function to round a given number to a specified number of digits. The ROUND function takes two arguments: the original value and the number of digits to round to.

Entering a negative number as the second argument tells Excel to round based on significant digits to the left of the decimal point. The following formula, for example, returns 9500:

=ROUND(9489,-2)

Changing the significant digits argument to -3 returns a value of 9000.

=ROUND(B14,-3)

This works great, but what if you have numbers on differing scales? That is, what if some of your numbers are millions while others are hundreds of thousands? If you wanted to show them all with 1 significant digit, you would need to build a different ROUND function for each number to account for the differing significant digits argument that you would need for each type of number.

To help solve this issue, you can replace your hard-coded significant digits argument with a formula that calculates what that number should be. Imagine that your number is -2330.45. You can use this formula as the significant digits argument in your ROUND function:

LEN(INT(ABS(-2330.45)))*-1+2

This formula first wraps your number in the ABS function, effectively removing any negative symbol that may exist. It then wraps that result in the INT function, stripping out any decimals that may exist. It then wraps that result in the LEN function to get a measure of how many characters are in the number without any decimals or negation symbols.

In this example, this part of the formula results in the number 4. If you take the number -2330.45 and strip away the decimals and negative symbol, you have four characters left.

This number is then multiplied by -1 to make it a negative number, and then added to the number of significant digits you are looking for. In this example, that calculation looks like this: 4*-1+2 = -2.
Again, this formula will be used as the second argument for your ROUND function. Enter this formula into Excel and round the number to 2300 (2 significant digits):

=ROUND(-2330.45, LEN(INT(ABS(-2330.45)))*-1+2)

You can then replace this formula with cell references that point to the source number and cell that holds the number of desired significant digits. This what you see in Figure 2-12, shown previously.

=ROUND(B5,LEN(INT(ABS(B5)))*-1+$E$3)

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 up to the nearest multiple of significance that you pass to it. This utility comes in handy when you need to override the standard rounding protocol with your own business rules. For instance, you can force Excel to round 123.222 to 124 by using the CEILING  function with a significance of 1.

=CEILING(123.222,1)

So entering a .01 as the significance tells the CEILING function to round to the nearest penny.
If you wanted to round to the nearest nickel, you could use .05 as the significance. For
instance, the following formula returns 123.15:

=CEILING(123.11,.05)

The FLOOR function works the same way except that it forces a rounding down to the nearest significance. The following example function rounds 123.19 down to the nearest nickel, giving 123.15 as the result:

=FLOOR(123.19,.05)

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 2-10: Rounding numbers using formulas.

How it works

Excel’s ROUND function is used to round a given number to a specified number of digits. The ROUND function takes two arguments: the original value and the number of digits to round to.

Entering a 0 as the second argument tells Excel to remove all decimal places and round the integer portion of the number based on the first decimal place. For instance, this formula rounds to 94:

=ROUND(94.45,0)

Entering a 1 as the second argument tells Excel to round to one decimal based on the value of the second decimal place. For example, this formula rounds to 94.5:

=ROUND(94.45,1)

You can also enter a negative number as the second argument, telling Excel to round based on values to the left of the decimal point. The following formula, for example, returns 90:

=ROUND(94.45,-1)

You can force rounding in a particular direction using the ROUNDUP or ROUNDDOWN functions.
This ROUNDDOWN formula rounds 94.45 down to 94:

=ROUNDDOWN(94.45,0)

This ROUNDUP formula rounds 94.45 up to 95:

=ROUNDUP(94.45,0)

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 continuously subtract 2 five times.

• 10-2 = 8 
• 8-2 = 6 
• 6-2 = 4 
• 4-2 = 2 
• 2-2 = 0
  So, 10/2 = 5.

Now if you tried to do this with 10 divided by 0, you would never get anywhere, because 10-0 is 10 all day long. You’d be sitting there subtracting 0 until your calculator dies.

• 10-0 = 10 
• 10-0 = 10 
• 10-0 = 10 
• 10-0 = 10
  …..Infinity

Mathematicians call the result you get when dividing any number by zero “undefined.” Software like Excel simply gives you an error when you try to divide by zero. In Excel, when you divide a number by zero, you get the #DIV/0! error. You can avoid this by telling Excel to skip the calculation if your denominator is a zero. Image 2-9 illustrates how to do this by wrapping the division operation in Excel’s IF function.

=IF(C4=0, 0, D4/C4)

Image 2-9: Using the IF function to avoid a division-by-zero error.

How it works

The IF function requires three arguments: the condition; what to do if the condition is true; and what to do if the condition is false.

The condition argument in this example is the budget in C4 is equal to zero (C4=0). Condition arguments must be structured to return TRUE or FALSE, and that usually means that there is a comparison operation (like an equal sign or greater-than sign).

If the condition argument returns TRUE, the second argument of the IF function is returned to the cell. The second argument is 0, meaning that you simply want a zero displayed if the budget number in cell C4 is a zero.

IF the condition argument is not zero, the third argument takes effect. In the third argument, you tell Excel to perform the division calculation (D4/C4). So this formula basically says that if C4 equals 0, then return a 0, or else return the result of D4/C4.

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 and decrease using a simple formula. In cell E5, you apply a 10 percent price increase to Product A. In Cell E9, you give a 20 percent discount to Customer A.

Image 2-8: Applying a percent increase and decrease using a simple formula.

How it works

To increase a number by a percentage amount, multiply the original amount by 1+ the percent of increase. In the example in Figure 2-8, Product A is getting a 10 percent increase. So you first add 1 to the 10 percent, which gives you 110 percent. You then multiply the original price of 100 by 110 percent. This calculates to the new price of 110.

To decrease a number by a percentage amount, multiply the original amount by 1- the percent of increase. In the example in Figure 2-8, Customer A is getting a 20 percent discount. So you first subtract 20 percent from 1, which gives you 80 percent. You then multiply the original 1,000 cost per service by 80 percent. This calculates to the new rate of 800.

Note the use of parentheses in the formulas. By default, Excel’s order of operations states that multiplication must be done before addition or subtraction. But if you let that happen, you would get an erroneous result. Wrapping the second part of the formula in parentheses ensures that Excel performs the multiplication last.

Ref: For more information on the order of operator precedence.

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 function to add all the units from cell C3 to the current row. The trick to this formula is the absolute reference ($C$3). Placing an absolute reference in the reference for the first value of the year locks that value down. Locking the value down ensures that as the formula is copied down, the SUM function always captures and adds the units from the very first value to the value on the current row.

Ref :  For more information see on absolute and relative cellreferences.

 

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 distribution for each region.

Image 2-5: Calculating a percent distribution of revenue across regions.

How it works

This formula doesn’t have a lot to it. You’re simply using cell references to divide each component value by the total. The one thing to note is that the cell reference to the Total is entered as an absolute reference ($C$9). Using the dollar symbols locks the reference in place, ensuring that the cell reference pointing to Total does not adjust as you copy the formula down.

Note : For more information see on absolute and relative cell references.

Alternative: Percent distribution without a dedicated Total cell

You don’t have to dedicate a separate cell to an actual Total value. You can simply calculate Total on the fly within the percent distribution formula. Image 2-6 demonstrates how you can use the SUM function in place of a cell dedicated to holding a Total. The SUM function adds together any numbers you pass to it. Again, note the use of absolute references in the SUM function. Using absolute references ensures that the SUM range stays locked as you copy the formula down:

=C3/SUM($C$3:$C$6)

Image 2-6: Calculating percent distribution with the SUM function.

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 $10,000. Now imagine that after your first year, you actually made money, earning $12,000. Calculating the percent variance between your actual revenue and budgeted revenue would give you -220%. You can try it on a calculator. 12,000 minus -10,000 divided by -10,000 equals -220%.

How can you say that your percent variance is -220% when you clearly made money? Well, the problem is that when your benchmark value is a negative number, the math inverts the results, causing numbers to look wacky. This is a real problem in the corporate world where budgets can often be negative values.

The fix is to leverage the ABS function to negate the negative benchmark value:

=(C4-B4)/ABS(B4)

Image 2-4 uses this formula in cell E4, illustrating the different results you get when using the standard percent variance formula and the improved percent variance formula.

Image 2-4: Using the ABS function gives you an accurate percent variance when
dealing with negative values.

How it works

Excel’s ABS function returns the absolute value for any number you pass to it. Entering =ABS(-100) into cell A1 would return 100. The ABS function essentially makes any number a non-negative number. Using ABS in this formula negates the effect of the negative benchmark (the negative 10,000 budget in the example) and returns the correct percent variance.

Note : You can safely use this formula for all your percent variance needs; it
works with any combination of positive and negative numbers.

 

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 the percentage difference between the benchmark number (120) and the new number (150). You calculate the percent variance by subtracting the benchmark number from the new number and then dividing that result by the benchmark number. In this example, the calculation looks like this:
(150-120)/120 = 25%. The Percent variance tells you that you sold 25 percent more widgets than yesterday.

Image 2-3 demonstrates how to translate this into a formula. The formula in E4 calculates the percent variance between current year sales and previous year sales.

=(D4-C4)/C4

Image 2-3. Calculate Percent Variance

How it works

The one thing to note about this formula is the use of parentheses. By default, Excel’s order of operations states that division must be done before subtraction. But if you let that happen, you would get an erroneous result. Wrapping the first part of the formula in parentheses ensures that Excel performs the subtraction before the division.

You can simply enter the formula one time in the first row (cell E4 in this case) and
then copy that formula down to every other row in your table.

Ref : For more information on the order of operator precedence see article Tutorial Excel : Using Formula Operators.

Alternative: Simplified percent variance calculation

An alternative formula for calculating percent variance is to simply divide the current year sales by the previous year sales and then subtract 1. Because Excel performs division operations before subtraction, you don’t have to use parentheses with this alternative formula.

=D4/C4-1

Read More!

Formula Excel : Calculating Percent of Goal

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

For sample all formula you can download here.

 Note : Percent of Goal can also be referred to as percent of budget or percent
of forecast; it all means the same thing.

In Image 2-1, you see a list of regions with a column for goals and a column for
actuals. Note that the formula in cell E5 simply divides the value in the Actual column
by the value in the Goal column.

=D5/C5

Image 2-1: Calculating the percent of goal.

How it works

There isn’t much to this formula. You’re simply using cell references to divide one value by another. You just enter the formula one time in the first row (cell E5 in this case) and then copy that formula down to every other row in your table. Alternative: Using a common goal

If you need to compare actuals to a common goal, you can set up a model like the one shown in Image 2-2. In this model, each region does not have its own goal. Instead, you’re comparing the values in the Actual column to a single goal found in cell B3.

=C6/$B$3

Image 2-2: Calculating the percent of goal using a common goal.

Note that the cell reference to common goal is entered as an absolute reference ($B$3). Using the dollar symbols locks the reference to the goal in place, ensuring that the cell reference pointing to your common goal does not adjust as you copy the formula down.

For more information on absolute and relative cell references see article about Tips Excel : Relative versus Absolute Cell References.

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 formula:

=SUM(A1:A25)*(1+B1)

Now imagine that you gave your ranges descriptive names, calling cells A1:25 Sale_Items, and calling cell B1 Tax_Percent. You could then calculate the total sale amount with tax by using this formula:

=SUM(Sales_Items)*(1+Tax_Percent)

Immediately, you can see how much easier it is to understand what is going on in the formula. The formula is easier not only to read but also to explain to others who aren’t familiar with the workbook.
Another benefit to naming these ranges is that creating new formulas with these named ranges becomes easier because you can simply use the easily remembered descriptive name instead of trying to remember that line-item sales live in cells A1:A25.

Creating a named range

Follow these steps to create a named range:

1. Select the cell or range of cells you want to name.
2. Choose Define Name from the Formulas tab. This activates the New Name dialog box, shown in Figure 1-7.
3. In the Name input box, enter a friendly, descriptive name for your range.
4. In the Scope drop-down box, select whether you want your named range to be available for use throughout the workbook or just on a specific sheet.
5. Press the OK button to create your named range.

Image 1-7. Name Range Dioalog Box

Keep these rules and best practices in mind when choosing a name for your range:

• You cannot use spaces in range names. Use an underscore to emulate a space instead (for example, Sales_Items).
• Range names must begin with a letter or an underscore.
• Range names cannot be the same as cell addresses. For instance, you cannot name your range Q1 because Excel already has a cell Q1. 
• You can use any single letter as a range name except for R and C. These are reserved in Excel for the R1C1 reference style.
• You cannot use operator symbols (+, –, *, /, <, >, &) in range names. The only symbols valid in range names are the period (.), question mark (?), underscore (_), and backslash (\) symbols, as long as they are not used as the first character of the name.
• Avoid using names that Excel uses internally, for example, Print_Area. Although Excel allows this name, using it can cause name conflict errors in the workbook. Other names to avoid are Auto_Activate, Auto_Close, Auto_Deactivate, Auto_Open, Consolidate_Area, Criteria, Data_Form, Database, Extract, FilterDatabase, Print_Titles, Recorder, and Sheet_Title.
• The maximum length for a range name is 255 characters. That being said, you should avoid very long range names in general. Remember that the purpose of a range name is to provide a meaningful, easy-to-remember name that you can easily type into a formula.

Working with the Name Box

The Name Box, found to the left of the Formula Bar, offers a couple of handy features for working with named ranges. You can click the drop-down selector in the Name Box to see all the named ranges in your workbook (see Image 1-8). Clicking any of the named ranges in the list automatically selects that range.

Image 1-8: Use the Name Box to view and navigate to any named range within the
workbook.

The Name Box also serves as a faster way to create a named range. To create a named range with the Name Box, first select the cell or range you want to name. Next, enter a valid name directly into the Name Box. Press the Enter key to create the name.

Tips : The Name Box is resizable. If you have a name that is too long for the Name Box, simply move your mouse cursor over the right edge of the Name Box until it turns into a horizontal arrow. When your cursor becomes a horizontal arrow, click and drag to widen the Name Box.

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 error value means that the formula is attempting to divide a value by zero. There is mathematically no way to divide a number by zero. You will also see this error if the formula is trying to divide a value by an empty cell.
• #N/A: This error value means that the formula cannot return a legitimate result. You would typically see this error when you use an inappropriate argument in a function. You will also see this error when a lookup function does not return a match.
• #NAME?: This error value means Excel doesn't recognize a name you used in a formula as a valid object. This error could be a result of a misspelled function, a misspelled sheet name, a mistyped cell reference, or some other syntax error.
• #NULL!: This error value means the formula uses an intersection of two ranges that don’t intersect.
• #NUM!: This error value means there is a problem with a number in your formula; typically an invalid argument in a math or trig function. For example, you entered a negative number where a positive number was expected.
• #REF!: This error value means that your formula contains an invalid cell reference. This is typically caused by deleting a row or column to which the formula refers. This could also mean that the formula uses a cell reference that doesn’t exist (A2000000, for instance).
• #VALUE: This error value means that your formula uses the wrong data type for the operation it’s trying to do. For example, this formula will return a #VALUE error (=100+”dog”).

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 in Image 1-5. The idea here is to find the function you need and double-click it.

Image 1-5: The Insert Function dialog box.

If you’re not sure which function you need, you can use the search field at the top of the dialog box to find the most appropriate function based on a keyword. Simply enter one or more keywords and click the Go button. The list of functions will change to display those that best match your search criteria. For example, entering the search term “loan payment” results in functions that perform loan calculations.

If you need to use a function that you’ve recently implemented, you can skip the search feature and simply select “Most Recently Used” from the category drop-down box. The list of functions changes to display those you’ve recently utilized.

You can also use the category drop-down box to select a category of functions. For instance, selecting “Statistical” from the category drop-down box displays all the statistical functions.

When you find the function that you want to use, double-click it to activate the Function Arguments dialog box (see Image 1-6). This dialog box serves as a kind of wizard, guiding you through the arguments needed for the selected function.

Image 1-6: The Function Arguments dialog box guides you through creating your
Excel functions.

For each required argument, enter an appropriate value or cell reference in the respective input boxes. Note that the required arguments are always listed first and shown in bold type. Do the same thing for any optional argument that you want to utilize.
Click the OK button to apply your newly configured function to your target cell.

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 most useful Excel functions. For now, you start with a basic understanding of the role Excel functions play in your formulas.

Why to use Excel functions

One of the key benefits of using Excel functions is that they help simplify your formulas. For example, if you wanted to get the average of the values in cell A1, A2, and A3, you could enter this formula:

=(A1+A2+A3)/3

This particular formula isn’t too bad, but what if you had to get the average of 100 values? How cumbersome would that formula be to create and manage? Luckily, Excel has an AVERAGE function. With the AVERAGE function, you can simply enter this formula:

=AVERAGE(A1:A3)

If you had to get the average of 100 values, you could simply expand the range:

=AVERAGE(A1:A100)

Another key benefit of using functions is that they help you accomplish tasks that would be impossible with standard formulas. For instance, imagine that you wanted a couple of formulas that would automatically return the largest and smallest numbers in a range of cells. Sure, if your range was small enough, you could eyeball the largest and smallest numbers. But that’s hardly automated.

There is no nonfunction-driven formula you could possibly enter that would automatically return the largest or smallest number in a range. Excel’s MAX and MIN functions, however, make short work of this task. The MAX function returns the largest number, whereas the MIN function returns the smallest.

=MAX(A1:A100)
=MIN(A1:A100)

Functions can also help save time by helping you automate tasks that would take you hours to accomplish manually. For example, say that you needed to extract the first 10 characters of a customer number. How long do you think it would take you to go through a table of 1,000 records and get a list of customer numbers that contains only the first 10 characters?
The LEFT function can help here by pulling out the left 10 characters:

=LEFT(A1,10)

You can simply enter this formula for the first row of your table and then copy it down to as many rows you need.

Understanding function arguments

Most of the functions found in Excel require some input or information in order to calculate correctly. For example, to use the AVERAGE function, you need to give it a range of numbers to average.

=AVERAGE(A1:A100)

Any input you give to a function is called an argument.
The basic construct of a function is:

Function_Name(argument1, argument2,…)

To use a function, you enter its name, open parenthesis, the needed arguments, and then the close parenthesis. The number of arguments needed varies from function to function.

Using functions with no arguments

Some functions, such as the NOW() function, don’t require any arguments. To get the current date and time, you can simply enter a formula like this:

=NOW()

Note that even though no arguments are required, you still need to include the open and close parentheses.

Using functions with one or more required arguments

Some functions require one or more arguments. The LARGE function, for instance, returns the nth largest number in a range of cells. This function requires two arguments: a cell reference to a range of numeric values and a rank number. To get the third largest value in range A1 through A100, you can enter:

=LARGE(A1:A100,3)

Note that each argument is separated by a comma. This is true regardless of how many arguments you enter. Each argument must be separated by a comma.

Using functions with both required and optional arguments

Many Excel functions, such as the NETWORKDAYS function, allow for optional arguments in addition to the required arguments. The NETWORKDAYS function returns the number of workdays (days excluding weekends) between a given start date and end data.

To use the NETWORKDAYS function, you need to provide, at minimum, the start and
end dates. These are the required arguments.

The following formula gives you the answer 260, meaning that there are 260 workdays
between January 1, 2014, and December 31, 2014:

=NETWORKDAYS("1/1/2014", "12/31/2014")

The NETWORKDAYS function also allows for an optional argument that lets you pass a range containing a list of holiday dates. The function treats each date in the optional range as a nonworkday, effectively returning a different result (255 workdays between January 1, 2014, and December 31, 2014, taking into account holiday dates).

=NETWORKDAYS("1/1/2014", "12/31/2014", A1:A5)

Don’t be too concerned with completely understanding the NETWORKDAYS function. The take-away here is that when a function has required and optional arguments, you can elect to use the function with just the required arguments, or you can take advantage of the function’s additional utility by providing the optional arguments.

Finding out which arguments are needed for a given function

An easy way to discover the arguments needed for a given function is to begin typing that function into a cell. Click a cell, enter the equal sign, enter the function name, and then enter an open parenthesis. Recognizing that you are entering a function, Excel activates a tooltip (see Image 1-4) that shows you all the arguments for the function. Any argument that is shown in brackets ([ ]) is an optional argument. All others shown without the brackets are required arguments.

Image 1-4: The function tooltip is a handy way to find out the required and optional arguments for a function.

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 newly recalculated formula is also recalculated.

Calculation

You can imagine that as your spreadsheet grows and gets populated with interweaving formulas, Excel will be constantly recalculating. You may even find that when working with worksheets that contain many complex formulas, Excel slows dramatically as it tries to keep up with all the recalculating it needs to do.

In these cases, you can choose to set Excel’s calculation mode to Manual. You can do this by clicking the Formulas tab in the Excel Ribbon and selecting Calculation Options⇒Manual.

While working in Manual calculation mode, none of your formulas will recalculate until you trigger the calculation yourself. You have several ways to trigger a recalculation:

• Click the Calculate Now command on the Formulas tab to recalculate all formulas in the entire workbook. 
• Click the Calculate Sheet command on the Formulas tab to recalculate only the formulas on the currently active sheet.
• Click the Calculate link on the status bar to recalculate the entire workbook.
• Press F9 to recalculate all formulas in the entire workbook.
• Press Shift+F9 to recalculate only the formulas on the currently active sheet.

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 the data in the outside workbook changes, Excel automatically updates the value returned by the external cell reference.

Creating an external cell reference is relatively easy. Open both workbooks (the workbook that you’re currently working in and the outside workbook). While entering a formula in the workbook you’re currently working in, click the cell that you want to reference in the outside workbook.

As you can see in Image 1-3, you’ll immediately be able to tell that the cell reference is an external reference due to the full file path and sheet name prefixing the cell reference.

Image 1-3: An example of an external cell reference.

All external cell references have the same component parts, as follows:
      'File Path[Workbook Name]Sheet Name'!Cell Reference
Here’s a breakdown of these parts:

• File Path: This part of the cell reference points to the drive and directory in which the workbook is located.
• Workbook Name: This part of the cell reference points to the name of the workbook. This part is always enclosed in brackets ([ ]) and always includes the file extension (.xlsx, .xls, .xslm, and so on).
• Sheet Name: This part of the cell reference points to the name of the sheet in which the referenced cell resides.
• Cell Reference: This part of the cell reference points to the actual cell that is being referenced.

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 one
space to the left.

If you copy the formula =A1+B1 from cell C1 and paste it into cell D1, the formula in D1 will seem different to you. You will see =B1+C1. But to Excel, the formula is exactly the same: Take the value in the cell two spaces to the left and add it to the value in the cell one space to the left.

By default, Excel considers every cell reference used in a formula as a relative reference. That is, it takes no heed of actual column row coordinates. Instead, it evaluates the cell references in terms of where they are relative to the cell the formula resides in.

This behavior is by design and works in situations in which you need the cell references to be adjusted when you copy the formula and paste it to other cells. For instance, the formula shown in cell C1 (see Image 1-2) was copied and pasted down to the rows below. Note how Excel helps by automatically adjusting the cell references to match each row.

Image 1-2: Relative references come in handy when you need to apply the same
operation to values in different rows.

Note :When you copy and paste a formula, Excel automatically adjusts the cell references. However, if you cut and paste a formula, Excel assumes that you want to keep the same cell references and does not adjust them.

If you want to ensure that Excel does not adjust cell references when a formula is copied, you can lock the references down by turning them into absolute references. You turn them into absolute references by adding a dollar symbol ($) before the column and row reference. For instance, you can enter =$A$1+$B$1 to add the value in A1 to the value of B1.

By adding the dollar symbol to any cell reference and making that reference absolute, you can copy the formula anywhere else on the spreadsheet, and the formula will always point to A1 and B1.

Excel gives you the flexibility to make any part of your cell reference absolute. That is, you can specify that only the column part of your cell reference should be locked but the row part can adjust. Alternatively, you can specify that only the row part of your cell reference should be locked but the column part can adjust. These different types of absolute references are commonly called Absolute, Row Absolute, and Column Absolute, and here’s how they work:

• Absolute: When the formula is copied, the cell reference does not adjust at all. Example: $A$1
• Row Absolute: When the formula is copied, the column part adjusts but the row part stays locked. Example: A$1
• Column Absolute: When the formula is copied, the column part stays locked but the row part adjusts. Example: $A1

Note : Instead of manually entering the dollar symbols, you can easily toggle between the possible reference styles by highlighting the cell reference in your formula and pressing the F4 key.

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 quota by using a comparison operator to see if actual sales are greater than or equal to a predetermined quota.
Table 1-1 lists the operators you can use in your Excel formulas.

Table 1-1: Operators for Excel Formulas

Operator	What It Does
+	The plus symbol adds two or more numeric values.
-	The hyphen symbol subtracts two or more numeric values.
/	The forward slash symbol divides two or more numeric values.
*	The asterisk symbol divides two or more numeric values.
%	The percent symbol indicates a numeric percent. Entering a percent sign after a whole number divides the number by 100 and formats the cell as a percentage.
&	The ampersand symbol is used to join or concatenate two or more textual values.
^	The carat symbol is used as an exponentiation operator.
=	The equal symbol is used to evaluate whether one value is equal to another value
>	The greater-than symbol is used to evaluate whether one value is greater than another value.
<	The less-than symbol is used to evaluate whether one value is less than another value.
>=	The greater-than symbol used in conjunction with the equal symbol evaluates whether one value is greater than or equal to another value.
<=	The less-than symbol used in conjunction with the equal symbol evaluates whether one value is less than or equal to another value.
<>	The less-than symbol used in conjunction with the greater-than symbol evaluates whether one value is not equal to another value.

Understanding the order of operator precedence

It’s important to understand that when you create a formula with several operators, Excel evaluates and performs the calculation in a specific order. For instance, Excel always performs multiplication before addition. This order is called the order of operator precedence. You can force Excel to override the built-in operator precedence by using parentheses to specify which operation to evaluate first.

Consider this basic example. The correct answer to (2+3)*4 is 20. However, if you leave off the parentheses, as in 2+3*4, Excel performs the calculation like this: 3*4 = 12 + 2 = 14. Excel’s default order of operator precedence mandates that Excel perform multiplication before addition. Entering 2+3*4 gives you the wrong answer. Because Excel evaluates and performs all calculations in parentheses first, placing 2+3 inside parentheses ensures the correct answer.
The order of operations for Excel is as follows:

• Evaluate items in parentheses.
• Evaluate ranges (:).
• Evaluate intersections (spaces).
• Evaluate unions (,).
• Perform negation (-).
• Convert percentages (%).
• Perform exponentiation (^).
• Perform multiplication (*) and division (/), which are of equal precedence.
• Perform addition (+) and subtraction (-), which are of equal precedence.
• Evaluate text operators (&).
• Perform comparisons (=, <>, <=, >=).

Operations that are equal in precedence are performed left to right.

Here is another widely demonstrated example. If you enter 10^2, which represents the exponent 10 to the 2nd power as a formula, Excel returns 100 as the answer. If you enter -10^2, you would expect -100 to be the result. Instead, Excel returns 100 yet again. The reason is that Excel performs negation before exponentiation, meaning that Excel is converting 10 to -10 before the exponentiation, effectively calculating -10*- 10, which indeed equals 100. Using parentheses in the formula -(10^2) ensures that Excel calculates the exponent before negating the answer, giving you -100.

Remembering the order of operations and using parentheses where appropriate will ensure that you avoid miscalculating your data.

Using nested parentheses

It’s a best practice to use parentheses whenever you can in formulas, even if the use of parentheses seem to be superfluous. The liberal use of parentheses can not only help you avoid calculation errors but also help you better understand what the formula is doing.

You can even nest parentheses in formulas. Nesting means putting parentheses inside of other parentheses. When a formula contains nested parentheses, Excel evaluates the most deeply nested operations first and works its way out. The following formula uses nested parentheses:

=((A1*B1)+(C1*D1))*E1

This formula has three sets of parentheses. Excel will evaluate the two nested sets of parentheses first, then will add those two results together. The added result will then be multiplied by the value in E1.

Note:
Every open parenthesis must have a matching close parenthesis. You can imagine that when you start adding lots of parentheses to your formula, determining which open parenthesis has a matching close parenthesis can get difficult. For its part, Excel offers some help by color coding the parentheses while you’re in Edit mode. Matching open and close parentheses will have the same color.

Read More!