Solved

Assigning for a missing score

Posted on 2011-02-22
8
271 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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 …

910 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

24 Experts available now in Live!

Get 1:1 Help Now