Dear All,

need your guys help.

I am working on a formula to tabulated in excel to find the End percentage of the 2 other percentage amounts.

1) WORKED PERFECTLY (VARIABLE WITH NO ZERO PERCENTAGE VALUE)

I have done up the formula as below and it works :

VARIABLE 1 : F111 WHICH IS 0.04%
VARIABLE 2 : F112 WHICH IS -0.09%

END RESULT IS 142%

CODE :
=IF(F$48=0," ",IF((1-F111/F112)2,"200%",IF((1-F111/F112)<-2,"<-200%",IF((1-F111/F112)=-2,1-F111/F112,IF((1-F111/F112)<=2,1-F111/F112,IF(AND(F111=0,F112=0),1))))))

2) DID NOT WORK , WITH #DIV/0 ERROR (VARIABLE WITH ZERO PERCENTAGE VALUE)

VARIABLE 1 : E111 WHICH IS 0.00%
VARIABLE 2 : E112 WHICH IS 0.00%

END RESULT IS #DIV/0 ERROR, EVERNTHOUGH IT IS FOLLOW THE SAME FORMULA.

CODE:

=IF(E$48=0," ",IF((1-E111/E112)2,"200%",IF((1-E111/E112)<-2,"<-200%",IF((1-E111/E112)=-2,1-E111/E112,IF((1-E111/E112)<=2,1-E111/E112,IF(OR(E111=0,E112=0),1))))))


Please help me what should be changed in the formula in order to get a figure.
Ypur help will be much appreciated.

Hi,

Am Thu, 23 May 2019 02:37:51 -0700 (PDT) schrieb
:

I am working on a formula to tabulated in excel to find the End percentage of the 2 other percentage amounts.

1) WORKED PERFECTLY (VARIABLE WITH NO ZERO PERCENTAGE VALUE)

I have done up the formula as below and it works :

VARIABLE 1 : F111 WHICH IS 0.04%
VARIABLE 2 : F112 WHICH IS -0.09%

END RESULT IS 142%

CODE :
=IF(F$48=0," ",IF((1-F111/F112)2,"200%",IF((1-F111/F112)<-2,"<-200%",IF((1-F111/F112)=-2,1-F111/F112,IF((1-F111/F112)<=2,1-F111/F112,IF(AND(F111=0,F112=0),1))))))

2) DID NOT WORK , WITH #DIV/0 ERROR (VARIABLE WITH ZERO PERCENTAGE VALUE)

VARIABLE 1 : E111 WHICH IS 0.00%
VARIABLE 2 : E112 WHICH IS 0.00%

try:
=IFERROR(IF(F$48=0,"",IF(ABS(1-F111/F112)2,IF(SIGN(1-F111/F112)=-1,"<-200%","200%"),1-F111/F112)),1)


Regards
Claus B.
--
Windows10
Office 2016