Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: % 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%) 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, A1<B1, A1B1 with negative values etc. Taking all the possiblities how can I have a working formula incorporating all possibilities??!!! How many IF(AND, ,) / IF(OR, ,) I have to use? Thanks for your help. Abdul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: % Variance
Not sure if I've picked up exactly what you are getting at
but can't you use? =IF(A1<0,(B1/A1-1)*100,0) I'm assuming that if A1 is 0 then C1 is 0 also? This always negative numbers if you don't want negative numbers use the ABS term Laura -----Original 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%) 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, A1<B1, A1B1 with negative values etc. Taking all the possiblities how can I have a working formula incorporating all possibilities??!!! How many IF(AND, ,) / IF(OR, ,) I have to use? Thanks for your help. Abdul . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: % Variance
i think still it will not take all cases.
if A1=0 and B1 0 C1=100% and if A1=0 and B1<0 (??%) minus figure if i use your formulae : if A1= -1 and B1= -2 wht would be the result and if A1= -2 and B1= -1 wht would be the result I am comparing the revenu from two items in two years so i have all cases like A1=0, B1 =0 A1=0, B1 0 A1=0, B1 <0 A1=<0, B1 0 ........ i mean all the possible combinations any help ? Abdul -----Original Message----- Not sure if I've picked up exactly what you are getting at but can't you use? =IF(A1<0,(B1/A1-1)*100,0) I'm assuming that if A1 is 0 then C1 is 0 also? This always negative numbers if you don't want negative numbers use the ABS term Laura -----Original 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%) 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, A1<B1, A1B1 with negative values etc. Taking all the possiblities how can I have a working formula incorporating all possibilities??!!! How many IF(AND, ,) / IF(OR, ,) I have to use? Thanks for your help. Abdul . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variance | Excel Discussion (Misc queries) | |||
variance % | Excel Worksheet Functions | |||
Pivot Tables - Variance and Variance % | Excel Discussion (Misc queries) | |||
Pivot Tables - Variance and % Variance fields | Excel Discussion (Misc queries) | |||
% variance | Excel Programming |