Max out at negative -100%

[Miki]

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)

 

[joeu2004]

I would write:

=IF(D49=0, IF(E49<>0, 100%, 0%), MAX(-100%, MIN(100%, F49/D49)))

IFERROR is not necessary since #DIV/0 cannot happen. But that assumes that D49 and F49 are numeric or empty (i.e. no #VALUE error).

 

[jasonb75]

See if this helps

Book12345 (version 1).xlsb
	D	E	F	G	H	I
48	Committed	Actual	Difference	jason.b75	joeu2004	request
49	$ 259,964.88	$ 286,074.99	$ 26,110.11	10%	10%	10%
50	$ 1,695.90	$ 6,441.36	$ 4,745.46	100%	100%	100%
51	$ 143,936.36	$ 192,984.46	$ 49,048.10	34%	34%	34%
52	$ 116,626.93	$ 116,626.93	$ -	0%	0%	0%
53	$ 7,708.00		$ -7,708.00	-100%	-100%	-100%
54	$ -3,261.07	$ 23,447.29	$ 26,708.36	100%	-100%	100%
55	$ 20,303.37	$ 20,303.37	$ -	0%	0%	0%
56	$ 901,609.40	$ 875,807.99	$ -25,801.41	-3%	-3%	-3%
57	$ 501,569.49	$ 583,199.86	$ 81,630.37	16%	16%	16%
58		$ -37,380.00	$ -37,380.00	-100%	100%	-100%
Sheet1

Cell Formulas
Range	Formula
F49:F58	F49	=E49-D49
G49:G58	G49	=MEDIAN(-1,1,ABS(IFERROR(F49/D49,1))*SIGN(F49))
H49:H58	H49	=IF(D49=0, IF(E49<>0, 100%, 0%), MAX(-100%, MIN(100%, F49/D49)))

 

[Miki]

See if this helps

Book12345 (version 1).xlsb
	D	E	F	G	H	I
48	Committed	Actual	Difference	jason.b75	joeu2004	request
49	$ 259,964.88	$ 286,074.99	$ 26,110.11	10%	10%	10%
50	$ 1,695.90	$ 6,441.36	$ 4,745.46	100%	100%	100%
51	$ 143,936.36	$ 192,984.46	$ 49,048.10	34%	34%	34%
52	$ 116,626.93	$ 116,626.93	$ -	0%	0%	0%
53	$ 7,708.00		$ -7,708.00	-100%	-100%	-100%
54	$ -3,261.07	$ 23,447.29	$ 26,708.36	100%	-100%	100%
55	$ 20,303.37	$ 20,303.37	$ -	0%	0%	0%
56	$ 901,609.40	$ 875,807.99	$ -25,801.41	-3%	-3%	-3%
57	$ 501,569.49	$ 583,199.86	$ 81,630.37	16%	16%	16%
58		$ -37,380.00	$ -37,380.00	-100%	100%	-100%
Sheet1

Cell Formulas
Range	Formula
F49:F58	F49	=E49-D49
G49:G58	G49	=MEDIAN(-1,1,ABS(IFERROR(F49/D49,1))*SIGN(F49))
H49:H58	H49	=IF(D49=0, IF(E49<>0, 100%, 0%), MAX(-100%, MIN(100%, F49/D49)))

Worked like a charm! Thanks for your help @jasonb75, appreciate it!

 

[Miki]

I would write:

=IF(D49=0, IF(E49<>0, 100%, 0%), MAX(-100%, MIN(100%, F49/D49)))

IFERROR is not necessary since #DIV/0 cannot happen. But that assumes that D49 and F49 are numeric or empty (i.e. no #VALUE error).

Thanks, @joeu2004 for your reply, however the answer from @jasonb75 solved it entirely.