| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MAX() of a range of negative values?
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX() of a range of negative values?
-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. "steve_m" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX() of a range of negative values?
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. "Fred Smith" wrote: -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. "steve_m" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX() of a range of negative values?
Your formula will work quite happily when all the numbers are negative.
-- David Biddulph "steve_m" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX() of a range of negative values?
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. -- Gary''s Student - gsnu200817 "steve_m" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX() of a range of negative values?
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! "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200817 "steve_m" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX() of a range of negative values?
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". -- Pascal "steve_m" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
MAX() of a range of negative values?
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 ) "Pascal" wrote: 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". -- Pascal "steve_m" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Negative values turn into zero values | Excel Discussion (Misc queries) | |||
| Sum a range but exclude and negative values | Excel Worksheet Functions | |||
| how do i sum up a column of time values but ignore negative values | New Users to Excel | |||
| Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
| ... Count, <<< Positive Values minus Negative Values >>> ... | Excel Worksheet Functions | |||
Hybrid Mode
