I have a calculation of Committed and Actual to show the difference and then to show that difference in %.
Further, the % should max out at 100% or -100% if the difference is over 100% or -100%, and show numbers (%) as they are in between that range.
I've IFERROR to omit the #DIV/0 error if divided by zero.
The formula I have is working until the point where if there is nothing in a Committed column, so zero (0), and Actual is in negative, it's maxing out at positive 100% when actually should max out at negative -100% (marked in yellow in the screenshot).
Here is the formula I used.
=IFERROR(IF(AND(D49=0,E49<>0),1,
IF(AND(F49>0,F49/D49>100%),1,
IF(AND(F49>0,F49/D49<-99%),1,F49/D49))),0)
Further, the % should max out at 100% or -100% if the difference is over 100% or -100%, and show numbers (%) as they are in between that range.
I've IFERROR to omit the #DIV/0 error if divided by zero.
The formula I have is working until the point where if there is nothing in a Committed column, so zero (0), and Actual is in negative, it's maxing out at positive 100% when actually should max out at negative -100% (marked in yellow in the screenshot).
Here is the formula I used.
=IFERROR(IF(AND(D49=0,E49<>0),1,
IF(AND(F49>0,F49/D49>100%),1,
IF(AND(F49>0,F49/D49<-99%),1,F49/D49))),0)