SUM Largest 4 Values in Non-Contiguous Cells of a Row
SUM Largest 4 Values in Non-Contiguous Cells of a Row
Cell B9 formula: Non-Contiguous cells with even intervals – SUM largest 4 values in the 2nd row cells of B2, F2, J2, N2, R2 & V2 (consider 1st cell, skip the next 3 cells). Returns SUM of cells B2, F2, J2 & N2.
———————————————————————————————–
Cell B9 formula: Non-Contiguous cells with even intervals – SUM largest 4 values in the 2nd row cells of B2, F2, J2, N2, R2 & V2 (consider 1st cell, skip the next 3 cells). Returns SUM of cells B2, F2, J2 & N2.
The formula uses “TRANSPOSE(ROW(1:4))” instead of “{1,2,3,4}” which is useful when determining a large number of “Largest” values.
Note: The formula presumes there will be a minimum of 4 values to be considered by the LARGE function, and in case there are less then IFERROR function can be used (to return zero) to avoid getting an error result.
Non-Contiguous Cells of the 2nd Row (B2:X2) are considered – cells may have +ive / -ive values, and blank cells are to be ignored.
Cell B9 formula:
=SUM(LARGE(IF( B2:X2<>””, IF(MOD( COLUMN(B2:X2)- COLUMN(B2)+1,4)=1, B2:X2)), TRANSPOSE(ROW(1:4))))
Explanation – Cell B9 formula (Array Formula – Ctrl+Shift+Enter):
1) MOD(COLUMN(B2:X2)- COLUMN(B2)+1, 4): Using “COLUMN(B2:X2)- COLUMN(B2)+1” as the number argument of the MOD function returns an array of numbers for each cell in the range B2:X2, from 1 to 23 – {1,2, 3,4, 5,6, 7,8, 9,10, 11,12, 13,14, 15,16, 17,18, 19,20, 21,22, 23}. Using 4 as the divisor, the MOD function returns an array of numbers for each cell in the range B2:X2 – {1,2, 3,0, 1,2, 3,0, 1,2, 3,0, 1,2,3,0, 1,2, 3,0, 1,2,3}. Equating this array with 1 (which will refer cells B2, F2, J2, N2, R2 & V2), returns an array of TRUE and FALSE values – {TRUE, FALSE, FALSE, FALSE,TRUE, FALSE, FALSE, FALSE, TRUE, FALSE,..} – TRUE indicates every 4th cell in the range starting with B2 ie. cells B2, F2, J2, N2, R2 & V2.
2) IF(B2:X2<>””, IF(MOD( COLUMN(B2:X2)- COLUMN(B2)+1,4)=1, B2:X2)): using the IF statement with the array of 1) above, returns the values of cells B2, F2, J2, N2, R2 & V2 from the range B2:X2 – {-14,FALSE, FALSE, FALSE,-9, FALSE, FALSE, FALSE,-28, FALSE, FALSE, FALSE, -16,FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, -33 ,FALSE, FALSE}. This is also the array argument of the LARGE function. Using “TRANSPOSE(ROW(1:4))” as the k argument of the LARGE function returns the 4 largest values – {-9,-14, -16,-28}, and using SUM returns their total ie. -67 in cell B9.
3) Using “TRANSPOSE(ROW(1:4))” returns “{1,2,3,4}” and is used as the k argument of the LARGE function to determine the largest 4 values. This option is particularly useful when determining a large number of “Largest” values (say, to determine 12 largest values) wherein “TRANSPOSE(ROW(1:12))” can be used which obviates the need of entering an array of {1,2,3,4, 5,6,7,8, 9,10, 11,12} manually.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formulas:
Cell B8 formula: This performs the same as cell B9 formula ie. Non-Contiguous cells with even intervals – SUM largest 4 values in the 2nd row cells of B2, F2, J2, N2, R2 & V2 (consider 1st cell, skip the next 3 cells). Returns SUM of cells B2, F2, J2 & N2. Uses “{1,2,3,4}” as the k argument of the LARGE function – Array formula (Ctrl+Shift+Enter).
Cell B12 formula: SUM largest 4 values in the 2nd row (B2:X2) where 1st row (B1:X1) = 1. Returns SUM of cells C2, F2, J2 & O2. Uses the AGGREGATE function. Normal, Non-Array formula.
Cell B13 formula: This formula performs the same as cell B12 formula, SUM largest 4 values in the 2nd row (B2:X2) where 1st row (B1:X1) = 1. Uses the LARGE function instead of AGGREGATE. Returns SUM of cells C2, F2, J2 & O2. Array formula (Ctrl+Shift+Enter).
Cell B16 formula: Non-Contiguous cells with even intervals – SUM largest 4 values in 2nd row cells of B2,C2, F2,G2, J2,K2, N2,O2, R2,S2, V2,W2 (consider 1st 2 cells, skip next 2 cells). Returns SUM of cells F2, G2, O2 & S2. Array formula (Ctrl+Shift+Enter).
Cell B19 formula: SUM largest 4 values in the 2nd row (B2:X2) where 1st row (B1:X1) = 1 or 2. Returns SUM of cells D2, G2, O2 & X2. Uses the AGGREGATE function. Normal, Non-Array formula.
Cell B23 formula: Non-Contiguous cells with even intervals: Consider 6 blocks of 2 cells each (consider 1st 2 cells, skip next 2 cells) in the 2nd row – B2,C2 & F2,G2 & J2,K2 & N2,O2 & R2,S2 & V2,W2. Compute Largest of the 2 values from each Block – 6 values. Then Return the Largest 4 of these 6 values. Returns SUM of cells B2, G2, O2 & S2. Array formula (Ctrl+Shift+Enter).