Writing logic questions in Excel

Question

I want to write a series of conditional statements (rules?) in Excel for a proof of concept before building a more complex rules engine (in Prolog)

The goal is to be able to apply these statements to different bodies of text, scoring each on depending on how many of the rules are triggered.

I've separated articles so that each word is in its own cell. e.g A1 Word 1, A2, Word 2, A3 Word 3 etc

Example logic statements.

IF "football" resides within 5 words either side of "american" THEN it gets a score of +5

IF "hockey" reside within 5 words of either side of "field" then it gets a score of +10

IF "hockey" reside within 5 words of either side of "ice" then it gets a score of +20

Can this be easily done? Someone has mentioned nested if error vlookups but this is beyond my understanding.

Alternatively, I've explored looking at this in other languages but I can't code plus for the moment other parts of the project work in Excel so I'm kind of tied to it. Those parts do involve macros though so would be open to exploring that option for this.

How would you achieve this in VBA?

Thanks

you need to look at how you are defining a word for one thing. Does it actually have to exist in a dictionary, or is it simply a combination of characters (alpha and numeric) that has a space on either side? If its defining spaces you could start by counting how many spaces are before and after each word to see if there are even 5 words either side. you can then check to see if your word start greater than or less than the 5th space either side of the target word. easier to do in VBA but not quite impossible with formulas. just really ugly. possible multiple intermediate cells. — Forward Ed, Aug 09 '17 at 12:03
take a look at the following functions, FIND, SEARCH, AND, OR, IF — Forward Ed, Aug 09 '17 at 12:06
when you say you have separated each word, which words? where is the sentence you ae searching in? 2-3 lines of sample data may be helpful. — Forward Ed, Aug 09 '17 at 12:07
Pure Excel sounds like the perfect way *not* to do this. Any other technologies you're familiar with? It's possible, but it would require some *very* tedious formulas in Excel, of the kind you'd possibly need to laboriously rewrite if you added a column. Conversely, in almost any programming language this is much easier. In fact, I'm not sure you shouldn't just go ahead and build your prototype in Prolog directly; even if you need to learn it, replicating the logic in Excel first seems like a waste of effort since none of the complexity really transfers. — Jeroen Mostert, Aug 09 '17 at 12:07
Another option is to leverage existing technologies that can do queries like this without any programming, like the `NEAR` operator of [SQL Server's full-text search](https://learn.microsoft.com/sql/relational-databases/search/search-for-words-close-to-another-word-with-near), which is supported even in free editions of SQL Server (though [possibly not from the UI](https://stackoverflow.com/questions/10407337/)). — Jeroen Mostert, Aug 09 '17 at 12:13

score 1 · Accepted Answer · answered Aug 09 '17 at 18:58

I would imagine this could be cleaned up and refactored, but some quick and dirty vba to do this as a UDF would be:

Function word_proximity_test(sentence As String, word1 As String, word2 As String, distance As Integer) As Boolean
    'Dim a variant for an array
    Dim words As Variant
    'Dim a string for each array element
    Dim word As Variant
    'Dim some integers for holding the positions of word1 and word2 when found
    Dim word1pos As Integer, word2pos As Integer
    'Dim integer to track position in array
    Dim arrPos As Integer

    'Split the sentence into the array
    words = Split(sentence, " ")

    'Iterate the words array
    arrPos = 1
    For Each word In words

        'Test for word1
        If word = word1 Then
            'set the position for word1
            word1pos = arrPos
            'Test if the word2 has been found and if the distance between them is within the distance specified
            If word2pos >= 1 And word1pos - word2pos <= distance Then
                'Return true and exit
                word_proximity_test = True
                Exit Function
            End If
        End If

        'Test for word2
        If word = word2 Then
            word2pos = arrPos
            If word1pos >= 1 And word2pos - word1pos <= distance Then
                word_proximity_test = True
                Exit Function
            End If
        End If


        'increment arrPos
        arrPos = arrPos + 1
    Next word
End Function

Drop that in a new module on the workbook (save the workbook) and then you can use it in a cellformula like:

 =word_proximity_test(A1, "football", "american", 5)

Which will return true or false if A1 has the words football and american within 5 words of each other.

Wow! Thanks, that looks like it will work a treat. I'll try it out and comment on the findings. — FJ1993, Aug 09 '17 at 19:48

Writing logic questions in Excel

1 Answers1