Re : Excel Quest of an Array Formula

[TKT-Tang]

Re : Excel Quest of an Array Formula

1. Enter an Excel worksheet ; Update the working range of A1:E30 with a
set of random numbers.

2. Look across row 1 and locate the minimum value(s) amidst A1:E1 ; And
so, the same across every subsequent row until the end of the given
range.

3. It behooves one of an (unenviable) sedentary role to enter the
following formula,

4. =MIN(A1:E1) in cell F1 ; And then, copy down to F30.

5. The useful array of (minimum) values is thus held in the range of
F1:F30.

6. And now, how could that column of formulae be waived ? such that the
useful array (as given) would be computed by A Single Array Formula all
at once (Hint, hint : Excel Automation should be deployed to alleviate
the menial task of sorts).

7. Please share your comments. Regards.

 

[Harlan Grove]

TKT-Tang wrote...

1. Enter an Excel worksheet ; Update the working range of A1:E30 with a
set of random numbers.

2. Look across row 1 and locate the minimum value(s) amidst A1:E1 ; And
so, the same across every subsequent row until the end of the given
range.

3. It behooves one of an (unenviable) sedentary role to enter the
following formula,

4. =MIN(A1:E1) in cell F1 ; And then, copy down to F30.

....

One simple formula in each cell in F1:F30 is optimally efficient.

6. And now, how could that column of formulae be waived ? such that the
useful array (as given) would be computed by A Single Array Formula all
at once (Hint, hint : Excel Automation should be deployed to alleviate
the menial task of sorts).

....

Meaning how could you get an equivalent array for use in some other
formula without using F1:F30? Possible but very inefficient.

If there were no duplicate values within any row,

=MMULT((COUNTIF(OFFSET(A1:E30,ROW(1:30)-1,0,1,5),"<"&A1:E30)=0)*A1:E30,
{1;1;1;1;1})

If there could be duplicate values within rows,

=MMULT((COUNTIF(OFFSET(A1:E30,ROW(1:30)-1,0,1,5),"<"&A1:E30)=0)
/MMULT(--(COUNTIF(OFFSET(A1:E30,ROW(1:30)-1,0,1,5),"<"&A1:E30)=0),
{1;1;1;1;1})*A1:E30,{1;1;1;1;1})

That is, the formula =VAR(F1:F30) and the formula

=VAR(MMULT((COUNTIF(OFFSET(A1:E30,ROW(1:30)-1,0,1,5),"<"&A1:E30)=0)
/MMULT(--(COUNTIF(OFFSET(A1:E30,ROW(1:30)-1,0,1,5),"<"&A1:E30)=0),
{1;1;1;1;1})*A1:E30,{1;1;1;1;1}))

will return the same result.

Why do I feel like I just won someone a [geek] bar bet?