Function to lookup largest value
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! |
Function to lookup largest value
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) "ianthow" wrote in message ... 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! |
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 |
All times are GMT +1. The time now is 06:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com