Need help with "Headline Counter" spreadsheet...

[Cloy]

Journalism editors have traditionally done "counts" to determine
spacing for a headline.

In short, this means totalling the values of all characters in a
headline. Most lower-case characters are worth 1, but some are worth
only .5 (spaces; punctuation; the letters l, i, f, t, j and the digit
1), some are worth 1.5 (most capital letters) and some are worth 2
(capital M and W).

I've created a quick-and-dirty spreadsheet to break out the headline
character-by-character. Now I need a more elegantly way to compare each
character with the "value list" to determine how much it is worth.

Right now, I'm using a formula with three nested IF statements (see
below). Is there a more elegant way to do this? Maybe with an array or
a list?

I occasionally have to switch the character values (there are several
ways to do counts) and modifying these long complex formulas can make
that challenging.

Any help would be appreciated...

-Cloy

Here's the Excel Formula... formatted for easier reading...

=IF(
##### THE FOLLOWING SECTION EVALUATES FOR .5-value characters ######
OR(
EXACT(B4,"j"),
EXACT(B4,"i"),
EXACT(B4,"l"),
EXACT(B4,"t"),
EXACT(B4,"f"),
EXACT(B4," "),
EXACT(B4,"1"),
EXACT(B4,"!"),
EXACT(B4,","),
EXACT(B4,"."),
EXACT(B4,":"),
EXACT(B4,"-"),
EXACT(B4,";"),
EXACT(B4,$A$17),
EXACT(B4,"'")
),0.5,

IF(
##### THE FOLLOWING SECTION EVALUATES FOR 1-value characters ######
OR(
EXACT(B4,"a"),
EXACT(B4,"b"),
EXACT(B4,"c"),
EXACT(B4,"d"),
EXACT(B4,"e"),
EXACT(B4,"g"),
EXACT(B4,"h"),
EXACT(B4,"k"),
EXACT(B4,"n"),
EXACT(B4,"o"),
EXACT(B4,"p"),
EXACT(B4,"q"),
EXACT(B4,"r"),
EXACT(B4,"s"),
EXACT(B4,"t"),
EXACT(B4,"u"),
EXACT(B4,"v"),
EXACT(B4,"x"),
EXACT(B4,"y"),
EXACT(B4,"z"),
EXACT(B4,"2"),
EXACT(B4,"3"),
EXACT(B4,"4"),
EXACT(B4,"5"),
EXACT(B4,"6"),
EXACT(B4,"7"),
EXACT(B4,"8"),
EXACT(B4,"9"),
EXACT(B4,"0"),
EXACT(B4,"?")
), 1,

IF(
##### THE FOLLOWING SECTION EVALUATES FOR 2-value characters ######
##### ON FAIL, THE FORMULA WILL RETURN 1.5 ####

OR(
EXACT(B4,"M"),
EXACT(B4,"W")
), 2,1.5
)
)

)
)

 

[Bob Phillips]

Cloy,

Create a table of characters and spacing in say M1:N36, like so

j .5
i .5
l .5
t .5
f .5
.5
1 .5
! .5
, .5
.. .5
: .5
- .5
; .5
' .5
a 1.0
b 1.0
c 1.0
d 1.0
e 1.0
g 1.0
h 1.0
k 1.0
n 1.0
o 1.0
p 1.0
q 1.0
r 1.0
s 1.0
t 1.0
u 1.0
v 1.0
x 1.0
y 1.0
z 1.0
2 1.0
3 1.0
4 1.0
5 1.0
6 1.0
7 1.0
8 1.0
9 1.0
0 1.0
? 1.0
M 1.5
W 1.5

Then just use

=MAX(INDEX((EXACT(the_character,M1:M36))*(N1:N36),0))

to lookup a character.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)