UPDATED - How to multiply arrays and get the largest number in theresulting array ?

[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 three arrays created from using the INDEX and
OFFSET functions and then LARGE the results.


A B C
1 1 0 1
2 0 0 2
3 1 1 3


The formula should do the following.

$A$1:$A$3 * $B$1:$B$3 * $C$1:$C$3

which should result in an array consisting of {0,0,3}

from which you perform LARGE({0,0,3},1)

which should result in 3.


Exceluser

 

[Ms-Exl-Learner]

Try this…

=SUMPRODUCT(MAX((A1:A3)*(B1:B3)*(C1:C3)))
OR
=SUMPRODUCT(LARGE((A1:A3)*(B1:B3)*(C1:C3),1))

 

[exceluser]

Try this…

=SUMPRODUCT(MAX((A1:A3)*(B1:B3)*(C1:C3)))
OR
=SUMPRODUCT(LARGE((A1:A3)*(B1:B3)*(C1:C3),1))

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











- Show quoted text -

Ms-Exl-Learner,

Rumba thanks.

Use of SUMPRODUCT seems to be the only way to keep arrays intact
even though the final intent is not to SUM.

The trick with SUMPRODUCT is to make sure you've got your answer
(like using LARGE below) before SUMPRODUCT can sum the array's
contents.

Though the final formula looks a lot different, the
=SUMPRODUCT(LARGE((A1:A3)*(B1:B3)*(C1:C3),1)) idea is what helped make
it work.

It saved me from having to continue staring at the screen with a
dumb look on my face for the next week.



Exceluser

 

[Ms-Exl-Learner]

Nice to here the Tamil Word. You are welcome and thanks for feeding
back.

 

[Ms-Exl-Learner]

We cant able to say that it can be achieved only by using Sumproduct.
The below formula will also do that work, but it is an array
formula's. It requires CNTRL+SHIFT+ENTER to make the formula to work
perfectly and the general enter wont work.

=SUM(MAX((A1:A3)*(B1:B3)*(C1:C3)))
=SUM(LARGE((A1:A3)*(B1:B3)*(C1:C3),1))

Copy and paste the above formula in your cell and press F2 and hit
CNTRL+SHIFT+ENTER and now the formula will be covered by curly braces
{} like the below.

{=SUM(MAX((A1:A3)*(B1:B3)*(C1:C3)))}
{=SUM(LARGE((A1:A3)*(B1:B3)*(C1:C3),1))}

Don't add the Curly braces {} manually.

For avoiding array formula we should go for Sumproduct, since it is a
non array function.

Hope it's clear to you!