Formula for neg and pos values

[Paul]

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?

 

[Guest]

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

 

[Arvi Laanemets]

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

 

[Dave Peterson]

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:A10>0,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]

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