Function to lookup largest value
On Fri, 28 Apr 2006 02:05:02 -0700, ianthow
wrote:
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!
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 <ctrl<shift
while you hit <enter. Excel will place braces {...} around the formula.
Also, this will use the "first" 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
|