choosing from an array

[pjs44]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

from an array of positive and negative values such as 1,-4,3,5,-7, is there a way to establish a formula that would select the highest absolute value from the list, but then return the actual value from the array with its appropriate sign? for example, from the array above, the formula would return -7

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

from an array of positive and negative values such as 1,-4,3,5,-7, is there a
way to establish a formula that would select the highest absolute value from
the list, but then return the actual value from the array with its
appropriate sign? for example, from the array above, the formula would return
-7

One way (array-entered with CMD-RETURN):

=INDEX(A:A,MATCH(MAX(ABS(A:A)),ABS(A:A),FALSE))

Note that prior to XL08, you can't use entire columns in array formulae,
so if this workbook may be used in previous versions, use (still
array-entered):

=INDEX(A1:A100,MATCH(MAX(ABS(A1:A100)),ABS(A1:A100),FALSE))

 

[pjs44]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

from an array of positive and negative values such as 1,-4,3,5,-7, is there a
way to establish a formula that would select the highest absolute value from
the list, but then return the actual value from the array with its
appropriate sign? for example, from the array above, the formula would return
-7

One way (array-entered with CMD-RETURN):

=INDEX(A:A,MATCH(MAX(ABS(A:A)),ABS(A:A),FALSE))

Note that prior to XL08, you can't use entire columns in array formulae,
so if this workbook may be used in previous versions, use (still
array-entered):

=INDEX(A1:A100,MATCH(MAX(ABS(A1:A100)),ABS(A1:A100),FALSE))
[/QUOTE]
thanks J.E. I'm not having any luck getting that to work. When it didn't work overall, i began to look at pieces of your suggestion. It appears I am having a problem with the =MAX(ABS(A1:A10)) portion. I didn't realize that the ABS function would work in an array vs. referencing each cell individually. What am I doing wrong? Thanks for any help you can provide!

 

[JE McGimpsey]

thanks J.E. I'm not having any luck getting that to work. When it didn't work
overall, i began to look at pieces of your suggestion. It appears I am having
a problem with the =MAX(ABS(A1:A10)) portion. I didn't realize that the ABS
function would work in an array vs. referencing each cell individually. What
am I doing wrong?

Unless you tell us what "didn't work" means to you, one can only
imagine...

Are you getting an error? the wrong value? a crash?

You are array-entering the formula, right? If so, XL will put curly
brackets around the formula in the formula bar:

{=INDEX(A1:A100,MATCH(MAX(ABS(A1:A100)),ABS(A1:A100),FALSE))}

(you can't put those in manually and have it work).

 

[pjs44]

thanks J.E. I'm not having any luck getting that to work. When it didn't work
overall, i began to look at pieces of your suggestion. It appears I am having
a problem with the =MAX(ABS(A1:A10)) portion. I didn't realize that the ABS
function would work in an array vs. referencing each cell individually. What
am I doing wrong?

Unless you tell us what "didn't work" means to you, one can only
imagine...

Are you getting an error? the wrong value? a crash?

You are array-entering the formula, right? If so, XL will put curly
brackets around the formula in the formula bar:

{=INDEX(A1:A100,MATCH(MAX(ABS(A1:A100)),ABS(A1:A100),FALSE))}

(you can't put those in manually and have it work).
[/QUOTE]
thanks so much for your help! i was getting the VALUE message because, as you figured, i was not entering the formula in array format; works now; sorry for the trouble.