summing an array of cells absolute value
 

In quattro, the formula )) 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

summing an array of cells absolute value
 

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 wrote:

In quattro, the formula )) 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

summing an array of cells absolute value
 

On Feb 9, 9:28 am, Dave Peterson wrote:
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 wrote:

In quattro, the formula )) 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

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

summing an array of cells absolute value
 

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

JohnJack wrote:

On Feb 9, 9:28 am, Dave Peterson wrote:
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 wrote:

In quattro, the formula )) 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

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

Thank you very much. The cntrl shift enter did the trick. Otherwise excel does not recognize the formula.