Array formula needed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ZipCurs - 17 Dec 2005 02:27 GMT

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 to this Message

B. R.Ramachandran - 17 Dec 2005 05:43 GMT

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

> 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
[quoted text clipped - 3 lines]
>
> Thanks

Reply to this Message

ZipCurs - 17 Dec 2005 12:40 GMT

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.

> Hi,
>
[quoted text clipped - 13 lines]
> >
> > Thanks

Reply to this Message

Max - 17 Dec 2005 13:42 GMT

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

Reply to this Message

B. R.Ramachandran - 17 Dec 2005 14:16 GMT

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

> 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.
[quoted text clipped - 17 lines]
> > >
> > > Thanks

Reply to this Message