scrabble excel template

[aixeric]

Hi,

I'm trying to make an excel workbook that calculates various values and
statistics for games of scrabble. One of the things I want the sheet to
do is to automatically update the sum total for the points for a given
set of letters. For instance, if I type into cell A1 the letters "BAT"
I want cell B1 to output "5", by knowing that the letters have the
following values and summing them: B=3, A=1, T=1. I've tried using the
COUNTIF() function:

=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,3,3,2,1,
4,2,4,1,8,5,1,3,1,1,3,10,1,1,1,1,4,4,8,4,10})

however the function only seems to count unique letter values, and
cannot handle words with two or more instances of one letter. For
example, the word "letter" is given a value of "4", but should be "6",
however only one "t" and one "e" are counted. Any suggestions?

Thanks!

 

[Ron Coderre]

Here's something to get you pointed in the right direction.....

Put this table in G1:H26
A 1
B 3
C 3
D 2
E 1
F 4
G 2
H 4
I 1
J 8
K 5
L 1
M 3
N 1
O 1
P 3
Q 10
R 1
S 1
T 1
U 1
V 4
W 4
X 8
Y 4
Z 10

Then....for a word in A1

This formula calculates its scrabble value
B1:
=SUMPRODUCT(LOOKUP(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1),1)),1),$G$1:$G$26,$H$1:$H$26))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[aixeric]

Thanks, Ron. We've been trying all day to figure this one out. Happy
New Year.

Regards,

eric