To calculate the index percentage of two numbers, whether they are positive or negative, you can use the following formula:
Index % = (Actual Profit - Budgeted Profit) / ABS(Budgeted Profit) * 100
Here's how to use the formula in Excel:
- In an empty cell, type the equal sign (=) to start the formula.
- Type the open parenthesis "(" to start the formula.
- Type the actual profit number, followed by a minus sign (-), followed by the budgeted profit number.
- Type the close parenthesis ")" to complete the numerator of the formula.
- Type a forward slash (/) to start the denominator of the formula.
- Type the ABS function, followed by an open parenthesis "(".
- Type the budgeted profit number inside the ABS function.
- Type the close parenthesis ")" to complete the ABS function.
- Type an asterisk (*) to multiply the numerator and denominator.
- Type the number 100 to convert the result to a percentage.
- Press Enter to complete the formula.
For example, if your actual profit is -$100 and your budgeted profit is $105, the formula would be:
Code:
- =((-100)-105)/ABS(105)*100
The result would be
-195.24%, indicating that the actual profit is 195.24% lower than the budgeted profit. If your actual profit was $100 and your budgeted profit was -$105, the formula would be:
Code:
- =(100-(-105))/ABS(-105)*100
The result would be
195.24%, indicating that the actual profit is 195.24% higher than the budgeted profit.