Array formula needed

Z

ZipCurs

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
 
B

B. R.Ramachandran

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
 
Z

ZipCurs

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.
 
M

Max

.. What if instead of a (2xn) matrix, I have one (1xn) matrix
and one (nx1) matrix that I want to select the minimum from.
Click to expand...

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
 
B

B. R.Ramachandran

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
 
Top