Home » Digits With Sum Equal to Length

Digits With Sum Equal to Length

Find the min, max and count of n digit numbers where n = sum of digits Ex. if digits = 3, one such number is 201 where 3 = 2 + 0 + 1

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

Solving the challenge of Digits With Sum Equal to Length with Power Query

Power Query solution 1 for Digits With Sum Equal to Length, proposed by John V.: 
let
 S = Excel.CurrentWorkbook(){0}[Content],
 R = Table.AddColumn(S, "R", each
 let
 d = [Digits] - 1, b = Number.Power(10, d)
 in
 Table.FromColumns({{b + d}, {b + b * d}, {Number.Combinations(2 * d, d)}})
 )
in
 Table.Combine(R[R])

Blessings!
Power Query solution 2 for Digits With Sum Equal to Length, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.Combine(
    Table.AddColumn(
      Source, 
      "A", 
      (x) =>
        let
          a = {0 .. x[Digits]}, 
          b = List.Repeat({a}, x[Digits]), 
          c = Table.FromRows({b}), 
          d = List.Accumulate(
            List.Skip(a), 
            c, 
            (s, c) => Table.ExpandListColumn(s, "Column" & Text.From(c))
          ), 
          e = List.Select(Table.ToRows(d), each List.Sum(_) = x[Digits]), 
          f = List.Transform(e, each Number.From(Text.Combine(List.Transform(_, Text.From)))), 
          g = List.Select(f, each Text.Length(Text.From(_)) = x[Digits]), 
          h = Table.FromColumns({{g{0}}, {List.Last(g)}, {List.Count(g)}}, {"Min", "Max", "Count"})
        in
          h
    )[A]
  )
in
  Sol
Power Query solution 3 for Digits With Sum Equal to Length, proposed by Luan Rodrigues: 
let
 fx = (n)=>
 [
 ge = List.Buffer(List.Generate(
 ()=> [z = Number.From(Text.PadEnd(Text.From(n),n,"0"))/n ] ,
 each Text.Length(Text.From([z])) = n,
 each [z = [z] + 1 ],
 each [z]
 )),
 res = List.Select(ge, (x)=> List.Sum(List.Transform(Text.ToList(Text.From(x)),Number.From)) = n ),
 tab = hashtag#table({"Min","Max","Count"},{{List.Min(res),List.Max(res),List.Count(res)}})
 ][tab],

 Fonte = Tabela1,
 resul = Table.Combine(Table.AddColumn(Fonte, "Personalizar", each fx([Digits]))[Personalizar]) 
in
 resul
Power Query solution 4 for Digits With Sum Equal to Length, proposed by Ramiro Ayala Chávez: 
let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Fx = (x) =>
    let
      a = x, 
      b = Number.Power(10, a - 1), 
      c = Number.Power(10, a - 1) * a, 
      d = {b .. c}, 
      e = List.Transform(d, Text.From), 
      f = List.Transform(e, each Text.ToList(_)), 
      g = List.Transform(f, each List.Transform(_, Number.From)), 
      h = List.Select(g, each List.Sum(_) = a), 
      i = List.Transform(h, each List.Transform(_, Text.From)), 
      j = List.Transform(i, each Text.Combine(_)), 
      k = List.Transform(j, Number.From), 
      l = List.Min(k), 
      m = List.Max(k), 
      n = List.Count(k), 
      o = {l} & {m} & {n}
    in
      o, 
  p = Table.AddColumn(S, "Min", each Fx([Digits]){0}), 
  q = Table.AddColumn(p, "Max", each Fx([Digits]){1}), 
  Sol = Table.AddColumn(q, "Count", each Fx([Digits]){2})
in
  Sol

Solving the challenge of Digits With Sum Equal to Length with Excel

Excel solution 1 for Digits With Sum Equal to Length, proposed by Bo Rydobon 🇹🇭: 
=LET(n,
    SEQUENCE(
        ,
        10
    )-1,
    x,
    9,
    s,
    TOCOL(
        n
    ),
    r,
    REDUCE(s,
    s,
    LAMBDA(a,
    i,
    LET(b,
    a&n,
    l,
    MOD(
        a-1,
        9
    )+1,
    
IF(i=x,
    FILTER(
        a,
        LEN(
            a
        )=l
    ),
    UNIQUE(VSTACK(a,
    TOCOL(--IFS((l<=x)*(l+n
Excel solution 2 for Digits With Sum Equal to Length, proposed by Rick Rothstein: 
=LET(a,
    A2:A6-1,
    HSTACK(a+10^a,
    (a+1)*10^a,
    COMBIN(
        2*a,
        a
    )))
Excel solution 3 for Digits With Sum Equal to Length, proposed by John V.: 
=LET(
    d,
    A2:A6-1,
    b,
    10^d,
    HSTACK(
        b+d,
        b+b*d,
        COMBIN(
            2*d,
            d
        )
    )
)
Excel solution 4 for Digits With Sum Equal to Length, proposed by Kris Jaganah: 
=LET(a,
    A2:A6,
    b,
    a-1,
    HSTACK(10^b+b,
    10^b*a,
    COMBIN(2*(b),
    b)))
Excel solution 5 for Digits With Sum Equal to Length, proposed by Julian Poeltl: 
=LET(Digits,
    A2:A6,
    LET(Digi,
    Digits-1,
    HSTACK(10^(Digi)+Digi,
    (Digi+1)*10^(Digi),
    COMBIN(
        2*Digi,
        Digi
    ))))
Excel solution 6 for Digits With Sum Equal to Length, proposed by Timothée BLIOT: 
=REDUCE({"Min",
    "Max",
    "Count"},
    A2:A6,
    LAMBDA(w,
    v,
    LET(A,
    v-1,
    B,
    10^A,
    S,
    SEQUENCE(MIN(
        B*9,
        10^6
    ),
    ROUNDUP((B)/10^6,
    0),
    B),
    C,
    TOCOL(
        MAP(
            S,
            LAMBDA(
                x,
                IF(
                    SUM(
                        --REGEXEXTRACT(
                            x,
                            "d",
                            1
                        )
                    )=v,
                    x,
                    1/0
                )
            )
        ),
        3
    ),
    VSTACK(
         w,
        HSTACK(
            TAKE(
                C,
                1
            ),
            TAKE(
                C,
                -1
            ),
            COUNT(
                C
            )
        )
    ))))
Excel solution 7 for Digits With Sum Equal to Length, proposed by Pieter de Bruijn: 
=REDUCE({"Min",
    "Max",
    "Count"},
    A2:A6,
    LAMBDA(a,
    b,
    LET(x,
    BASE(
        SEQUENCE(
            b^b-1
        ),
        b,
        b
    ),
    y,
    TOCOL(x/(LEN(
        --x
    )=b)/(MMULT(
        --MID(
            x,
            SEQUENCE(
                ,
                b
            ),
            1
        ),
        EXPAND(
            1,
            b,
            ,
            1
        )
    )=b),
    2),
    VSTACK(
        a,
        HSTACK(
            MIN(
                y
            ),
            --CONCAT(
                EXPAND(
                    b,
                    b,
                    ,
                    0
                )
            ),
            ROWS(
                y
            )+1
        )
    ))))
Excel solution 8 for Digits With Sum Equal to Length, proposed by Nicolas Micot: 
=LET(_nbChiffres;
    A2;
    
_nombres;
    SEQUENCE((_nbChiffres-1)*10^(_nbChiffres-1);
    ;
    1*10^(_nbChiffres-1);
    1);
    
_sommeChiffres;
    LAMBDA(
        l_nombres;
        MAP(
            l_nombres;
            LAMBDA(
                l_nombre;
                SOMME(
                    STXT(
                        l_nombre;
                        SEQUENCE(
                            NBCAR(
                                l_nombre
                            )
                        );
                        1
                    )+0
                )
            )
        )
    );
    
_selectionNombres;
    FILTRE(
        _nombres;
        _sommeChiffres(
            _nombres
        )=_nbChiffres;
        ""
    );
    
ASSEMB.H(
    MIN(
        _selectionNombres
    );
    MAX(
        _selectionNombres
    );
    NBVAL(
        _selectionNombres
    )
))
Excel solution 9 for Digits With Sum Equal to Length, proposed by Ziad A.: 
=ARRAYFORMULA(
    LET(
        a,
        A2:A6-1,
        {1&REPT(
            0,
            a-1
        )&a,
        a+1&REPT(
            0,
            a
        ),
        COMBIN(
            2*a,
            a
        )}
    )
)
Excel solution 10 for Digits With Sum Equal to Length, proposed by Giorgi Goderdzishvili: 
=LET(
_dg,
    A2:A6,
    
_mn,
    "1"&REPT(
        0,
        _dg-2
    )&(_dg-1),
    
_mx,
    _dg&REPT(
        0,
        _dg-1
    ),
    
_cnt,
    COMBIN(2*(_dg-1),
    _dg-1),
    
HSTACK(
    _mn,
    _mx,
    _cnt
)*1)
Excel solution 11 for Digits With Sum Equal to Length, proposed by Diarmuid Early: 
=LET(input,
    A2:A6-1,
    
 _min,
    10^input+input,
    
 _max,
    (input+1)*10^input,
    
 _cnt,
    COMBIN(
        2*input,
        input
    ),
    
 HSTACK(
     _min,
     _max,
     _cnt
 ))

(These formulas all break down if n becomes a 2-digit number)

Solving the challenge of Digits With Sum Equal to Length with Python in Excel

Python in Excel solution 1 for Digits With Sum Equal to Length, proposed by John V.: 
Hi everyone!
One [Python] option could be:
d = xl("A2:A6")[0] - 1; b = 10 ** d
np.array([b + d, b + b * d, comb(2 * d, d)]).T
Blessings!
Python in Excel solution 2 for Digits With Sum Equal to Length, proposed by Abdallah Ally: 
import pandas as pd
# Read an excel file
file_path = 'Excel_Challenge_390_Digit Equal to Sum of Digits.xlsx'
df = pd.read_excel(file_path)
def results(col):
 values = []
 value = 10 ** (col-1)
 while len(str(value)) == col:
 if sum([int(x) for x in str(value)]) == col:
 values.append(value)
 value += 1
 return (min(values), max(values), len(values))
df[['MyMin','MyMax','MyCount']] = df['Digits'].apply(results).tolist()
print(df)

Solving the challenge of Digits With Sum Equal to Length with R

R solution 1 for Digits With Sum Equal to Length, proposed by Konrad Gryczan, PhD: 
Let me show you:
library(tidyverse)
library(readxl)
input = read_excel("Excel/390 Digit Equal to Sum of Digits.xlsx", range = "A1:A6")
test = read_excel("Excel/390 Digit Equal to Sum of Digits.xlsx", range = "A1:D6")
res = input %>%
 mutate(inputs = Digits - 1,
 Min = 10^(inputs) + inputs,
 Max = (Digits) * 10^(inputs),
 Count = choose(2 * (inputs), inputs)) %>%
 unnest() %>% 
 select(-inputs)
identical(res, test)
# [1] TRUE
R solution 2 for Digits With Sum Equal to Length, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
input = read_excel("Excel/390 Digit Equal to Sum of Digits.xlsx", range = "A1:A6")
test = read_excel("Excel/390 Digit Equal to Sum of Digits.xlsx", range = "A1:D6")
compute = function(number) {
 df = expand.grid(rep(list(0:number), number)) %>%
 mutate(sum = rowSums(.)) %>%
 filter(sum == number, Var1 != 0) %>%
 select(-sum) %>%
 unite("NO", everything(), sep = "", remove = TRUE)
 
 summary = df %>%
 summarise(Min = min(NO) %>% as.numeric(),
 Max = max(NO) %>% as.numeric(),
 Count = n() %>% as.numeric()) 
 
 return(summary)
}
result = input %>%
 mutate(summary = map_df(Digits, compute)) %>%
 unnest(summary)

&&&

Leave a Reply