How to multiply arrays and get the largest number in the resultingarray ?

[exceluser]

Is it possible to multiply arrays without summing the results or using
an array entered formula and then determine the largest number in the
resulting array ?

For example, multiply two arrays created from using the INDEX and
OFFSET functions and then LARGE the results.

A B
1 10 20
2 20 30
3 30 40

LARGE($A$1:$A$3*$B$1:$B$3,1) = 1,200

 

[Ms-Exl-Learner]

=SUMPRODUCT((LARGE(A1:A3,1))*(LARGE(B1:B3,1)))
=LARGE(A1:A3,1)*LARGE(B1:B3,1)

 

[Charabeuh]

Hello,

You can use your formula :
=LARGE($A$1:$A$3*$B$1:$B$3,1)

but you should not validate this formula with the key "Enter" but with the
keys "Ctrl+Shift+Enter"
(it is an array formula)






"exceluser" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...

 

[exceluser]

Hello,

You can use your formula :
=LARGE($A$1:$A$3*$B$1:$B$3,1)

but you should not validate this formula with the key "Enter" but with the
keys "Ctrl+Shift+Enter"
(it is an array formula)

"exceluser" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...







- Show quoted text -

Charabeuh,

Thank you for responding.

Unfortunately, as I stated in the original post, the solution
should not involve an array entered formula.

Given the number of cells this formula would be used in, an array
entered formula would cause unnecessarily high processor utilization
during calculation.


Exceluser

 

[Charabeuh]

hello,

You said : "Given the number of cells this formula would be used in, an
array entered formula would cause unnecessarily high processor utilization
during calculation"

Why ?

I tried this formula on two arrays of 1 000 000 items (from A1 to A1000000
and B1 to B1000000)
The result displayed in less than 2 seconds.

Excel 2010 - windows seven - dual core E6420 - 2 Go of ram

You could insert a third column C to calculate the product of A1:An and
B1:Bn and then compute max(Column C1:Cn). But the duration (with my
computer) is the same than the array formula.

 

[exceluser]

=SUMPRODUCT((LARGE(A1:A3,1))*(LARGE(B1:B3,1)))
=LARGE(A1:A3,1)*LARGE(B1:B3,1)

-----------------------
Ms-Exl-Learner
-----------------------






- Show quoted text -

Ms-Exl-Learner,

Thanks for the reply.

Unfortunately, both of those formulas multiply only the largest
numbers.




Exceluser

 

[Charabeuh]

Replace: The result displayed in less than 2 seconds
with: The result displayed in less than 5 seconds

 

[exceluser]

Replace: The result displayed in less than 2 seconds
with: The result displayed in less than 5 seconds

"Charabeuh" <[email protected]> a écrit dans le message de groupe de
discussion : [email protected]...









- Show quoted text -

Charabeuh,

Thanks for the quick response.

The arrays would be created using the INDEX and OFFSET functions.

The OFFSET function is a volatile function meaning that many things
you do in Excel would cause each formula that contains an OFFSET
function to recalculate - even if you didn't change a formula that
contained an OFFSET function.

For a large spreadsheet, this is a problem.

As for array entered formulas, they are to be avoided for large
amounts of calculations, especially when using volatile functions.

I recently replaced most array entered formulas with standard
formulas in a large spreadsheet and managed to cut recalculation time
down from 2.5 hours to 90 seconds.

I appreciate your effort.

It would be nice if Microsoft came up with a function similar to
SUMPRODUCT that would multiply arrays and give an array as a result.






Exceluser

 

[Ms-Exl-Learner]

Sorry I Miss Read Your Post. Try the below:-

=SUMPRODUCT(MAX((A1:A3)*(B1:B3)))

OR

=SUMPRODUCT(LARGE((A1:A3)*(B1:B3),1))

 

[Ziggy]

I don't have the answer but I am looking at the problem differently

I think what he's after is the maximum product of values on the same
line, not tha max of each colum multiplied


1 30 20
2 25 25
3 20 30

He would want 625 as the answer, not 900... I think.

 

[Ziggy]

I don't have the answer but I am looking at the problem differently

I think what he's after is the maximum product of values on the same
line, not tha max of each colum multiplied

1      30     20
2      25     25
3      20     30

He would want 625 as the answer, not 900... I think.

Ms-Exl-Leaner, I tried your formulas and they work great. ☺

 

[Ms-Exl-Learner]

You are welcome and thanks for your feed!