I am trying to get a formula to calculate the index% of two numbers when they
are either positive or negative and sometimes when one is positive and the
other is negative.
i.e.
Actual Profit-------Budgeted Profit------Index %
($100)--------------($105)
or
Actual Profit-------Budgeted Profit------Index %
$100-----------------$105
or
Actual Profit-------Budgeted Profit------Index %
$100--------------($105)
or
Actual Profit-------Budgeted Profit------Index %
($100)--------------$105

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:

1. In an empty cell, type the equal sign (=) to start the formula.
2. Type the open parenthesis "(" to start the formula.
3. Type the actual profit number, followed by a minus sign (-), followed by the budgeted profit number.
4. Type the close parenthesis ")" to complete the numerator of the formula.
5. Type a forward slash (/) to start the denominator of the formula.
6. Type the ABS function, followed by an open parenthesis "(".
7. Type the budgeted profit number inside the ABS function.
8. Type the close parenthesis ")" to complete the ABS function.
9. Type an asterisk (*) to multiply the numerator and denominator.
10. Type the number 100 to convert the result to a percentage.
11. Press Enter to complete the formula.

For example, if your actual profit is -$100 and your budgeted profit is $105, the formula would be:

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:

The result would be 195.24%, indicating that the actual profit is 195.24% higher than the budgeted profit.

With your numbers in columns A and B, starting in row 2,

Put this in C2 and copy down
=(B2-A2)*-1

Vaya con Dios,
Chuck, CABGx3



"mrbernh" wrote:

I am trying to get a formula to calculate the index% of two numbers when they
are either positive or negative and sometimes when one is positive and the
other is negative.
i.e.
Actual Profit-------Budgeted Profit------Index %
($100)--------------($105)
or
Actual Profit-------Budgeted Profit------Index %
$100-----------------$105
or
Actual Profit-------Budgeted Profit------Index %
$100--------------($105)
or
Actual Profit-------Budgeted Profit------Index %
($100)--------------$105