Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with "Headline Counter" spreadsheet...
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 ) ) ) ) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with "Headline Counter" spreadsheet...
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) "Cloy" wrote in message ps.com... 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 ) ) ) ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Big problem with excel recalculating spreadsheet | Excel Discussion (Misc queries) | |||
Linked Spreadsheet Opens | Excel Discussion (Misc queries) | |||
summarize data from one spreadsheet to other spreadsheet | Excel Worksheet Functions | |||
Working spreadsheet highlighting function for Excel 2007 | Excel Worksheet Functions | |||
Spreadsheet merging problems | Excel Worksheet Functions |