Ittechsavvy: expert excel

Showing posts with label expert excel. Show all posts

Saturday, 13 May 2017

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

…..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.

Thursday, 11 May 2017

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.

Wednesday, 3 May 2017

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

Monday, 1 May 2017

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

Wednesday, 26 April 2017

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

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

Wednesday, 19 April 2017

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

In the previous post, “how to Calculate 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 awkward.

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)

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

Using the ABS function

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.

Saturday, 15 April 2017

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

The picture below demonstrates how to translate this into a formula. The formula in E4

calculates the percent variance between current year sales and previous year sales.


calculating the percent variance between current yr sales and previous yr sales

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.

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

You may also be interested in Ready To Use Excel Formulas: how to calculate a percentage of goal.

Tuesday, 11 April 2017

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