Microsoft Excel - variance % formula - Asked By Praveen Chennupati on 25-Feb-14 07:33 AM

Hi,

I need help on variance % in excel. With the below given numbers, my variance % should be +ve instead of -ve.

Can someone help me in getting one formula with different scenarios.

1)

CY	PY	Var %	Formula used
28,390	(19,176)	-168%	B3/A3-1

2) In the below example, we should have a unit value with -ve number instead of +ve.

CY	PY	Unit value	Formula used
(28,390)	(19,176)	0.68	B3/A3

In both of the examples i need, the % formula where PY should be a denominator?

Hello Praveen,
You say that you want one formula but you seem to be calculating two fifferent factors if the names are anything to go by.
Do you mean that  if CY is positive you want to use one formula and if it is negative you want to use another?  And if that is the case, the sign of the result should match the sign of CY?
Regards
Harry

Its not the sign match. What i mean is if we hv negative no. in previous yr and +ve in current yr, it means we hv positive results. In this case if i need calculate variance %, we should be getting +ve % as our results are favourable.

Hi Praveen,
If I was trying to assess what I think you are trying to do, I would use this formula:

=(A3-B3)/ABS(B3)

This does use PY (B3) in the denominator and gives the change in value between the two years as a proportion of the vaule in the previous year.
Trust this helps but if I have misinterpreted, please get back to me.
Regards
Harry

This formula does not suits if A and B both hv negative numbers? Iam expecting a single formula in different scenarios like above?

Praveen,
I would have thought that if CY was less than PY you would want the ratio to be negative. That is the result for the current year is worse than the previous year regardless of whether either is positive or negative.  If CY is greater than PY you would want the ratio to be positive.  That is the result for the current year is better than the previous year regardless of whether either is positive or negative.  And that is what I have given for you.
Perhaps if you provide a matrix of CY and PY values and the corresponding values that you expect to see it might help to clarify the situation.
Regards
Harry

Hi Harry,

Thanks for helping. I think your formula is good to go for me. Thanks again.

thanks for your help