Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula needed
I want to perform an sumproduct type of function with on column of data (1 x
n) and the minimum value in each row of a 2 x n matrix. Is there a simple formula that will allow me to do this. I know that I could create a new column with the minimums or that I could brute force the math, I am just hoping for something simple since I have to apply it a few hundred times. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula needed
Hi,
If the (1 x n) range data are in Column A, and the (2 x n) matrix data are in Columns B and C, use the following formula: =SUMPRODUCT(A1:A100,IF(B1:B100<C1:C100,B1:B100,C1: C100)) and confirm with CTRL-SHIFT-ENTER. Regards, B. R. Ramachandran "ZipCurs" wrote: I want to perform an sumproduct type of function with on column of data (1 x n) and the minimum value in each row of a 2 x n matrix. Is there a simple formula that will allow me to do this. I know that I could create a new column with the minimums or that I could brute force the math, I am just hoping for something simple since I have to apply it a few hundred times. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula needed
Thanks, that did the trick. What if instead of a (2xn) matrix, I have one
(1xn) matrix and one (nx1) matrix that I want to select the minimum from. This doesn't seem to work with that. "B. R.Ramachandran" wrote: Hi, If the (1 x n) range data are in Column A, and the (2 x n) matrix data are in Columns B and C, use the following formula: =SUMPRODUCT(A1:A100,IF(B1:B100<C1:C100,B1:B100,C1: C100)) and confirm with CTRL-SHIFT-ENTER. Regards, B. R. Ramachandran "ZipCurs" wrote: I want to perform an sumproduct type of function with on column of data (1 x n) and the minimum value in each row of a 2 x n matrix. Is there a simple formula that will allow me to do this. I know that I could create a new column with the minimums or that I could brute force the math, I am just hoping for something simple since I have to apply it a few hundred times. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula needed
"ZipCurs" wrote
.. What if instead of a (2xn) matrix, I have one (1xn) matrix and one (nx1) matrix that I want to select the minimum from. Assume the (1xn) is A1:A10, and the (nx1) is B1:Z1 Try in say, B2: =SUMPRODUCT(A1:A10*MIN(B1:Z1)) Normal ENTER will do -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula needed
Hi,
If the first 1xn range is in A1:A100, and the second (i.e., nx1 ) range is in B1:Z1, =SUM(A1:A100)*MIN(B1:Z1) Regards, B. R. Ramachandran "ZipCurs" wrote: Thanks, that did the trick. What if instead of a (2xn) matrix, I have one (1xn) matrix and one (nx1) matrix that I want to select the minimum from. This doesn't seem to work with that. "B. R.Ramachandran" wrote: Hi, If the (1 x n) range data are in Column A, and the (2 x n) matrix data are in Columns B and C, use the following formula: =SUMPRODUCT(A1:A100,IF(B1:B100<C1:C100,B1:B100,C1: C100)) and confirm with CTRL-SHIFT-ENTER. Regards, B. R. Ramachandran "ZipCurs" wrote: I want to perform an sumproduct type of function with on column of data (1 x n) and the minimum value in each row of a 2 x n matrix. Is there a simple formula that will allow me to do this. I know that I could create a new column with the minimums or that I could brute force the math, I am just hoping for something simple since I have to apply it a few hundred times. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula needed | Excel Worksheet Functions | |||
referencing the value of a cell containing an array formula | Excel Worksheet Functions | |||
problem with Array Formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |