i have a list of numbers in multiple columns......
A B C D
1 40 -36 MAX abs. Value 1
2 45 -45 MAX abs. Value 2
3 21 -10
4 26 -46
: :

i have to select the largest absolute value from:
1) columns 'b' and 'c', and from the from rows 1, 3, 5,... etc
2) columns 'b' and 'c', and from the from rows 2, 4, 6,... etc

I know how to use the =MAX(cell selection) function:
D1 =MAX(B1:C1, B3:C3,...)

but how do i compare the absolute values so that:
D1 = 40
D2 = 46

my =MAX(cell selection) formula is quite large, so i dont want to do an
=IF(this, then, else) to check to see if it's negative, and then multiply by
-1 if it is, because my formula will be excessively large.

any help?

i believe i found the answer!
using an =ABS(cell) formula
woo.


"Derrick" wrote:

i have a list of numbers in multiple columns......
A B C D
1 40 -36 MAX abs. Value 1
2 45 -45 MAX abs. Value 2
3 21 -10
4 26 -46
: :

i have to select the largest absolute value from:
1) columns 'b' and 'c', and from the from rows 1, 3, 5,... etc
2) columns 'b' and 'c', and from the from rows 2, 4, 6,... etc

I know how to use the =MAX(cell selection) function:
D1 =MAX(B1:C1, B3:C3,...)

but how do i compare the absolute values so that:
D1 = 40
D2 = 46

my =MAX(cell selection) formula is quite large, so i dont want to do an
=IF(this, then, else) to check to see if it's negative, and then multiply by
-1 if it is, because my formula will be excessively large.

any help?

ok i lied. it didnt work -

the =ABS(MAX() returns the abs of the value that's already been selected
because it's positive.

reversing it... MAX(ABS(B1:C1, B3:C3,...)) doesnt work either.

any help?

"Derrick" wrote:

i have a list of numbers in multiple columns......
A B C D
1 40 -36 MAX abs. Value 1
2 45 -45 MAX abs. Value 2
3 21 -10
4 26 -46
: :

i have to select the largest absolute value from:
1) columns 'b' and 'c', and from the from rows 1, 3, 5,... etc
2) columns 'b' and 'c', and from the from rows 2, 4, 6,... etc

I know how to use the =MAX(cell selection) function:
D1 =MAX(B1:C1, B3:C3,...)

but how do i compare the absolute values so that:
D1 = 40
D2 = 46

my =MAX(cell selection) formula is quite large, so i dont want to do an
=IF(this, then, else) to check to see if it's negative, and then multiply by
-1 if it is, because my formula will be excessively large.

any help?

Try these array formulas**:

For the even numbered rows:

=MAX(IF(MOD(ROW(B1:B15),2)=0,ABS(B1:C15)))

For the odd numbered rows:

=MAX(IF(MOD(ROW(B1:B15),2)=1,ABS(B1:C15)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Derrick" wrote in message
...
i have a list of numbers in multiple columns......
A B C D
1 40 -36 MAX abs. Value 1
2 45 -45 MAX abs. Value 2
3 21 -10
4 26 -46
: :

i have to select the largest absolute value from:
1) columns 'b' and 'c', and from the from rows 1, 3, 5,... etc
2) columns 'b' and 'c', and from the from rows 2, 4, 6,... etc

I know how to use the =MAX(cell selection) function:
D1 =MAX(B1:C1, B3:C3,...)

but how do i compare the absolute values so that:
D1 = 40
D2 = 46

my =MAX(cell selection) formula is quite large, so i dont want to do an
=IF(this, then, else) to check to see if it's negative, and then multiply
by
-1 if it is, because my formula will be excessively large.

any help?