I have a puzzle to solve and have been trying for a while now.

Each letter of the alphabet is valued between 1 - 26.

I have a list of 20 names. (Some have double letters in them such as
Barrett)

Each name has a known value based on the sum of it's letter values. (eg
Barrett MAY be 3+7+2+2+6+10+10 = 40 but I only know the total, NOT the
individual letter values)

Each value of 1 - 26 can only be used once for A - Z.

Requirement - Assign each letter of the alphabet a value between 1 & 26
to give the correct name value. Each letter value is to be used only
once.

Any help would be GREATLY appreciated.

Truly hoping I'm not doing your homework for you : \

Try this:

For text in A1

B1:
=SUM((COUNTIF(A1,"*"&{"A","B","C","D","E","F","G", "H","I","J","K","L","M","N","O","P","Q","R","S","T ","U","V","W","X","Y","Z"}&"*")0)*{1,2,3,4,5,6,7, 8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,2 5,26})

OR....
B1:
=SUMPRODUCT((COUNTIF(A1,"*"&CHAR(ROW(INDIRECT("$A$ 1:$A$26"))+64)&"*")0)*ROW(INDIRECT("$A$1:$A$26")) )

Does that help?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

I have a puzzle to solve and have been trying for a while now.

Each letter of the alphabet is valued between 1 - 26.

I have a list of 20 names. (Some have double letters in them such as
Barrett)

Each name has a known value based on the sum of it's letter values. (eg
Barrett MAY be 3+7+2+2+6+10+10 = 40 but I only know the total, NOT the
individual letter values)

Each value of 1 - 26 can only be used once for A - Z.

Requirement - Assign each letter of the alphabet a value between 1 & 26
to give the correct name value. Each letter value is to be used only
once.

Any help would be GREATLY appreciated.

No, you're not doing my homework. It's a geocaching puzzle that's had
me stumped for a while.

The choice of a value for a letter is random. I have several names that
have a known value based on the value of each letter. If you can, have
a look at this link for a better explaination

http://www.geocaching.com/seek/cache...a-d95bbd5b7394

All I know for sure is the total of each name. The values of the
letters is what needs to be worked out. I've tried using solver, but
the numbers won't stay as integers, even though I'm specifying that
they should

Regards

Instead of Solver or combinations/permutations
try this approach using simultaneous equations.
To save space here, I have simplified the problem to 10 words
of 7 letters that use the first 10 letters of the alphabet.
The letters were selected at random.
Give each letter a separate cell with Text to Columns Fixed Width
vec3 A B C D E F G H I J vec5 out
A C E A D C F 2 0 2 1 1 1 0 0 0 0 47 8
F E H C J F H 0 0 1 0 1 2 0 2 0 1 34 2
J E J H C A F 1 0 1 0 1 1 0 1 0 2 38 10
E B D I C C G 0 1 2 1 1 0 1 0 1 0 43 4
I A F F J A C 2 0 1 0 0 2 0 0 1 1 52 1
F F C E B H F 0 1 1 0 1 3 0 1 0 0 34 6
I F H A G A D 2 0 0 1 0 1 1 1 1 0 45 7
G J B H E H J 0 1 0 0 1 0 1 2 0 2 26 3
F H B D F J B 0 2 0 1 0 2 0 1 0 1 28 9
J J C H F C I 0 0 2 0 0 1 0 1 1 2 48 5
Name the 10 x 7 matrix of letters ArrA.
Name the 10 x 10 matrix of numbers next to ArrA ArrB
Name the 1 x 7 vector (A thru J) vec3
Name the 10 x 1 vector of sums (47 thru 48) vec5
Use Insert Name Define
Use Tools Options General R1C1 reference style
Fill ArrB with this formula
=SUMPRODUCT(--(arrA R=vec3 C))
The array formula for out (the value assigned to A thru J) is
=MMULT(MINVERSE(arrB),vec5)
When expanding this procedure to 26 letters,
provide for 26 rows and fill the extra rows with zeros.

Thanx Herbert

It allwent into Excel OK and the arrays defined as planned. But the
letter values that reulted were not unique for each letter.

I've sort of got solver workin, but the values won't come out asd an
integer no matter what I do. Which alo=so means I don't get a unique
integer value.

I have heard there is a bug in some versions of Excel and solver. Are
you aware which ones it may be. I can only find reference to the German
& Polish solvers giving ineger problems

Regards
John