Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:
  1. =((-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:
  1. =(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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel, change column of negative numbers to positive numbers? Nita New Users to Excel 3 November 27th 07 05:54 AM
Excel 2002 : Convert Positive Numbers to Negative Numbers ? Mr. Low Excel Discussion (Misc queries) 2 November 6th 06 04:30 PM
Set negative numbers to zero. Do not calculate with negative valu Excel Headache Excel Discussion (Misc queries) 4 September 14th 06 08:56 PM
change 2000 cells (negative numbers) into positive numbers lisbern Excel Worksheet Functions 2 August 16th 06 05:54 PM
Excel Formula - Add column of numbers but ignore negative numbers view for Distribution List members Excel Worksheet Functions 1 April 7th 06 03:13 AM


All times are GMT +1. The time now is 12:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"