Formula problems: max and min

[Terry Regan]

Hi everyone,

I have two columns of data (A and B) that I wish to compare with a
formula in column C

for each row, the numbers will both be either zero, positive or
negative.

all I wish to do is find the number closest to zero, i.e. least
postive or least negative but when I get the least postive of two
positive numbers, it returns the most negative of two negative
numbers.......

e.g.

A B C
2 4 should return 2
-2 -4 should return -4
0 0 should return 0

any help would be very appreciated.

Terry

 

[Ken Wright]

=MIN(ABS(A1:A30))

array entered - CTRL+SHIFT+ENTER

 

[Laura Cook]

The following should return the resulst you said should be returned:

=MIN(A1:B1)

If you meant -2 should be returned for the second set of data (as that would
be closest to zero) then try the following:

=IF(ABS(A1)>ABS(B1),B1,A1)

 

[Rick]

it may not be the easiest way but you can always put

=if(a2>0,(maxa2:b2),if(a2<0,(maxA2:B2),0))

If both are not always negative or positive, or matching
zeros or will not be in the future it would be better to
use an AND function with this.

Rick