Excel for Practice Exam

[klickoe]

Hello,

Basically, I'm wondering if I could create a practice exam in Excel using a data bank of questions from a separate sheet.

What I was picturing was a section on the first/main sheet to select the topic of questions and how many. Then, Excel would pull the questions randomly from a separate sheet and populate them for the "test taker" to go through.

There are a few of us who have, are, or will be taking the same classes, and it may be beneficial if we could have a series of practice tests.

Changing the number of questions would be nice, as the people going through or have gone through could do ten questions as a refresher, but then someone who hasn't could do a full-blown practice exam of 100 questions. The other thing I would like is to be able to focus on particular questions they would be struggling with. If on the sheet with the databank of questions, they were separated into columns based on the chapter (chapter 1 = column 1: chapter 2 = column 2: etc.). This way, the test taker could select chapter 4, and it would only pull questions from that column or have an "all" option that draws from all columns.


Is this possible in Excel?

 

[etaf]

some one would have to setup all the questions , and all the scenarios data and graphs etc and of course all answers

when i set up exam questions as part of an interview process , the questions got quite complex, with pivot tables and lookup summary dashboards and graphs - quite a lot was setup and had a lot of data filled in for various parts of the exam

Not sure its just a list of 100 questions , it would need scenarios set up with data.

Then you could add the chapters info
and use RAND() to select a random set
Are you still using 2016 version ? - 365 i suspect would provide more functions to do this sort of thing easily

 

[Kaper]

Yes it is possible.

And I don't think it is a good idea, to have each chapter questions in separate column. I'd rather use one column for a chapter designation, next for a question, next for correct answer, few next fro wrong answers etc. So each row is a representation of whole information of one question.

You can do this with different tools available in excel. one of possibilities would be Power Query (get and transform data), next would be VBA, You could also use functions, but in this case, to avoid recalculation probably iterative calculations mode (set up in options) shall be used.

 

[klickoe]

Thank you for the responses guys, and sorry for the late response. I got wrapped up in work and forgot all about this.

Based on your responses, it seems like this would be a bit complex for my current skill level. But thanks for the suggestions, I think I'll take them and work on this as a side project to improve my skillset.

 

[Logit]

Here is something to get you started. Hopefully you can modify it to accomplish your needs.

Dim counter, ans, qcounter

Private Sub Answer1_Click()
Button.Enabled = True
End Sub
Private Sub Answer2_Click()
Button.Enabled = True
End Sub
Private Sub Answer3_Click()
Button.Enabled = True
End Sub
Private Sub Answer4_Click()
Button.Enabled = True
End Sub

Private Sub Button_Click()
If (Answer1.Value = True) Then
    ans = 1
ElseIf (Answer2.Value = True) Then
    ans = 2
ElseIf (Answer3.Value = True) Then
    ans = 3
ElseIf (Answer4.Value = True) Then
    ans = 4
End If
ansacc = CInt(Range("Questions!F" & counter).Text)
If (ansacc = ans) Then
    status.Width = status.Width + 30
End If
Answer1.Value = False
Answer2.Value = False
Answer3.Value = False
Answer4.Value = False
counter = counter + 1
qcounter = qcounter + 1
If qcounter <= 10 Then
While (Range("Questions!G" & counter).Text <> "A")
    counter = counter + 1
Wend
Question.Caption = Range("Questions!A" & counter).Text
Answer1.Caption = Range("Questions!B" & counter).Text
Answer2.Caption = Range("Questions!C" & counter).Text
Answer3.Caption = Range("Questions!D" & counter).Text
Answer4.Caption = Range("Questions!E" & counter).Text
End If
Button.Enabled = False
If qcounter = 11 Then
MsgBox ("Your score is " & 10 * status.Width / 30 & "%")
QForm.Hide
End If
End Sub

Private Sub UserForm_Activate()
counter = 1
Answer1.Value = False
Answer2.Value = False
Answer3.Value = False
Answer4.Value = False
status.Width = 0
Button.Enabled = False
ans = 0
While (Range("Questions!G" & counter).Text <> "A")
    counter = counter + 1
Wend
Question.Caption = Range("Questions!A" & counter).Text
Answer1.Caption = Range("Questions!B" & counter).Text
Answer2.Caption = Range("Questions!C" & counter).Text
Answer3.Caption = Range("Questions!D" & counter).Text
Answer4.Caption = Range("Questions!E" & counter).Text
qcounter = 1
End Sub

Download workbook : Internxt Drive – Private & Secure Cloud Storage