summing an array of cells absolute value

[JohnJack]

In quattro, the formula @max(@abs(B5..B200)) would return the maximum
of the absolute values in cells B5 to B200. I am having troubles
finding a function in excel that will do this

basically returning the max of an arrays absolute values. I know I
can create another column doing the individual absolute values and
just take the max of that, but I would think excel would have a
similar function.

Any help would be appreciated.

Jack

 

[Dave Peterson]

try
=max(abs(b5:b200))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And you can't use the whole column unless you're using xl2007.

=====
Or maybe...
=if(count(b5:b200)=0,"No Numbers",max(abs(b5:b200)))
(still array entered)

 

[JohnJack]

try
=max(abs(b5:b200))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And you can't use the whole column unless you're using xl2007.

=====
Or maybe...
=if(count(b5:b200)=0,"No Numbers",max(abs(b5:b200)))
(still array entered)

Thanks a ton. the ctrl-shift-enter thing worked. Just as a side note,
why does excel require you to do this?
Jack

 

[Dave Peterson]

Some functions work like loops--for each cell in that range, do something.
Excel uses the ctrl-shift-enter to know that you want it do that loop.

If you want a better explanation of how to use these array formulas, check out
Chip Pearson's site:

http://www.cpearson.com/excel/array.htm