Ready to use Excel Formulas: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. , screenshot illustrates how to do this by wrapping the division operation in

Excel’s IF function.

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

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 Also Ready to use Excel Formula: Applying a Percent Increase or Decrease to Values

Read Also Ready to use Excel formula: Calculating a Running Total

Ready to use Excel Formula: 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.

The screenshot below 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

% decrease and increase using simple formula

How it works

To increase a number by a percentage amount, multiply the original amount by 1+ the percent of the increase. In the example in Screenshot, 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 the increase. In the example in Screenshot, 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.

Read also Ready to use Excel formula: Calculating a Running Total

Read also Ready To Use Excel Formula: how to Calculate percentage distribution

Ready to use Excel formula: 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. Screen shot 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)

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.

You may also be interested in Ready To Use Excel Formulas :how to Calculate percentage distributio

Ready To Use Excel Formulas :how to Calculate percentage 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 Figure 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.

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.

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. Figure 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)

% distribution

you may also read Ready To Use Excel Formulas : Calculating a Percent Distribution

Ready To Use Excel Formulas : 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 the screen shot below, 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.

 

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.

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. The screen shot below 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)

% distribution 

Read Also Ready To Use Excel Formulas :how to Calculate Percent Variance with Negative Values

Ready To Use Excel Formulas : how to calculate percentage of goal.

Formulas are the true engines of Excel. Employing various Excel functions, formulas

enable Excel analysts to create aggregated reporting, complex calculation engines,

clever dashboard models, and much more. Indeed, Excel analysts become more

productive as their proficiency with Excel functions and formulas improves.

But building proficiency with Excel functions and formulas takes time. Given that

Excel contains more than 400 functions, you could spend months, even years, learning

which functions are best for certain tasks and which functions can be combined with

others functions.

Unfortunately, many analysts don’t have the luxury of taking a few weeks’ time-out to

learn all they need to know about Excel functions and formulas. The scenarios and

issues they face require solutions now.

Formula 1: 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).

Calculating the percentage of a Goal

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.

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.

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 Figure 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

Calculating the Percentage of a 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.

Read also Learn Microsoft Excel lesson #41