Solved

Assigning for a missing score

Posted on 2011-02-22
8
272 Views
Last Modified: 2012-05-11
I have a worksheet with spelling scores and I have a dilemma.  I want to be able to deduct a point if a letter is present in a word.  The word is 'decorator' and its base word is decorate - the e is dropped in spelling decorator.  I need to check that the 8th letter is not an 'e' but an o and assign a point if it is an o AND the word is spelled correctly.  See attached file
upload.xls
0
Comment
Question by:Dier02
8 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34958688

You may use this formula:

=IF(AND(MID(C31,8,1)="o",C31="decorator"),100,0)

100 is the points you assigned
0 means no point at all.

Sincerely,
Ed
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34958728
If the word is spelled correctly then the 8th letter is already known to be correct.

So you are left with a total score add if the word is spelled correctly or some deductions if not. And it looks like the deductions are based on how far off the spelling is from the correct spelling. Am I correct?

Kevin
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34958755
...an o AND the word is spelled correctly...

If the word is spelled correctly the question of e versus o doesn't arise so you would just compare B31 with C31.

If you mean you want to assign a point if  8 is 'o' OR the word is spelled correctly then  (it's none of my busuiness but that seems unfair on those who DID spell it correctly; 'ooooooooo' scores a point but 'decorater' doesn't?)  you would have something like..

=if(OR(Trim(B31)=C31, mid(trim(B31),8,1)="o") , 1, 0)
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34958831
I think you will be better off with a more straightforward scoring system that lists the possible spellings and the scores associated with each rather than trying to look for specific sequences of characters at specific locations. In the attached example I list the correct spelling and the associated points, then, progressing across the worksheet, each possible spelling that can earn partial points. I incorporated wildcard character (? for a single character and * for any number of characters) for additional flexibility. The first match working across from left to right is the score used.

Kevin
Q-26841153.xls
0
 

Author Comment

by:Dier02
ID: 34959066
I think the simplest way is to give a point in that column for 'tor' in retrospect.
0
 

Author Comment

by:Dier02
ID: 34959089
Your system looks interesting Zorvek but I don't really understand what you have done.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 34959237
What I am doing is looking at whole word solutions rather than bits and pieces which can be very difficult to inventory. For example, if you give a positive score for "tor" in retrospect, for which positions do you consider awarding that point? What if they misspell another part of the word but get the tor in the right place? Or are you really looking for "tro"? You can begin to see that the possibilities are endless.

Therefore I suggest listing possible whole word spellings for which you will consider points. In my solution you can still look for specific sequences of characters:

retorspect

will look for "tor" with the rest of the word spelled correctly.

??tro?????

will look for "tro" in positions 3 through 5 in any 10 character word.

*tro*

will look for "tro" in any length word with any spelling.

In my workbook I list the possible scored spellings from left to right in that row. The first match gets the points in the next column to the right. You can have as many or as few possible scored spellings as you desire. The last entry is a catchall resulting in zero points.

Kevin
0
 

Author Closing Comment

by:Dier02
ID: 34959308
Thank you.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question