?
Solved

Assigning for a missing score

Posted on 2011-02-22
8
Medium Priority
?
276 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 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