variance differences formula

[Neomatrix]

Hi,
With formula in cell E2 I have covered almost all scenarios needed in our finance report for variance analysis, except first two scenarios which has to be included in formula as well.
1. case is if first number is positive and second negative, give me =100%
2. case is if first number is negative and second is 0, give me =-100%
Inside formula there is also max percentage 999%. I have solved all other requested scenarios except additional first two cases which has to be incorporated inside formula
Thank you

 

[awoohaw]

It may be good for the forum if you posted the other accurate scenarios and results so the forum doesn't step on a correct portion of your calculation. But on the surface, why not just wrap what you have with two IF(AND()) functions,

IF(AND(B2>0,C2<0),1,if(AND(B2<0,C2=0), -1,   ... the rest of your formula ....   ))

 

[Neomatrix]

It may be good for the forum if you posted the other accurate scenarios and results so the forum doesn't step on a correct portion of your calculation. But on the surface, why not just wrap what you have with two IF(AND()) functions,

IF(AND(B2>0,C2<0),1,if(AND(B2<0,C2=0), -1,   ... the rest of your formula ....   ))

thank you, but it is not working inside formula

 

[Neomatrix]

Hi,
With formula in cell E2 I have covered almost all scenarios needed in our finance report for variance analysis, except first two scenarios which has to be included in formula as well.
1. case is if first number is positive and second negative, give me =100%
2. case is if first number is negative and second is 0, give me =-100%
Inside formula there is also max percentage 999%. I have solved all other requested scenarios except additional first two cases which has to be incorporated inside formula
Thank you
View attachment 112321

It may be good for the forum if you posted the other accurate scenarios and results so the forum doesn't step on a correct portion of your calculation. But on the surface, why not just wrap what you have with two IF(AND()) functions,

IF(AND(B2>0,C2<0),1,if(AND(B2<0,C2=0), -1,   ... the rest of your formula ....   ))

thank you, but it is not working inside formula

 

[Neomatrix]

Hi,
With formula in cell E2 I have covered almost all scenarios needed in our finance report for variance analysis, except first two scenarios which has to be included in formula as well.
1. case is if first number is positive and second negative, give me =100%
2. case is if first number is negative and second is 0, give me =-100%
Inside formula there is also max percentage 999%. I have solved all other requested scenarios except additional first two cases which has to be incorporated inside formula
Thank you

Here is full screen

 

[Neomatrix]

It may be good for the forum if you posted the other accurate scenarios and results so the forum doesn't step on a correct portion of your calculation. But on the surface, why not just wrap what you have with two IF(AND()) functions,

IF(AND(B2>0,C2<0),1,if(AND(B2<0,C2=0), -1,   ... the rest of your formula ....   ))

Only first scenario is not working, I have no idea why? (marked yellow)

 

[Cubist]

How can you have -0? Zero is neither positive nor negative.

 

[Neomatrix]

How can you have -0? Zero is neither positive nor negative.

-1,16415321826935E-09
not minus zero, but it is displayed as minus zero

 

[Cubist]

Your IF statements are a bit convoluted so can't really decipher, but this gives the expected result.

Book1
	B	C	D
1
2	-150.00	0.00	-100%
3	100.00	0.00	100%
4	0.00	200.00	-100%
5	0.00	-250.00	100%
6	0.35	0.00	999%
Sheet7

Cell Formulas
Range	Formula
D2:D6	D2	=IFERROR(MIN(999%,-SIGN(C2)*(C2-B2)/C2),SIGN(B2))

 

[Neomatrix]

Your IF statements are a bit convoluted so can't really decipher, but this gives the expected result.

Book1
	B	C	D
1
2	-150.00	0.00	-100%
3	100.00	0.00	100%
4	0.00	200.00	-100%
5	0.00	-250.00	100%
6	0.35	0.00	999%
Sheet7

Cell Formulas
Range	Formula
D2:D6	D2	=IFERROR(MIN(999%,-SIGN(C2)*(C2-B2)/C2),SIGN(B2))

ohh my God, you are genious!!!! Thank you!