Negative % Increase Calculation

[hennanra]

Hello,

I need to calculate the % of actual figures Vs target. The way I am doing this is:

= Actual / Target

So in simple terms if Actual is 150, Target is 100 then the 150% is the result of the formula.

How do I make a generic formula that takes into account negatives. For example:

Actual: -50
Target - 100

In this instance the result is 50% if I use the formulae I specified above whereas in reality it should also be 150%. I need a single formula that can achieve this for all instances. Also sometimes actual will be below target and sometimes a mix of positive and negatives.

Many thanks,
Raheel

 

[Sektor]

= ABS(Actual) / ABS(Target)

 

[hennanra]

Hi,

The problem with the formula you mentioned is that this would indicate a 50% of target when in fact it is more like 150% because you are 50% over the target. The result using formula above is 50% not 150%.

The answer to this seems simple but it isn't. Take another example:

Actual: -16%
Target: - 12%

In this scenario the formula you mentioned would incorrectly state that actual is 33% higher than target as absolute renders both values positive.

Appreciate any more suggestions...

Kind Regards,
Raheel

 

[jasonb75]

Actual: -50
Target - 100

In this instance the result is 50% if I use the formulae I specified above whereas in reality it should also be 150%.

How did you get a result of 150%?

 

[hennanra]

Actual Target % of Tgt Calc Expected:
150 100 150% 150%
-100 -150 67% 150%
-100 -50 200% -100%
-16 -12 133% -33%
-12 -16 75% 133%

The above might help some more to illustrate my expected result. The % of Tgt Calc is simply Actual / Tgt. As you can see in different scenarios - my expected result is very different from the result actually achieved. The Abs function that has been recommended doesn't solve this in all scenarios to achieve the expected result.

Hope this helps. Formula is simple A2/B2 in cell C2 and copied down. Expected are hardcoded values based on what I expect. I am looking for a single formula that handles all scenarios.

Raheel

 

[hennanra]

Jason, I realise mathematically what I am saying is not correct - but thing about comparing a % difference of any KPI.

For example, let's say your loss in a given year was -50Mn and your target was -100Mn. There is a 50Mn increase over the target loss figure. In this instance we could say you had 150% of target, otherwise how else would you represent actual as a % of target? If we said 50% that is wrong because your actual has exceeded the target.

Raheel

 

[jasonb75]

You haven't increased anything, you've reduced your loss by 50%, a 150% result would indicate that your loss was -150Mn against a forecast of 100Mn.

 

[hennanra]

Ok, but as far as the Clients financial scorecards - they are using a Red-Amber-Green system. This means that the expected behaviour is as I indicated which wouldn't be displayed on the scorecard itself but drive a Red Amber Green status - i.e. in this case it would be green because the Actual is above the Target so the % of Target is used as a conduit to achieve the desired RAG.

I agree that if we were displaying the % of Target - I would apply your logic.

 

[jasonb75]

Need some more expected results to cover all possibilities.

<TABLE style="WIDTH: 141pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=187><COLGROUP><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 37pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2747302 class=xl65 height=20 width=49>Actual</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Target</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=74>Expected</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>-100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>-50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD></TR></TBODY></TABLE>

 

[jasonb75]

Only tested with the samples provided so far, must be array confirmed with shift ctrl enter.

=IF(A2>B2,MAX(ABS(A2:B2))/MIN(ABS(A2:B2)),(MIN(ABS(A2:B2))-MAX(ABS(A2:B2)))/MIN(ABS(A2:B2)))