You can calculate a percent variance by subtracting the original number from the new number, then dividing that result by the original. For example, if the baseline number is 100, and the new number is 110:
This formula can be used to calculate things like variance between this year and last year, variance between a budgeted and actual values, and so on.
The concept of variance requires a baseline value and a "new" value. Once you calculate the difference between the two numbers, you only need to divide by the original value.
In the example, we are calculating a variance from forecast, so we need subtract the fForecast in column C from the Actual in column D, then divide that by the original number in Column C. The result in column E must be formatted using the Percentage number format.
In cell E5, the formula is:
In the example, the difference between actual and forecast is 34,000, so the formula reduces to:
When you format this result using the Percentage number format, Excel will automatically display the decimal result as a percentage, there is no need to multiply by 100.
If have a negative value for the original number, the above formula won't work and can be adjusted by adding the ABS function:
ABS makes negative numbers positive and in this case ensures the original value is positive when the variance is calculated. However, the results you get when with negative values can be misleading, as explained by Jon Acampora in his detailed article on the topic.
When working manually, percentage results are calculated by dividing one number by another (for example, a part by a whole) to get a decimal value, then multiplying by 100 to express as a percentage. Excel can display decimal or fractional values...
Following order of operations, Excel first calculates the difference between the values (the actual change in sales) then divides that result by the original, or "old" value to get the decimal value -0.3435: = ( D6 - C6 ) / C6 = ( 49500 - 75400 ) /...
the YEARFRAC function takes two dates, a start date and and an end date, and calculates the time between them in fractional years. In this case, we are using the end date as-is, but generating a start date that equals the first day of the same year...
If you need to calculate a profit margin, you can easily do so with a simple formula that uses the sale price and the cost. In the example shown, the first formula looks like this: = ( B4 - C4 ) / B4 Make sure you use parentheses to control the...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.