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

 

[CUExcel]

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)))

Hi all,

Hoping to get some help with this as well because the calculation does not seem to produce teh expected correct answer.

Let me use a simple example:

If say a budget is $ -100 if the person achieved $ -100 they would be 100 % of budget. Where 100 % is indicated when a target is fully achived. Why a negative budget/target? Think of a startup where it is not supposed to be profitable for three years and maybe in our example it is to lose $ 100 in year 1 if the business plan is achieved.

The following then is true:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Achieved[/TD]
[TD]Budget[/TD]
[TD]% of Budget [/TD]
[/TR]
[TR]
[TD]($ -150)[/TD]
[TD]($ 100)[/TD]
[TD] - 50 % [/TD]
[/TR]
[TR]
[TD]$ 0[/TD]
[TD]($ 100)[/TD]
[TD]200 %[/TD]
[/TR]
[TR]
[TD]$ 100[/TD]
[TD]($ 100)[/TD]
[TD]300 %[/TD]
[/TR]
[TR]
[TD]($ -200)[/TD]
[TD]($ 100)[/TD]
[TD]- 100 %
[/TD]
[/TR]
</tbody>[/TABLE]

 

[SMCAUL]

Hi all,

Hoping to get some help with this as well because the calculation does not seem to produce teh expected correct answer.

Let me use a simple example:

If say a budget is $ -100 if the person achieved $ -100 they would be 100 % of budget. Where 100 % is indicated when a target is fully achived. Why a negative budget/target? Think of a startup where it is not supposed to be profitable for three years and maybe in our example it is to lose $ 100 in year 1 if the business plan is achieved.

The following then is true:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Achieved[/TD]
[TD]Budget[/TD]
[TD]% of Budget[/TD]
[/TR]
[TR]
[TD]($ -150)[/TD]
[TD]($ 100)[/TD]
[TD] - 50 %[/TD]
[/TR]
[TR]
[TD]$ 0[/TD]
[TD]($ 100)[/TD]
[TD]200 %[/TD]
[/TR]
[TR]
[TD]$ 100[/TD]
[TD]($ 100)[/TD]
[TD]300 %[/TD]
[/TR]
[TR]
[TD]($ -200)[/TD]
[TD]($ 100)[/TD]
[TD]- 100 %[/TD]
[/TR]
</tbody>[/TABLE]

Did you ever find a solution for this problem? I have the same issue.

 

[joeu2004]

Hi all,

Hoping to get some help with this as well because the calculation does not seem to produce teh expected correct answer.
[....]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Achieved[/TD]
[TD]Budget[/TD]
[TD]% of Budget[/TD]
[/TR]
[TR]
[TD]($ -150)[/TD]
[TD]($ 100)[/TD]
[TD] - 50 %[/TD]
[/TR]
[TR]
[TD]$ 0[/TD]
[TD]($ 100)[/TD]
[TD]200 %[/TD]
[/TR]
[TR]
[TD]$ 100[/TD]
[TD]($ 100)[/TD]
[TD]300 %[/TD]
[/TR]
[TR]
[TD]($ -200)[/TD]
[TD]($ 100)[/TD]
[TD]- 100 %[/TD]
[/TR]
</tbody>[/TABLE]

Did you ever find a solution for this problem? I have the same issue.

First, for future note, I suggest that you start a new thread instead of "responding" to an old thread, especially a 3-year-old thread where the last question was never answered (read: obviously a dead).

Second, I hope you do not have "same" issue, since CUExcel's requirements make no sense to me (even if we ignore nonsensical notation like "($-200)" [sic]).

Consider the following table. Does that meet your needs?

[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: center"]A[/TD]
[TD="width: 64, align: center"]B[/TD]
[TD="width: 64, align: center"]C[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]Actual[/TD]
[TD="width: 64, align: right"]Budget[/TD]
[TD="width: 64, align: right"]%ofBudget[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]-150
[/TD]
[TD="width: 64, align: right"]-100[/TD]
[TD="width: 64, align: right"]-50.00%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-100[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]-100[/TD]
[TD="align: right"]200.00%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]-200[/TD]
[TD="align: right"]-100
[/TD]
[TD="align: right"]-100.00%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]-200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-100.00%[/TD]
[/TR]
</tbody>[/TABLE]

The formula in column C (C2 for example) is:

=IF(B2=0, SIGN(A2), (A2-B2)/ABS(B2))

formatted as Percentage.

Some people would argue that when we cross zero (negative to positive, or positive to negative), the concepts of %over and %under do not apply. They are right, IMHO. Nevertheless, management likes to apply those terms.

If we budget 100 and achieve 200 (100 more than budgeted), most people would agree we achieved 100% over budget. Likewise, if we budget -100 and achieve 0 (100 more than budgeted), we achieved 100% over budget, IMHO, not 200%.

When we budget 0, any gain or loss is an arbitrary %over or %under, since it really is not defined mathematically. I choose 100% with the sign of the difference, regardless of the magnitude.