Solved

Assigning for a missing score

Posted on 2011-02-22
8
270 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now