Max or Min value for absolute value

[pagrender]

Hi,

I'm having some trouble with a formula. I would like the "Low" amount to be the value closest to zero and the "High" amount to be the number furthest from zero. My issue with this formula is that it does not work correctly if the values in the table are negative. Can someone help me with my formula below?

Thanks!

Excel Workbook
	AF	AG	AH
316	Method		11-12
317	2 Yr Avg		5
318	3 Yr Avg		11
319	5 Yr Avg		13
320	Baseline		28
321
322	Low		5
323	High		28
WriteUp

 

[VoG]

Maybe like this

Excel Workbook
	A	B
1	5	5
2	11	28
3	-13
4	-28
Sheet2

 

[shg]

All you need is

{=MIN(ABS(AH$317:AH$320))}

{=MAX(ABS(AH$317:AH$320))}

 

[pagrender]

Hi guys,

I tried that formula earlier but ended up with an issue. If I change the 5 to a -5 (in cell AH317), my new low number should be -5. This formula is producing a result of 5. Any ideas?

Excel Workbook
	AF	AG	AH
316	Method		11-12
317	2 Yr Avg		(5)
318	3 Yr Avg		11
319	5 Yr Avg		13
320	Baseline		(28)
321
322	Low		5
323	High		28
WriteUp

 

[pagrender]

I think this might work (taken from this thread - it may be overcomplicated for my use but it looks like it works):
http://www.mrexcel.com/forum/showthread.php?t=43060

Excel Workbook
	AF	AG	AH
316	Method		11-12
317	2 Yr Avg		(5)
318	3 Yr Avg		11
319	5 Yr Avg		13
320	Baseline		(28)
321
322	Low		(5)
323	High		(28)
WriteUp

 

[shg]

How about,

=IF(MAX(A1:A10) > -MIN(A1:A10), MAX(A1:A10), MIN(A1:A10))