% variance formula problem

[gsullivan]

Hi,

I am having trouble with a percentage % variance formula in excel where I want to show the difference between a negative figure and a positive figure. eg:

In month 1 the income was -$60. In month 2 it increased to +$150. The variance is growth +$210 however if I then divide that growth of +210 by the -60 the formula returns -350% growth which is clearly wrong. It should be +350%!

The same applies if the two income figures are both negative but with positive growth ie mth1 -$60 and mth2 -$40 (growth of +$20) but formula returns -33% where it should be positive.

I know I can simply but a '-' negative sign in front of the formula but I unfortunately have many columns of figures I need to show the % variance on so would have to individually adjust each one. I simply want to copy down the formula. It seems so simple, but I'm just not getting it!

All help greatly appreciated.

GS

[DonkeyOte]

I always get confused on these myself... perhaps

=((month2-month1)/month1)*SIGN(month1)

[tony h]

I can't comment on your assertion that the & figure should be positive but if you want all the % to be positive use the sign() function.
eg

Please Login or Register  to view this content.

You will have to be careful with zeros. But your formula means you will have to be careful with zero anyway.

[gsullivan]

Guys, once again you've made my analytically led life a breeze. Have used the SIGN function and I get what want - be it right or wrong!! ;-)

Many thanks,
GS

viva la excelforum!

[tony h]

Pre.S. I am not sure whether I should have put this in a [ CODE] panel as it isn't actually code

I know I said I wouldn't comment but watch the trend in the %

Please Login or Register  to view this content.