Learning English

Members of my family are non native speakers of English … well, most of them can’t speak a word of it. Two of them are identical twins aged 13 and whilst their school results are not fantastic I have said to them that at least we can try to make them number one in their English lessons. 😉

They know the English alphabet, they can spell and say words, they just don’t know how to connect everything together. I will report back in a year and let you know how things have gone.

In the meantime I started by using Excel to generate some exercises. I have created two files so far:

• Flash cards with using the words in a sentence
• Word search
• Anagrams

Flash Cards with Using the Words in a Sentence

There is nothing especially interesting here as I could have prepared the materials using Word or PowerPoint. I will leave this one alone!

Word Search

I have tested the word search exercise already and they did it in about 15 minutes each:

Since we live in Thailand I used words that were familiar to them: there will be some that you probably don’t know, however.

This is how the file works. I created the 20 x 20 grid and made the columns all the same size. I put a border round each cell to make the whole thing easier to read than without!

I decided on my list of 12 words … why 12? Well, because I thought it was a good start.

I then typed the words into the grid:

The words go in different directions as you can see and you can type them anywhere you want.

The question now is, how to fill in the rest of the grid? I could have done it manually but how boring would that have been? So I created a new blank grid and in the top left cell I created this formula:

=IF(A1=””,VLOOKUP(RANDBETWEEN(1,26),$W$22:$X$47,2,0),A1)

Where $W22:$X$47 refers to a lookup table, the start of which is as follows:

The English alphabet!!

I used the RANDBETWEEN() function to give me a random basis for letter selection. That means any one of the 26 letters can occupy any one of the previously empty cells in the grid.

Notice, by starting with =IF(A1=”” … ) I have simply told Excel, if cell A1 is empty put a randomly assigned letter in there, otherwise put whatever is already in cell A1 in the new cell.

It works and at this stage I have not tried to automate the assignment of words to the grid from my chosen list and I probably won’t do that. After all, typing 12 words letter by letter takes me only a minute. The rest is automatic apart from thinking of the words themselves.

Download my file from this link … word_search … and please notice, this is a work in progress. I have not finished it by any means but the basic engine is there!

Anagrams

An anagram is just the rearrangement of letters in a word, like this:

duncan = undnac = nacnud = andnuc

Any one of those three rearrangements is an anagram, they all are.

Anagrams are used by all sorts of people in all sorts of ways. What I wanted to do was to create a file that would generate an anagram authomatically. I looked on the internet and found a few files using various methods but I thought they were al too complicated or they were doing something more sophisticated than I wanted. All I wanted was to take one word and create one anagram of it:

STEEET

AUCBRPOD

DORO

DEEBROMO

Everything in that list can be found at home!

In my research I found many VBA techniques but didn’t use any of them. This is what I did.

Again I chose a list comprising 12 words and created a grid that I called the Anagram Engine:

What’s happening here?

Across the table I created random number series: one series for each word.

Below the numbers I created the anagram by using the following formula:

=MID($B3,D$3,1)

That is, in the cell immediately below the number 8 in the first row … it is empty which means that there is no letter 8 in my word. In fact, you should be able to see that my word is a six letter word.

Finally, I then concatenated my anagrams like this:

=D4&E4&E4&F4&G4&H4&I4&J4&K4&L4&M4

I put this formula in an empty cell … of course it could go anywhere!

Overall my worksheet looks like this:

I type the word settee, the engine generates the anagram, I concatenate the anagram and create the exercise from it. I will rearrange everything as I work on this file further.

Please Note: I only wanted one anagram of each word and I didn’t mind what that anagram was. If I wanted multiple anagrams, I could enter the same word over and over again, like this:

I fully appreciate that this method will NOT guarantee unique anagrams of my 12 word list. In fact, since we are using chance, or random numbers, we might get the same anagram with just two attempts. Of course, that is highly unlikely but it is possible.

There you are, a very simple way of generating anagrams with the only thing left to say … look at the numbers I say are random in the Anagram Engine and compare them with the table called Original Random Numbers, which is where those numbers came from … you should be able to work out what I did and maybe why!

The file is downloadable from this link … anagrams … Concentrate on the anagrams_demo sheet and again, appreciate, this is a work in progress but it is usable.

If you would like me to work on similar examples, let me know and I will do what I can for you and for everyone.

 

Duncan Williamson