Home » Build Cumulative Triangle Grid

Build Cumulative Triangle Grid

Generate this triangle where bottom row starts with 1 and all rows contain numbers which are cumulative sum from 1 to A1. So, for A1 = 10 => 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 Cumulative sum => 1, 3, 6, 10, 15, 21, 28, 36, 45, 55 The rows are constituted of these numbers.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 556
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Build Cumulative Triangle Grid with Power Query

Power Query solution 1 for Build Cumulative Triangle Grid, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Column1]{0}, 
  Lista = {1 .. Source}, 
  Acum = List.Transform(Lista, each List.Sum(List.FirstN(Lista, _))), 
  Zip = List.Zip({Acum, List.Reverse(Lista)}), 
  Lista2 = List.Reverse(
    List.Transform(
      Zip, 
      each 
        let
          a = List.Repeat({null}, Source - _{1}), 
          b = List.Repeat({_{0}}, _{1} - 1), 
          c = {_{0}}, 
          d = a & b & c & b & a
        in
          d
    )
  ), 
  Sol = Table.FromRows(Lista2)
in
  Sol
Power Query solution 2 for Build Cumulative Triangle Grid, proposed by Abdallah Ally: 
let
  triangle = (n) =>
    [
      a = 2 * n - 1, 
      b = List.Accumulate({1 .. n}, {}, (x, y) => x & {List.Last(x, 0) + y}), 
      c = List.Transform(
        List.Zip({{1 .. n}, List.Reverse(b)}), 
        each [
          p = List.Repeat({null}, Number.IntegerDivide(a - 2 * _{0} + 1, 2)), 
          q = p & List.Repeat({_{1}}, 2 * _{0} - 1) & p
        ][q]
      )
    ][c], 
  Result = Table.FromRows(triangle(10))
in
  Result
Power Query solution 3 for Build Cumulative Triangle Grid, proposed by Ramiro Ayala Chávez: 
let
  N = 10, 
  C = List.Count, 
  R = List.Repeat, 
  a = {1 .. N}, 
  b = List.Reverse(
    List.Generate(
      () => [i = a{0}, j = 0], 
      each [j] < C(a), 
      each [i = [i] + a{[j] + 1}, j = [j] + 1], 
      each [i]
    )
  ), 
  Sol = Table.FromRows(
    List.Generate(
      () => [i = 0, j = 1], 
      each [i] < C(b), 
      each [i = [i] + 1, j = [j] + 2], 
      each R({null}, C(b) - [i] - 1) & R({b{[i]}}, [j]) & R({null}, C(b) - [i] - 1)
    )
  )
in
  Sol
Power Query solution 4 for Build Cumulative Triangle Grid, proposed by Meganathan Elumalai: 
let
  Source = 10, 
  TriList = List.Sort(List.Transform({1 .. Source}, (f) => f * (f + 1) / 2), Order.Descending), 
  Result = Table.FromRows(
    List.Transform(
      {0 .. Source - 1}, 
      (f) =>
        List.Transform(
          {1 .. Source * 2 - 1}, 
          each if _ >= Source - f and _ <= Source + f then TriList{f} else null
        )
    )
  )
in
  Result

Solving the challenge of Build Cumulative Triangle Grid with Excel

Excel solution 1 for Build Cumulative Triangle Grid, proposed by Bo Rydobon 🇹🇭: 
=LET(n,A1,MAKEARRAY(n,n*2-1,LAMBDA(r,c,IF(ABS(c-n)
Excel solution 2 for Build Cumulative Triangle Grid, proposed by Rick Rothstein: 
=MAKEARRAY(A1,
    2*A1-1,
    LAMBDA(r,
    c,
    IF((c>A1-r)*(c<=A1+r-1),
    (A1-r+1)*(A1-r+2)/2,
    "")))
Excel solution 3 for Build Cumulative Triangle Grid, proposed by John V.: 
=LET(s,SEQUENCE,IF(ABS(A1-s(,2*A1-1))
Excel solution 4 for Build Cumulative Triangle Grid, proposed by 🇰🇷 Taeyong Shin: 
=LET(
    n,
    A1,
    r,
    SEQUENCE(
        n
    ),
    IF(
        ABS(
            SEQUENCE(
                ,
                n*2-1
            )-n
        )
Excel solution 5 for Build Cumulative Triangle Grid, proposed by Kris Jaganah: 
=LET(a,
    A1,
    b,
    SEQUENCE,
    c,
    b(
        a
    ),
    d,
    b(
        ,
        a*2-1
    ),
    IF((d>a-c)*(d
Excel solution 6 for Build Cumulative Triangle Grid, proposed by Julian Poeltl: 
=LET(N,A1,C,N*(1+(N-1)/2),V,VSTACK(C,DROP(SCAN(C,-SEQUENCE(N,,N,-1),SUM),-1)),IF(MAKEARRAY(N,N*2-1,LAMBDA(A,B,(A+B>N)*(A+B
Excel solution 7 for Build Cumulative Triangle Grid, proposed by Aditya Kumar Darak 🇮🇳: 
=MAKEARRAY(
    A1,
     A1 * 2 - 1,
     LAMBDA(
         r,
          c,
          IF(
              ABS(
                  c - A1
              ) < r,
               SUM(
                   SEQUENCE(
                       A1 - r + 1
                   )
               ),
               ""
          )
     )
)
=MAKEARRAY(A1,
     A1 * 2 - 1,
     LAMBDA(r,
     c,
     IF(ABS(
                  c - A1
              ) < r,
     (
                       A1 - r + 1
                   ) * (A1 - r + 2) / 2,
     "")))
Excel solution 8 for Build Cumulative Triangle Grid, proposed by Timothée BLIOT: 
=LET(A,A1,MAKEARRAY(A,2*A-1,LAMBDA(x,y,IF(AND(A-y
Excel solution 9 for Build Cumulative Triangle Grid, proposed by Hussein SATOUR: 
=LET(a,SEQUENCE(A1),b,SEQUENCE(,A1*2-1),(b<=10+(a-1))*(b>=10-(a-1))*SCAN(SUM(VSTACK(a,A1+1)),A1+2-a,LAMBDA(x,y,x-y))*b^0)
Excel solution 10 for Build Cumulative Triangle Grid, proposed by Oscar Mendez Roca Farell: 
=LET(S,SEQUENCE,n,S(A1),REPT(COMBIN(A1-n+2,2),n>ABS(A1-S(,2*A1-1))))
Excel solution 11 for Build Cumulative Triangle Grid, proposed by Oscar Mendez Roca Farell: 
=MAKEARRAY(
    A1,
    2*A1-1,
    LAMBDA(
        r,
        c,
        IF(
            r>ABS(
                A1-c
            ),
            SUM(
                SEQUENCE(
                    A1-r+1
                )
            ),
            ""
        )
    )
)
Excel solution 12 for Build Cumulative Triangle Grid, proposed by Sunny Baggu: 
=LET(
 _a, SEQUENCE(A1),
 _b, A1 + 1 - _a,
 _c, _b * (_b + 1) / 2,
 _d, TOROW(VSTACK(DROP(_a, -1), _b)),
 IF(_b <= _d, _c, "")
)
Excel solution 13 for Build Cumulative Triangle Grid, proposed by Sunny Baggu: 
=LET(
 _s, SEQUENCE(A1),
 _a, SORT(SCAN(0, _s, LAMBDA(a, v, a + v)), , -1),
 _b, SORT(_s, , -1),
 _c, HSTACK(TOROW(_s), TOROW(DROP(_b, 1))),
 IF(N(_b <= _c), _a, "")
)
Excel solution 14 for Build Cumulative Triangle Grid, proposed by LEONARD OCHEA 🇷🇴: 
=LET(n,
    A1,
    f,
    SEQUENCE(
        n
    ),
    c,
    SEQUENCE(
        ,
        2*n-1
    ),
    s,
    f+c,
    IF((s>n)*(s
Excel solution 15 for Build Cumulative Triangle Grid, proposed by Pieter de B.: 
=LET(x,SEQUENCE(,A1*2-1,-A1+1),y,SEQUENCE(A1),IF(y>ABS(x),SORT(SCAN(,y,SUM),,-1),""))
Excel solution 16 for Build Cumulative Triangle Grid, proposed by ferhat CK: 
=MAKEARRAY(A1,
    A1*2-1,
    LAMBDA(x,
    y,
    IF((y>A1-x)*(y
Excel solution 17 for Build Cumulative Triangle Grid, proposed by Jaroslaw Kujawa: 
=LET(
    a ;
     A1 ;
     
    MAKEARRAY(
        a ;
         a*2-1 ;
        
        LAMBDA(
            r ;
             c ;
             IF(
                 ABS(
                     a-c
                 )-r<0 ;
                  SUM(
                      SEQUENCE(
                          a+1-r
                      )
                  ) ;
                  ""
             )
        )
        
    )
)
Excel solution 18 for Build Cumulative Triangle Grid, proposed by Andy Heybruch: 
=LET(n,A1,
MAKEARRAY(n,n*2-1,
 LAMBDA(_r,_c,
 IFS(
 ABS(_c-10)-_r>=0,"",_r>0,
 REDUCE(0,SEQUENCE(n+1-_r),SUM)))))
Excel solution 19 for Build Cumulative Triangle Grid, proposed by Meganathan Elumalai: 
=LET(n,A1,p,SEQUENCE(n),s,ABS(SEQUENCE(n*2-1,,1-n))+1,a,SORT(p*(p+1)/2,,-1),IF(MAKEARRAY(n,n*2-1,LAMBDA(r,c,IF(r>=INDEX(s,c),r))),a,""))
Excel solution 20 for Build Cumulative Triangle Grid, proposed by Bilal Mahmoud kh.: 
=LET(
    a,
    REDUCE(
        1,
        SEQUENCE(
            A1,
            ,
            0
        ),
        LAMBDA(
            x,
            y,
            VSTACK(
                x,
                HSTACK(
                    CHAR(
                        SEQUENCE(
                            ,
                            A1-y,
                            32,
                            0
                        )
                    ),
                    SEQUENCE(
                        ,
                        y*2+1,
                        REDUCE(
                            0,
                            SEQUENCE(
                                A1-y
                            ),
                            LAMBDA(
                                n,
                                m,
                                n+m
                            )
                        ),
                        0
                    ),
                    CHAR(
                        SEQUENCE(
                            ,
                            A1-y,
                            32,
                            0
                        )
                    )
                )
            )
        )
    ),
    DROP(
        DROP(
            DROP(
                a,
                1
            ),
            ,
            1
        ),
        ,
        -1
    )
)
Excel solution 21 for Build Cumulative Triangle Grid, proposed by Eddy Wijaya: 
=LET(
    
    n,
    A1,
    
    f_rept,
    LAMBDA(
        n,
        v,
        REPT(
            ",",
            n-v
        )
    ),
    
    s,
    SEQUENCE(
        n
    ),
    
    cum_n,
    SORT(
        SCAN(
            0,
            s,
            LAMBDA(
                a,
                v,
                a+v
            )
        ),
        ,
        -1
    ),
    
    l_t,
    DROP(
        REDUCE(
            0,
            s,
            LAMBDA(
                a,
                v,
                VSTACK(
                    a,
                    
                    CONCAT(
                        HSTACK(
                            f_rept(
                                n,
                                v
                            ),
                            
                            LET(
                                
                                m,
                                REPT(
                                    INDEX(
                                        cum_n,
                                        v
                                    )&",",
                                    2*v-1
                                ),
                                
                                LEFT(
                                    m,
                                    LEN(
                                        m
                                    )-1
                                )
                            ),
                            
                            f_rept(
                                n,
                                v
                            )
                        )
                    )
                )
            )
        ),
        1
    ),
    
    DROP(
        REDUCE(
            0,
            l_t,
            LAMBDA(
                a,
                v,
                VSTACK(
                    a,
                    IFERROR(
                        --TEXTSPLIT(
                            v,
                            ","
                        ),
                        ""
                    )
                )
            )
        ),
        1
    )
)
Excel solution 22 for Build Cumulative Triangle Grid, proposed by Ziad A.: 
=MAKEARRAY(A1,A1*2-1,LAMBDA(i,j,IF(AND(j>A1-i,j-A1
Excel solution 23 for Build Cumulative Triangle Grid, proposed by Philippe Brillault: 
=LET(
TRIANG,LAMBDA(n,IF(n=1,1,HSTACK(SCAN(0,SEQUENCE(n),SUM),Triang(n-1)))),
Flip,LAMBDA(t,dir,LET(l,ROWS(t),c,COLUMNS(t),MAKEARRAY(l,c,LAMBDA(i,j,INDEX(t,i+(dir="N")*(l+1-2*i),j+(dir="W")*(c+1-2*j)))))),
IFNA(HSTACK(DROP(Flip(Flip(TRIANG(10),"W"),"N"),,-1),Flip(TRIANG(10),"N")),"")
)
Excel solution 24 for Build Cumulative Triangle Grid, proposed by Songglod P.: 
=MAKEARRAY(A1,2*A1-1,LAMBDA(r,c,IF(AND(r+c>A1,c-r
Excel solution 25 for Build Cumulative Triangle Grid, proposed by Nonbow Wu: 
=LET(n,A1,k,ABS(SEQUENCE(n+n-1)-n),y,n-k,
REPT((n-y+1)*(n-y+2)/2,y>TOROW(k)))
Excel solution 26 for Build Cumulative Triangle Grid, proposed by Nonbow Wu: 
=LET(n,A1,y,SEQUENCE(n),
REPT((n-y+1)*(n-y+2)/2,y>ABS(SEQUENCE(,n+n-1)-n)))

Solving the challenge of Build Cumulative Triangle Grid with Python

Python solution 1 for Build Cumulative Triangle Grid, proposed by Konrad Gryczan, PhD: 
import pandas as pd
import numpy as np
path = "556 Generate Triangle Cumsum.xlsx"
input_value = pd.read_excel(path, usecols="A", nrows=1, header=None).iloc[0, 0]
test = pd.read_excel(path, usecols="B:T", skiprows=1, nrows=10, header=None).values
M = np.full((input_value, 2 * input_value - 1), np.nan)
p = np.cumsum(np.arange(1, 11))
for i in range(10):
 M[i, (input_value - i - 1):(input_value + i)] = p[::-1][i]
print(np.allclose(M, test, equal_nan=True)) #

Solving the challenge of Build Cumulative Triangle Grid with Python in Excel

Python in Excel solution 1 for Build Cumulative Triangle Grid, proposed by Alejandro Campos: 
n = xl("A1")
numbers = list(range(1, n+1))
cumulative_sums = [sum(numbers[:i+1]) for i in range(n)]
cumulative_sums_reversed = cumulative_sums[::-1]
df_corrected = pd.DataFrame('', index=range(n), columns=range(n*2))
for i, value in enumerate(cumulative_sums_reversed):
 start = n - i
 count = 2 * i + 1
 df_corrected.iloc[i, start:start+count:1] = value
df_corrected
Python in Excel solution 2 for Build Cumulative Triangle Grid, proposed by Ümit Barış Köse, MSc: 
input_value = xl("A1")
p = np.cumsum(np.arange(1, input_value + 1))[::-1]
M = np.where(np.arange(input_value)[:, None] + np.arange(2 * input_value - 1) >= input_value - 1,
 np.where(np.arange(input_value)[:, None] + np.arange(2 * input_value - 1) < input_value - 1 + (2 * np.arange(input_value)[:, None] + 1),
 p[:, None], np.nan), np.nan)
df = pd.DataFrame(M).fillna('')

Solving the challenge of Build Cumulative Triangle Grid with R

R solution 1 for Build Cumulative Triangle Grid, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "Excel/556 Generate Triangle Cumsum.xlsx"
input = read_excel(path, range = "A1:A1", col_names = FALSE) %>% pull()
test = read_excel(path, range = "B2:T11:", col_names = FALSE) %>% as.matrix()
M = matrix(NA_real_, nrow = input, ncol = 2 * input - 1)
p = 1:input %>% cumsum()
for (i in 1:10) {
 M[i, (input - i + 1):(input + i - 1)] = rev(p)[i] 
}
all.equal(M, test, check.attributes = FALSE) # TRUE

Solving the challenge of Build Cumulative Triangle Grid with Excel VBA

Excel VBA solution 1 for Build Cumulative Triangle Grid, proposed by Md. Zohurul Islam: 
Sub ExcelBI_Excel_Challenge556()
Dim nx, x, y
Dim a() As String
Dim strText As String
Dim rng As Range
strText = "55-45-36-28-21-15-10-6-3-1"
a = Split(strText, "-")
nx = WorksheetFunction.CountA(a)
y = 0
For x = 1 To nx
 Set rng = Range(Cells(x + 1, 11).Offset(0, -y), Cells(x + 1, 11).Offset(0, y))
 rng = a(x - 1)
 y = y + 1
Next x
End Sub

&&

Leave a Reply