I have a range of cells (A1:A10) that sometime contain
only positive values, sometime contain all negative
values, and sometime contain both positive and negative
values.

I need the valued of the difference between the maximum
and minimum values; but MAX(A1:A10)-MIN(A1:A10) only works
when min is equal or greater than zero.

Is there a formula in Excel or VBA that returns the
correct difference between the maximum and minimum even
when there are negative vlues in the range of values?

The formula works for me. This is Cols A,B,C, with formulas at bottom for
Max, Min and Delta( diff between Max and Min). Maybe this is not what you
want?
1 -1 1
2 -2 2
3 -3 3
4 -4 4
5 -5 5
-1 -1 1
-2 -2 2
-3 -3 3
-4 -4 4
-5 -5 5

Delta 10 4 4
Max 5 -1 5
Min -5 -5 1


"Paul" wrote:

I have a range of cells (A1:A10) that sometime contain
only positive values, sometime contain all negative
values, and sometime contain both positive and negative
values.

I need the valued of the difference between the maximum
and minimum values; but MAX(A1:A10)-MIN(A1:A10) only works
when min is equal or greater than zero.

Is there a formula in Excel or VBA that returns the
correct difference between the maximum and minimum even
when there are negative vlues in the range of values?

Hi

What do you want to be returned, and what do you get instead?

For me, when max value in range is p.e. 3 and minimum value -4, your formula
returns 7 - which is right answer, because biggest number is 3 and smallest
is -4.
Maybe you wnt to compare absolute values?
=MAX(ABS(A1:A10))-MIN(ABS(A1:A10))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Paul" wrote in message
...
I have a range of cells (A1:A10) that sometime contain
only positive values, sometime contain all negative
values, and sometime contain both positive and negative
values.

I need the valued of the difference between the maximum
and minimum values; but MAX(A1:A10)-MIN(A1:A10) only works
when min is equal or greater than zero.

Is there a formula in Excel or VBA that returns the
correct difference between the maximum and minimum even
when there are negative vlues in the range of values?

If you have
-5
-4
-3
-2
-1
0
1
2
3
4

In A1:A10, the minimum is -5. The maximum is 4.
Maximum-minimum = 4 - -5 = 9.

What do you really want?

If you're looking for the maximum positive number, you can use something like:

=IF(COUNTIF(A1:A10,"0")=0,"No Positive numbers",MAX(IF(A1:A100,A1:A10)))

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.)


Paul wrote:

I have a range of cells (A1:A10) that sometime contain
only positive values, sometime contain all negative
values, and sometime contain both positive and negative
values.

I need the valued of the difference between the maximum
and minimum values; but MAX(A1:A10)-MIN(A1:A10) only works
when min is equal or greater than zero.

Is there a formula in Excel or VBA that returns the
correct difference between the maximum and minimum even
when there are negative vlues in the range of values?

--

Dave Peterson

I stand corrected - thanks - just did not know it worked
that way

-----Original Message-----
The formula works for me. This is Cols A,B,C, with
formulas at bottom for
Max, Min and Delta( diff between Max and Min). Maybe this
is not what you
want?
1 -1 1
2 -2 2
3 -3 3
4 -4 4
5 -5 5
-1 -1 1
-2 -2 2
-3 -3 3
-4 -4 4
-5 -5 5

Delta 10 4 4
Max 5 -1 5
Min -5 -5 1


"Paul" wrote:

I have a range of cells (A1:A10) that sometime contain
only positive values, sometime contain all negative
values, and sometime contain both positive and negative
values.

I need the valued of the difference between the maximum
and minimum values; but MAX(A1:A10)-MIN(A1:A10) only
works
when min is equal or greater than zero.

Is there a formula in Excel or VBA that returns the
correct difference between the maximum and minimum even
when there are negative vlues in the range of values?

.