software

I am looking for a fucntion to lookup the largest value in a bunch of cells
(disregarding whether the value is ve or -ve) then use the largest value
with its sign ( ve or -ve) and multiply by another cell.

Example:

Value 1 = 34
Value 2 = -38
Value 3 = 0
Value 4 = 12

Value A = 10
Value B = -380

The largest value disregarding the sign is -38 which is Value 2, Value 2 is
then required to multiply by Value 5 which is 10 to get Value 6 which -380.

Thanks in advance!

to get the largest sign irrespective of sign

=MAX(ABS(A1:A20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;ianthowquot; gt; wrote in message
...
gt; I am looking for a fucntion to lookup the largest value in a bunch of
cells
gt; (disregarding whether the value is ve or -ve) then use the largest value
gt; with its sign ( ve or -ve) and multiply by another cell.
gt;
gt; Example:
gt;
gt; Value 1 = 34
gt; Value 2 = -38
gt; Value 3 = 0
gt; Value 4 = 12
gt;
gt; Value A = 10
gt; Value B = -380
gt;
gt; The largest value disregarding the sign is -38 which is Value 2, Value 2
is
gt; then required to multiply by Value 5 which is 10 to get Value 6
which -380.
gt;
gt; Thanks in advance!
On Fri, 28 Apr 2006 02:05:02 -0700, ianthow gt;
wrote:

gt;I am looking for a fucntion to lookup the largest value in a bunch of cells
gt;(disregarding whether the value is ve or -ve) then use the largest value
gt;with its sign ( ve or -ve) and multiply by another cell.
gt;
gt;Example:
gt;
gt;Value 1 = 34
gt;Value 2 = -38
gt;Value 3 = 0
gt;Value 4 = 12
gt;
gt;Value A = 10
gt;Value B = -380
gt;
gt;The largest value disregarding the sign is -38 which is Value 2, Value 2 is
gt;then required to multiply by Value 5 which is 10 to get Value 6 which -380.
gt;
gt;Thanks in advance!With your values in A1:A4,
and with Value A in A10,

The formula for value B would be:

=INDEX(A1:A4,MATCH(MAX(ABS(A1:A4)),ABS(A1:A4),0))* A10

This is an **array** formula so must be entered by holding down lt;ctrlgt;lt;shiftgt;
while you hit lt;entergt;. Excel will place braces {...} around the formula.

Also, this will use the quot;firstquot; highest absolute value. So if you have a
positive and a negative value of equal magnitude, the formula will use the one
that occurs first in the list.--ron