Automated Question Writer for Teachers!

Automation is more than just a electronic process driven by microcontrollers or PLCs. It's getting a machine to do you work for you. Your computer is that workhorse that can do a lot of that for you, particularly when coupled up with something like Excel.

Part of the joy of teaching is the process of watching students key-in and strive to learn. But it can also be incredibly exhausting, as students continually ask you for additional resources. You need a critical mass of questions, and they also need to be accurate so that no one gets misled by them. I automate that as much as possible.

I teach as part of the apprenticeship training process for electricians. So we are working with a lot of mathematically solved word problems. Over the years, I've learned to rely on Microsoft Excel and formulas to accurately calculate values for those. But you still need to create the questions themselves.

Over this last year, I’ve figured out how to make Excel generate not only the correct answer, but also automatically write the question stem itself. If you look at the picture, you’ll see the easy workflow a sheet like this generates.

1. Each sheet has a number of drop-down menus that you can use to select values for a question.
2. The Question stem is generated in one box based on those
3. A correct answer is generated, as well as 3 mathematically logical ones both higher and lower to use as distractors. (This way you can delete 3 distractors, but still have a standard 4-question multiple choice. And you can set it up so that the answer is anywhere from A to D.
4. You just copy all those boxes, then paste that as “Text Only” into Microsoft word, and then format as a multi-level list.

Although the sheet build takes about an hour to build, you can use it to generate a ton of unique and accurate questions after that. It’s got a lot of steps to build a sheet like this, but it is an easy build. So let’s get started…

Computer

Microsoft Excel

Microsoft Wrod

I start by writing out a generic question stem in Word. I write the values I can change as red. Then I write out a list of questions I could ask about the described question.

You can see the example I am using in the picture. It is for a three-phase electrical power system. I will define 3 things about the source/alternator end (connection, voltage type, and voltage value). I will also define 3 things about the load end (impedance, connection, and power factor). Then I have a list of mathematical questions I can ask about those.

You could easily adapt this to any sort of practice word question like the math one shown…

I take the 6 things I need to define (see previous step), and create a set of labels for that data. I will make drop-down menus and fillable boxes below this. These are just words in boxes, with a nice colour fill.

Three of my variables are simple text types. They will define my systems on both the load and source as being either ‘Delta’ connected or ‘Wye’ connected, and my referred to voltage as either ‘Line’ or ‘Phase.’ In each box, I want to create a set of those predefined options via a drop-down menu.

1. Select a box.
2. Go to the “Data” tab, then select “Data Validation”.
3. In the pop-up, change the Allow setting to “List” type.
4. Lastly, put in the values (words in this case) separated by commas as the ‘Source’. Use lowercase, as we will be using these inside a sentence later.

Two of my variables are limited number types. My power factor can vary between 0 to 100%, and my resistance will be between 0 to 10,000 Ohms. I want to limit those boxes between those values, as well as provide some instructions.

1. Select a box
2. Go to the “Data” tab, then select “Data Validation”.
3. In the pop-up, under change the ‘Allow’ to “Whole Numbers”
4. Set the maximum and minimum values
5. Go one tab over inside the pop-up and add an input message.

Alright, time to up our game and get a little more complicated. Remember that ‘system’ type of selection we talked about (Wye/Delta)? There are only certain types of voltages that get used with each here. These are further defined based on whether they are Line or Phase values. (Mine are limited to the standard Canadian system values.)

We need to make the last drop-down only display the proper options. So that means we will need to refer to values we will store in other sections of our table (Ranges). We will also need to make a conditional statement.

1. I start by putting the proper values into boxes N4 to Q7. This creates 3 horizontal ranges.
2. I create a drop down box with a list type (see Step 3), but this time I will use a formula as the source.
3. I create an IF statement that will follow this logical path.
   1. IF this is a Wye AND Line system, then use the range O5 to Q5.
      1. If that is not correct, run another IF statement.
         1. IF it is Wye AND Phase, use range O4 to Q4.
         2. If that is not correct it must be delta so then use range O6 to Q6

It is complex, but it will make sure that only the correct values are there to select from.

Here is the formula. =IF(((B6="wye") * (C6="line")),O5:Q5,IF(((B6="wye") * (C6="phase")),O4:Q4,O6:Q6))

Remember that question we defined in step 1? This is where you need to refer back to that. We are going to create a formula that will take the values from our drop-down menus and put them into long sentence with the rest of those words.

Select a box. This is where we will use the CONCATENATE function, which strings together anything you want. We will use it to string words (surrounded by quotation marks) along with the values in each of our drop-down boxes. It is fantastic, although you need to remember to place leading or trailing spaces under the quotation marks and commas between items. Here is the formula I used.

=CONCATENATE("A ",B6,"-connected alternator has a ",C6," voltage of ",D6,"V. The balanced three-phase load connected to this alternator consists of three ",H6,"-Ohm impedances, connected in ",G6," that are operating at an ",I6,"% lagging power factor. Calculate the")

We need to make one final drop-down to select the question we want to ask. So we will write out each question in a vertically descending list. (Range)

I create a drop down box with a list type (see Step 3), but this time we will use that Range(N12:N20 in this case) alone as the source.

We will also add this question box to the end of our concatenate sentence, so that the entire word problem is there.

Now we need to calculate all the answers, so we can select from them. We will do that two boxes over from the range of questions.

We will use simple IF statements to sort out which formula to use, and then inserted math formulas for that. I won’t go into detail on each as they are standard "Root-3" formulas used to do 3-phase calculations, but the basics are that I'm checking cells with IF/AND statements. So, here is the list of questions and the formulas.

• Phase voltage of the alternator =IF(((C6="line") * (B6="wye")),(D6/(SQRT(3))),D6)
• Line voltage of the system =IF(((C6="phase") * (B6="wye")),(D6*(SQRT(3))),D6)
• Phase voltage of the loads =IF(((G6="wye")),(P13/(SQRT(3))),P13)
• Phase current of the alternator =IF(B6="delta",P16/SQRT(3),P16)
• Line current of the system =IF(G6="delta",P17*SQRT(3),P17)
• Phase current of the loads =P14/H6
• VA of the system =P13*P16*SQRT(3)
• Watts of the system =P18*(I6/100)
• VARs of the system =SQRT((P18^2)-(P19^2))

We are going to do a “Match&Index” to get the correct data fronted for us.

The match function will have us match the “question asked” in the range where we stored that data. It will give us the row that “question asked” is found in.

The Index function will take that row value, and then find it and reference it with a fixed column in an array. In this case, it is taking Row 5 (our Match value) and intersecting that with Column 3.

We are almost done. We are now going to take that answer and port it over as our correct and rounded answer. We will also use Concatenate one more time to add a space and asterix behind it.

Once that is in, we will reference our results cell and create 3 calculated values both greater and less than it. I place constants in the boxes next to each result that I can multiply with or divide with to create a logically larger or smaller number. Last of all, I take the entire range and drop the decimal down to a single decimal.

I like to identify my values clearly and often share my sheets around. So I use color coding so others can understand it, add instructions directly into the sheet, and protect the sheet so the formulas don’t get lost.

1. Color code and add instructions.
2. Select the whole sheet, right-click anywhere, and then select Format Cells
3. Go to the protection tab. Then click the through the boxes until both show a check mark. This will lock all cells, and hide all formulas. Hit the OK
4. Now left-click into any one of the red boxes. These will all need to be unlocked in order to change the values.
5. Hold down the CTRL key and select all the rest of red boxes. Right-click in one and select Format Cells. Go to the protection tab. Then click the through the Lock until it blank. This will unlock that box and allow us to use the drop-down menu. Hit the OK.
6. Now we will lock the sheet. Go to the Review tab, and select Protect sheet. Enter and confirm the password, and you are done!

Usage is simple.

1. Make a selection in each drop-down menu, and put a value in all other red boxes.
2. Select all the yellow boxes, and use CTRL+C to copy them.
3. Open Word, and right-click in that document
4. Use Paste as “Text Only”.
5. Format as a multi-level list. Select all the answers and use tab to move them over
6. Delete the distractors you don’t want.

Repeat, and make a ton of practice questions!