Combinated SUMPRODUCT with LARGE

English
#1 
=SUMPRODUCT( LARGE( A1:A9 + B1:B9; {1.2.3} ) )

a) Is there an alternative to replacing the fixed LARGE positions {1.2.3} by assigning them to variables in three cells?

b.1) Why does the formula ‘=LARGE(A1:A9+B1:B9;1)’ return an incorrect value, while
b.2) ‘=LARGE(A1:A9+B1:B9;2)’ … ‘_;3)’ returns the error “#VALUE!”?
Even though they are part of the “Dutch” formula referenced above?

I cannot understand the coupling of the both formulas therein.

If I split the “Dutch” formula into 3, or rather 6 very simple ones, and add all results of them together, everything is correct. Furthermore, the manual numerical assignment of the value position {1.2.3} in the original formula works if it is also split. This is no great work. The change to, say {2.5.7}, has to be implanted, which isn’t brilliant here when you have to assign externally.

LO-CALC_combinates Sumproduct with Large_v0000_043349.ods (32.7 KB)

#2

Hallo

=SUMPRODUCT(C8:H8+C9:H9;C8:H8+C9:H9>=LARGE(C8:H8+C9:H9;3))

or:

=SUMIFS(C8:H8+C9:H9;C8:H8+C9:H9;">=" & LARGE(C8:H8+C9:H9;3)) #enter as Array-formula

or simply:

=SUM(LARGE(C8:H8+C9:H9;SEQUENCE(3))) #enter as ArrayFormula

and with free choice of the ranks to sum:

=SUM(LARGE(C8:H8+C9:H9;C19:C21)) #enter as ArrayFormula
1 Like
#3

… that’s my unique favorite as a short matrix/array construct. So it needs a closed field for the three valued order of precedence by [C19:C21].

Using the matrix/array funktion
If I subsequently change some of my formulas in my file to a matrix/array structure, all of the previously incorrect ones return their correct values. I had to figure that out before!

Now: I can understand the coupling of the both formulas therein.

#4

AFAICT, the output of non-array formulas in cells C12, C13 and C14 in your file exemplify Implicit intersection of array formulas.

The non-array formula =C8:H8+C9:H9 put in column C returns only one element. Therefore, cell C12 should display that value (rank 1 in the LARGE function), while cells C13 and C14 show the error “#VALUE!” — due to rank 2 and 3, respectively.

1 Like
#5

It returns a correct value. The LARGE function is defined to take a NumberSequenceList, conversion to which is specified to handle either a number, a reference, or a reference list. You pass there not a reference (nor a reference list), but a result of some calculation - which defines an array result; and that array result has to be converted to one of those three items - where it can only be converted to a number by intersection - meaning that the result of “40 + 2”, which is in the same column, is used as that number. Which is the “1st largest” match in that single-number data set. And indeed, the formulas below in the same column, asking for match 2nd and 3rd largest in the same single-number data set, fail.

2 Likes
#6

I’ll summarize the solutions from all your posts here: My question to

  • a) For the matrix/array {1.2.3}, the alternative assignment C20:C22 without a matrix is possible. Multiple assignments to three individual cells are not feasible.
  • b) The formula ‘=SUMPRODUCT’ already calculates within the matrix structure. It replaces the outer curly brackets, so to speak.

I am learning now to difference in ‘implicit intersection of array’ and ‘non implicit’.
Please correct me if I am wrong or add something important!

#7

Not sure about that. See the attached file.
eesaiFLIE.ods (21.8 KB)

So to speak…
Anyway, cf. information provided e.g. in LO Help (Array Functions, SUMPRODUCT), TDF Wiki ( Documentation/Calc Functions/SUMPRODUCT) and ODF specs (Force to array context, SUMPRODUCT).

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.