MAX() of a range of negative values?

[steve_m]

I have a large spreadsheet of data and I need to find the maximum value for
groups of rows in each column.
Currently I have something like =MAX(A1:A10,A21:A30,A41:A50)
However. when none of the values within the specified range are greater than
zero, 0 is returned instead of the least negative number.
How can I find the maximum number for all cases including when all the
numbers are negative?
Thanks

 

[Fred Smith]

-3 is less than -2, so you are actually looking for the minimum number, not
the maximum. Change Max to Min, and your formula should work.

Regards,
Fred.

 

[Gary''s Student]

With A1 thru A5 containing:
1
-1
2
-2
-4

=MAX(ABS(A1:A5)) will return 4

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

 

[steve_m]

I already have a cell which calculated the minimum value. This works fine.
It's the cell that calculates the maximum value that I'm having trouble with.
So when the data is all negative the MIN() formula will return the most
negative value and the MAX() formula will return the least negative value.
However, the formulae also need to work when the data is positive or a mix of
positive and negative as well.

 

[David Biddulph]

Your formula will work quite happily when all the numbers are negative.

 

[Pascal]

That's not logical, I tried on a similar table and got the negative value as
result.
The only possibility I see is that probably you have one (or more) cell with
apparently no value where a "zero value" is present instead of a "blank cell".
In this case you will always got "0" as results if no other positive values
are present.
You probably have to turn on the "show a zero in cells that have zero value"
to be abble to visualize your "zero values".
Conclusion: turn on the "show a zero in cells that have zero value" and then
replace the "zero value" by "blank cell".

 

[steve_m]

I've realised my mistake, I had an extra comma in the long list of range
references to find the maximum number from so it was treating that as a zero
value. Problem now solved, thanks!

 

[steve_m]

Problem solved! There was a rogue extra comma in the very long list of cell
references within my MAX() function which I guess was interpreted as a zero
value. ie MAX(A1:10,A20:29,,A40:49....etc )