CODEWITHSUNDEEP
google-sheetslambdafactgoogle-sheets-formula
Osm
Osm

Reputation: 2891

How Many Ways are There to Order the Letters of a Word using google sheets

Determine the number of distinct ways to arrange the letters for each word in the range A1:A18.

Input Desired Output
arrangement 2494800
sequence 6720
succession 302400
organization 29937600
disposition 1663200
structure 45360
system 360
series 180
grouping 20160
classification 1816214400
categorization 5448643200
codification 19958400
systematization 27243216000
disposal 20160
form 24
layout 720
array 30

For example the word arrangement

Formula:

                   n!
nPr =      -------------------
           (n1! n2! . . . nr!)

In the (nPr) permutation equation, place the values retrieved from the term arrangement.

                   11!
nPr =    ----------------------- 
         (2! 2! 2! 1! 2! 1! 1! )

    1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x 11
=  ---------------------------------------------
   {(1 x 2) (1 x 2) (1 x 2) (1) (1 x 2) (1) (1)}

nPr of word arrangement = 2494800

As a result, the letters in the word arrangement may be arranged in 2494800 distinct ways.

Upvotes: 1

Views: 48

Answers (2)

TheMaster
TheMaster

Reputation: 50697

Factorial can be easily found with FACT. Unique letter count is a bit complicated, but once we split the string, we can use QUERY to group by and count. Once count is found, we divide the factorial of LEN of string by the product of factorial, just as @Osm did in this answer.

=ARRAYFORMULA(
  BYROW(
    A2:INDEX(A2:A,COUNTA(A2:A)),
    LAMBDA(
      row,
        FACT(LEN(row))
        /PRODUCT(
          FACT(
            QUERY(
              TRANSPOSE(
                SPLIT(
                  REGEXREPLACE(row,"\B","🔘"),
                "🔘",1)
              ),
             "Select count(Col1) group by Col1 label count(Col1) ''")
            )
         )
     )
  )
)

Upvotes: 2

Osm
Osm

Reputation: 2891

Using this formula:

=ArrayFormula(IFERROR(BYROW(QUERY({A2:A}, " Select Col1 Where Col1 <> '' "), LAMBDA(rw,
 LAMBDA(v,
 FACT(LEN(v))/
 PRODUCT(BYROW(
 LAMBDA(s, COUNTIF(s,"="&UNIQUE(TRANSPOSE(s))))
 (Iferror(split(regexreplace(v,"(.)","$1​"),"​"),"")), LAMBDA(rg, FACT(rg)))))(rw))),""))

enter image description here

Simplified like this:

factorial of (Length of the (Range)) / Product of factorial of each sum of unique letter count

Used formulas help
ARRAYFORMULA - IFERROR - BYROW - QUERY - LAMBDA - FACT - LEN - PRODUCT - COUNTIF - UNIQUE - TRANSPOSE - SPLIT - REGEXREPLACE

Upvotes: 2

Related Questions