Solved

Assigning for a missing score

Posted on 2011-02-22
8
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

688 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