#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 137
Default % Variance

Hi All,

How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and if A1=150 and B1=100 then I shud get in C1 (33.33%)
and if A1= -150 and B1=100 then I shud get in C1 167%)
and if A1= -150 and B1= -100 then I shud get in C1 33 %)
and if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and if A1= -100 and B1= -150 then I shud get in C1 -50 %)

I am having this with the formula (B1/A1)-1 IN C1

Now my problem is that both A1 and C1 can be zero, either
of them or both of them can be negative, one can be
negative and the other positive, A1 Less Than B1 or A1 More than B1
with
negative values etc. Taking all the possiblities how can
I have a working formula??!!!

Thanks for your help.

Abdul
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 905
Default % Variance

"Abdul" wrote:
How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and if A1=150 and B1=100 then I shud get in C1 (33.33%)
and if A1= -150 and B1=100 then I shud get in C1 167%)
and if A1= -150 and B1= -100 then I shud get in C1 33 %)
and if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and if A1= -100 and B1= -150 then I shud get in C1 -50 %)


You asking for the percentage difference of B1 over A1. Try:

=(B1-A1) / ABS(A1)


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

"Abdul" wrote in message
...
Hi All,

How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and if A1=150 and B1=100 then I shud get in C1 (33.33%)
and if A1= -150 and B1=100 then I shud get in C1 167%)
and if A1= -150 and B1= -100 then I shud get in C1 33 %)
and if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and if A1= -100 and B1= -150 then I shud get in C1 -50 %)

I am having this with the formula (B1/A1)-1 IN C1

Now my problem is that both A1 and C1 can be zero, either
of them or both of them can be negative, one can be
negative and the other positive, A1 Less Than B1 or A1 More than B1
with
negative values etc. Taking all the possiblities how can
I have a working formula??!!!

Thanks for your help.

Abdul


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 905
Default % Variance

"Joe User" <joeu2004 wrote in message
...
You asking for the percentage difference of B1 over A1. Try:
=(B1-A1) / ABS(A1)


The following returns plus or minus 100% when A1 is zero. That's an
arbitrary choice; you might choose something else. Technically, the
percentage difference cannot be computed when A1 is zero.

=IF(A1=0, SIGN(B1), (B1-A1)/ABS(A1))


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

"Joe User" <joeu2004 wrote in message
...
"Abdul" wrote:
How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and if A1=150 and B1=100 then I shud get in C1 (33.33%)
and if A1= -150 and B1=100 then I shud get in C1 167%)
and if A1= -150 and B1= -100 then I shud get in C1 33 %)
and if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and if A1= -100 and B1= -150 then I shud get in C1 -50 %)


You asking for the percentage difference of B1 over A1. Try:

=(B1-A1) / ABS(A1)


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

"Abdul" wrote in message
...
Hi All,

How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and if A1=150 and B1=100 then I shud get in C1 (33.33%)
and if A1= -150 and B1=100 then I shud get in C1 167%)
and if A1= -150 and B1= -100 then I shud get in C1 33 %)
and if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and if A1= -100 and B1= -150 then I shud get in C1 -50 %)

I am having this with the formula (B1/A1)-1 IN C1

Now my problem is that both A1 and C1 can be zero, either
of them or both of them can be negative, one can be
negative and the other positive, A1 Less Than B1 or A1 More than B1
with
negative values etc. Taking all the possiblities how can
I have a working formula??!!!

Thanks for your help.

Abdul



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 137
Default % Variance

Thanks All,

Joe User, Your formula works fine for me.
I think some of them do not understand the question well.

Thanks again




On Nov 8, 9:21*pm, "Joe User" <joeu2004 wrote:
"Joe User" <joeu2004 wrote in message

...

You asking for the percentage difference of B1 over A1. *Try:
=(B1-A1) / ABS(A1)


The following returns plus or minus 100% when A1 is zero. *That's an
arbitrary choice; you might choose something else. *Technically, the
percentage difference cannot be computed when A1 is zero.

=IF(A1=0, SIGN(B1), (B1-A1)/ABS(A1))

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

"Joe User" <joeu2004 wrote in message

...

"Abdul" wrote:
How can I have a formula to get % variance (+/-) without
generating an error?


for eg: if A1=100 and B1=150 then I shud get in C1 50%
and * * if A1=150 and B1=100 then I shud get in C1 (33.33%)
and * * if A1= -150 and B1=100 then I shud get in C1 167%)
and * * if A1= -150 and B1= -100 then I shud get in C1 33 %)
and * * if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and * * if A1= -100 and B1= -150 then I shud get in C1 -50 %)


You asking for the percentage difference of B1 over A1. *Try:


=(B1-A1) / ABS(A1)


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


"Abdul" wrote in message
...
Hi All,


How can I have a formula to get % variance (+/-) without
generating an error?


for eg: if A1=100 and B1=150 then I shud get in C1 50%
and * * if A1=150 and B1=100 then I shud get in C1 (33.33%)
and * * if A1= -150 and B1=100 then I shud get in C1 167%)
and * * if A1= -150 and B1= -100 then I shud get in C1 33 %)
and * * if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and * * if A1= -100 and B1= -150 then I shud get in C1 -50 %)


I am having this with the formula (B1/A1)-1 IN C1


Now my problem is that both A1 and C1 can be zero, either
of them or both of them can be negative, one can be
negative and the other positive, A1 Less Than B1 or A1 More than B1
with
negative values etc. Taking all the possiblities how can
I have a working formula??!!!


Thanks for your help.


Abdul


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Variance FreddieP Excel Discussion (Misc queries) 5 September 28th 06 01:41 PM
variance % karibug Excel Worksheet Functions 5 June 14th 06 10:54 PM
Pivot Tables - Variance and Variance % PJS Excel Discussion (Misc queries) 2 January 18th 06 04:12 AM
CALCULATING VARIANCE Julian Campbell Excel Discussion (Misc queries) 5 May 9th 05 12:38 AM
Pivot Tables - Variance and % Variance fields CraigS Excel Discussion (Misc queries) 5 January 6th 05 01:22 AM


All times are GMT +1. The time now is 01:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"