Excel % Variance Formula........

[BigDon]

How do I formulate negative variance formulas? Example: 2005 (-) 500
2004 (-) 400 Variance =(-) 100 Variance % (-)100/(-)400 =25%
I want a formula that gives me (-) 25%.........not positive.......

If formula????

[N Harkawat]

How about multiplyingthe result by -1?


"BigDon" <[email protected]> wrote in message
news:[email protected]...
> How do I formulate negative variance formulas? Example: 2005 (-) 500
> 2004 (-) 400 Variance =(-) 100 Variance % (-)100/(-)400 =25%
> I want a formula that gives me (-) 25%.........not positive.......
>
> If formula????

[BigDon]

The problem is next month when the numbers change to positive......
Example.....2005 200.... 2004 (-100) Variance 300 Variance % -100/300
-33% which is correct, but formula times (-1) makes it positive 33%.......

Numbers change monthly.....some positive some negative......

"N Harkawat" wrote:

> How about multiplyingthe result by -1?
>
>
> "BigDon" <[email protected]> wrote in message
> news:[email protected]...
> > How do I formulate negative variance formulas? Example: 2005 (-) 500
> > 2004 (-) 400 Variance =(-) 100 Variance % (-)100/(-)400 =25%
> > I want a formula that gives me (-) 25%.........not positive.......
> >
> > If formula????
>
>
>

[N Harkawat]

Bigdon,
In your example the variance change % is actually
300/-100 = -300%
which when multiplied by -1 gives 300%

So from your 2 examples and by applying the logic of multiplying it by -1 I
get

2005 2004 Difference Difference %

-500 -400 100 100/-400 * -1 = -25%
200 -100 300 300/-100 * -1 = +300%

Which is what you want isn't it?


"BigDon" wrote:

> The problem is next month when the numbers change to positive......
> Example.....2005 200.... 2004 (-100) Variance 300 Variance % -100/300
> -33% which is correct, but formula times (-1) makes it positive 33%.......
>
> Numbers change monthly.....some positive some negative......
>
> "N Harkawat" wrote:
>
> > How about multiplyingthe result by -1?
> >
> >
> > "BigDon" <[email protected]> wrote in message
> > news:[email protected]...
> > > How do I formulate negative variance formulas? Example: 2005 (-) 500
> > > 2004 (-) 400 Variance =(-) 100 Variance % (-)100/(-)400 =25%
> > > I want a formula that gives me (-) 25%.........not positive.......
> > >
> > > If formula????
> >
> >
> >

[BigDon]

Lets try this.....2005 -2,059 2004 -1,289 Variance -770 Variance %
-770/-1289
59.7% should be (-) 59.7%

Next month.......2005 2,278 2004 -5,005 Variance 7,283 Variance %
7,383/-5005
-145.5% should be 145.5%........


"N Harkawat" wrote:

> Bigdon,
> In your example the variance change % is actually
> 300/-100 = -300%
> which when multiplied by -1 gives 300%
>
> So from your 2 examples and by applying the logic of multiplying it by -1 I
> get
>
> 2005 2004 Difference Difference %
>
> -500 -400 100 100/-400 * -1 = -25%
> 200 -100 300 300/-100 * -1 = +300%
>
> Which is what you want isn't it?
>
>
> "BigDon" wrote:
>
> > The problem is next month when the numbers change to positive......
> > Example.....2005 200.... 2004 (-100) Variance 300 Variance % -100/300
> > -33% which is correct, but formula times (-1) makes it positive 33%.......
> >
> > Numbers change monthly.....some positive some negative......
> >
> > "N Harkawat" wrote:
> >
> > > How about multiplyingthe result by -1?
> > >
> > >
> > > "BigDon" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > How do I formulate negative variance formulas? Example: 2005 (-) 500
> > > > 2004 (-) 400 Variance =(-) 100 Variance % (-)100/(-)400 =25%
> > > > I want a formula that gives me (-) 25%.........not positive.......
> > > >
> > > > If formula????
> > >
> > >
> > >

[N Harkawat]

That is why I have been mentioning to multiply your result by -1

Therefore your formula will be something like this
=-770/-1289 * (-1) = -59.7%

= 7383/-5005 *(-1) = 145.5%

Notice carefully the * (-1) added after the first part of your formula




"BigDon" <[email protected]> wrote in message
news:[email protected]...
> Lets try this.....2005 -2,059 2004 -1,289 Variance -770 Variance %
> -770/-1289
> 59.7% should be (-) 59.7%
>
> Next month.......2005 2,278 2004 -5,005 Variance 7,283 Variance %
> 7,383/-5005
> -145.5% should be 145.5%........
>
>
> "N Harkawat" wrote:
>
>> Bigdon,
>> In your example the variance change % is actually
>> 300/-100 = -300%
>> which when multiplied by -1 gives 300%
>>
>> So from your 2 examples and by applying the logic of multiplying it by -1
>> I
>> get
>>
>> 2005 2004 Difference Difference %
>>
>> -500 -400 100 100/-400 * -1 = -25%
>> 200 -100 300 300/-100 * -1 = +300%
>>
>> Which is what you want isn't it?
>>
>>
>> "BigDon" wrote:
>>
>> > The problem is next month when the numbers change to positive......
>> > Example.....2005 200.... 2004 (-100) Variance 300 Variance % -100/300
>> > -33% which is correct, but formula times (-1) makes it positive
>> > 33%.......
>> >
>> > Numbers change monthly.....some positive some negative......
>> >
>> > "N Harkawat" wrote:
>> >
>> > > How about multiplyingthe result by -1?
>> > >
>> > >
>> > > "BigDon" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > How do I formulate negative variance formulas? Example: 2005 (-)
>> > > > 500
>> > > > 2004 (-) 400 Variance =(-) 100 Variance % (-)100/(-)400 =25%
>> > > > I want a formula that gives me (-) 25%.........not positive.......
>> > > >
>> > > > If formula????
>> > >
>> > >
>> > >