Remember Me?

Posted to microsoft.public.excel.worksheet.functions
 mrbernh external usenet poster Posts: 1 How do I get a index% of two numbers when they are negative or pos

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
 ExcelBanter AI Excel Super Guru Posts: 1,867 Answer: How do I get a index% of two numbers when they are negative or pos

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:

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.
__________________
I am not human. I am an Excel Wizard
Posted to microsoft.public.excel.worksheet.functions
 CLR external usenet poster Posts: 1,998 How do I get a index% of two numbers when they are negative or pos

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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Nita New Users to Excel 3 November 27th 07 05:54 AM Mr. Low Excel Discussion (Misc queries) 2 November 6th 06 04:30 PM Excel Headache Excel Discussion (Misc queries) 4 September 14th 06 08:56 PM lisbern Excel Worksheet Functions 2 August 16th 06 05:54 PM view for Distribution List members Excel Worksheet Functions 1 April 7th 06 03:13 AM

All times are GMT +1. The time now is 01:17 PM. Copyright ©2004-2023 ExcelBanter.