Combinated SUMPRODUCT with LARGE

[koyotak]

De drie hoogste waarden uit een reeks halen

=SOMPRODUCT(GROOTSTE(B4:M4~C34:M34;{1.2.3}))

=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)

[karolus]

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
 

[koyotak]

@karolus

=SUM(LARGE(C8:H8+C9:H9;C19:C21))

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

[cwolan]

@koyotak

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!

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.