Factorial Problem

[Juggler_IN]

I want to calculate a value (x) using factorials for any given word (w) as per the following logic:

For example,
if w=BOB then, x = 3!/(2!1!) --- BOB has three letters (3!) with B twice (2!) and O once (1!);
if w=ABE then, x = 3!/(1!1!1!) --- ABE has three letters (3!) with A once (1!), B once (1!), and E once (1!);
if w=ABBA then, x = 4!/(2!2!) --- ABBA has four letters (4!) with A twice (2!) and B twice (2!);

Is there a way to execute and output x for any w input?

 

[Phuoc]

if w=BOB then, x = 3?

if w=ABE then, x = 6?

if w=ABBA then, x = 6?

Is that correct?

 

[Juggler_IN]

@Phuoc

if w=ABBA then x=24 (4fact divided by product of 2fact & 2fact).
first two correct.

 

[Phuoc]

if w=ABBA then, x = 4!/(2!2!)

4! = 24

(2!2!) = 2 *2 = 4

24/4 = 6 not 24?

 

[Juggler_IN]

Sorry, my bad. It will be 6.

 

[Phuoc]

Try this

=FACT(LEN(A1))/PRODUCT(FACT(FREQUENCY(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),A1),ROW(INDIRECT("1:"&LEN(A1))))))

 

[Juggler_IN]

Works as a formula, but how it be integrated in a code?

 

[Rick Rothstein]

Here is function that you can use in your code (it will also works as a UDF... User Defined Function... in a worksheet formula as well) that can handle a word (no spaces) of up to 17 letters long .

Function WordFactorial(Word As String) As Long
  Dim X As Long, Product As Long, Lets As String, Letters(65 To 90) As String
  If Len(Word) < 18 Then
    For X = 1 To Len(Word)
      Letters(Asc(Mid(UCase(Word), X, 1))) = Val(Letters(Asc(Mid(UCase(Word), X, 1)))) + 1
    Next
    Lets = Replace(Join(Letters, ""), " ", "")
    Product = 1
    For X = 1 To Len(Lets)
      Product = Product * Application.Fact(Mid(Lets, X, 1))
    Next
    WordFactorial = Application.Fact(Len(Word)) / Product
  End If
End Function

 

[Juggler_IN]

@Rick Rothstein ;

This works for a word; but if instead of a word it is a string, with alphanumeric values, can this be modified?

 

[Juggler_IN]

I got it ... changed 65 to 90 TO 33 to 126.