Extract largest value from array regardless of sign and ignoring errors

[amclay6]

Hello,

I am trying the get the largest value, positive or negative, from an array.
e.g. (-1.27, #VALUE!, 0.12, -0.54)
For the above case I want the answer to be -1.27.

I have used the following formula however I keep getting an error.
=INDEX(C66:F66,MATCH(AGGREGATE(4,6,ABS(C66:F66)),ABS(C66:F66),0))

For some reason the ABS function in the AGGREGATE function doesn't work.

Any solutions??

 

[kevin9999]

Do you need AGGREGATE() for this? Try

=MAX(ABS(C66:F66))

 

[amclay6]

Do you need AGGREGATE() for this? Try

=MAX(ABS(C66:F66))

I need the value returned with its original sign (-1.27)

 

[kevin9999]

test.xlsx
	C	D	E	F	G	H
65
66	-1.27	#VALUE!	0.12	-0.54		-1.27
67
Sheet1

Cell Formulas
Range	Formula
D66	D66	=E66+D64
H66	H66	=IF(ABS(AGGREGATE(5,2,C66:F66))>ABS(AGGREGATE(4,2,C66:F66)),AGGREGATE(5,2,C66:F66),AGGREGATE(4,2,C66:F66))

 

[amclay6]

test.xlsx
	C	D	E	F	G	H
65
66	-1.27	#VALUE!	0.12	-0.54		-1.27
67
Sheet1

Cell Formulas
Range	Formula
D66	D66	=E66+D64
H66	H66	=IF(ABS(AGGREGATE(5,2,C66:F66))>ABS(AGGREGATE(4,2,C66:F66)),AGGREGATE(5,2,C66:F66),AGGREGATE(4,2,C66:F66))

Legend, thanks for your help!

 

[kevin9999]

Legend, thanks for your help!

Happy to help, and thanks for the feedback.

 

[Peter_SSs]

If you want to stick with that previous method, you could write that more efficiently as

=LET(a,AGGREGATE(5,6,C66:F66),b,AGGREGATE(4,6,C66:F66),IF(-a>b,a,b))

Here is another approach you could also consider

22 07 11.xlsm
	C	D	E	F	G	H
66	-1.27	#VALUE!	0.12	-0.54		-1.27
+- Max

Cell Formulas
Range	Formula
H66	H66	=INDEX(SORTBY(C66:F66,IFERROR(ABS(C66:F66),0),-1),1)