Calculation & Negative Help

[Jack Rabbitt]

Hi All,

Please can someone check my maths below & calculations as it doesnt look
right!

Im looking in particular at the -100.00

The idea of this little table, shows how much debit & credit you have had,
and against a target, with a variance %

it should be that a negative number is bad, positive good.

In the example shown, there is an allowed / target deficate of -350 between
the deb & credit.
the actual shows that debit was not as much as thought, being good, and cred
being 200 better.

The TOT dff row then works out differences, I would have expected
the -100.00 figure to have been + 200% ? am i right or wrong?

Thanks

Actual Target Difference % Diff
DEB 2050 2200 150 6.82
CRED 2050 1850 200 10.81

TOT Diff 0 -350 350 -100.00

 

[Joe User]

Please can someone check my maths below & calculations

That would be a lot easier to do if you had posted your formulas.

Actual Target Difference % Diff
DEB 2050 2200 150 6.82
CRED 2050 1850 200 10.81
TOT Diff 0 -350 350 -100.00

I assume that the data and formulas are in B2:E4 as follows:
B C D E
2 2050 2200 =C2-B2 =100*D2/C2
3 2050 1850 =B3-C3 =100*D3/C3
4 =B3-B2 =C3-C2 =B4-C4 =100*D4/C4

(Forgive me if the columns do not align in your view.)

Note the difference in formulas in D2 (C2-B2) and D34 (B3-C3;B4-C4). That
seems wrong. Arguably, you can choose either; but I think you should be
consistent. (I would opt for the formula paradigm in D34.)

Style note: I would format E2:E4 as Percentage and dispense with "100*". I
think that minimizes mistakes in formulas that might depend on E2:E4 later.
I will assume this change below.

The TOT dff row then works out differences,
I would have expected the -100.00 figure to
have been + 200% ? am i right or wrong?

I would expect +100%. But there is right or wrong. There two different
opinions.

The mathematical purist might argue that -100% is correct for the following
reason. Given the percentage difference (D%) from the target (T), T + T*D%
should equal the actual (A). Substituting: (-350) + -350*(-100%) = 0

But in practical terms, I think +100% is correct for the following reason.
We usually think of movement to the right (increase) as percentage gain
(positive), and movement to the left (decrease) as percentage loss
(negative).

Thus, the formula in E4 (and E2:E3) should be =D4/ABS(C4).

That is mathematically sound, too. Given the percentage difference (D%)
from the target (T), T + ABS(T)*D% should equal the actual (A).
Substituting: (-350)+(350*100%) = 0.

I believe that your mistake in expecting +200% is that you are thinking of
the percentage difference between the difference (350) and the target
(-350), instead of the percentage difference between the actual (0) and the
target (-350).

Caveat: Whichever formula you choose, I think it should handle the case
where the target is zero. For example: =IF(C4=0,1,D4/ABS(C4)). But note
that the choice of 1 (100%) is arbitrary, and there is no choice that is
mathematically sound as defined above, given only the percentage difference
and the target. Of course, given the difference (not the percentage
difference), the actual should always be target plus difference. (Which is
why I question the formula in D2, as noted above.)


----- original message -----

 

[Jack Rabbitt]

Thanks very much Joe.

I think that all makes sense and will try and apply it.

Cheers!

Jack