+ Reply to Thread
Results 1 to 2 of 2

Complicated formula to evaluate multiple strings in cell

  1. #1
    Registered User
    Join Date
    07-15-2012
    Location
    PA, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Complicated formula to evaluate multiple strings in cell

    Hello,

    I had an earlier post similar to this, however as I began scoring I realized I missed an important factor that changes everything. This is going to be a really complicated forumla, and I'm looking for a place to begin more than anything.

    **See attached file.

    This is a simplified version of what I am working with (due to privacy laws). What I need to do is evaluate whether the response in C contains the word in D as well as if it was answered in the order given (not case sensitive). For example A (trial1) would recieve a value "TRUE" next to "dog" and "cat" because they were answered correctly in the order given.

    However I'm not sure how to handle the "order" when they are out of order and not all words were recalled. For example in A (trial 3) "smile" "red" and "cloud" were all recalled in order, but since "New York" was recalled first they would technically be wrong if I assigned a PLACE for each word.

    I'm not even sure if this really makes sense or is possible... So any advice would be appreciated. I'm also willing to do some manual work because each subject has the same number of rows, so once I figure it out for subject 1 I can fill down.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Complicated formula to evaluate multiple strings in cell

    Could you post your example again, with the required results included so we can see what you're expecting from the formula.

    Not even sure if I'm anywhere close to what you're trying to explain above.

    In E1

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In F3

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In G4, Array confirmed with Shift Ctrl Enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    First column finds the position of word in D within the relevant string in C.

    Second column checks that each word was found after the previous.

    Third column returns false if one or more words out of sequence, or true if correct.

    What should happen with spelling errors, like the one in C24?
    Last edited by jason.b75; 08-09-2012 at 03:23 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1