Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function for counting how many times a Q is answered wrong
Hi there,
I'm a bit stuck - I've written a fairly simple S/sht which is basically designed to hold the answers to a number of book based multi choice tests. Reason behind it was simply so that the student can enter the answer they think is appropriate without either marking their book, thus meaning they can't repeat that test, or without the need to write out the question numbers repeatedly. So, the actual comparison and "right or wrong" flag is fairly simple, but one extra bit of functionality I'd like to add is to add a running count of how many times a particular question has been answered incorrectly....so that hopefully the tests can be done several times and attention diverted to those topics causing the most problem. My issue is I don't know how to do this, as the standard worksheet functions would reset to zero when the original answer is deleted....I think it might be a macro job, but wouldnt' know where to start....any help gratefully received! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function for counting how many times a Q is answered wrong
Charly,
You could use the an event to record results and to clear the results table when you want to reset. For example, if the student enters answers into column B, and you have the Right or Wrong formulas in column C, then this code will keep a running count of how many times the answer has been answered incorrectly in column H. Copy the code, right click the sheet tab, select "View Code" and paste the code into the window that appears. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address = "$D$1" And UCase(Target.Value) = "CLEAR" Then Application.EnableEvents = False Range("H:H").ClearContents Application.EnableEvents = True Exit Sub End If If Target.Column < 2 Then Exit Sub If Target.Offset(0, 1).Value < "Wrong" Then Exit Sub Application.EnableEvents = False Cells(Target.Row, "H").Value = Cells(Target.Row, "H").Value + 1 Application.EnableEvents = True End Sub To clear the record, type "clear" (without the quote marks) into cell D1 of the same sheet. If you have trouble changing this code to reflect your actual sheet structure, then post back. HTH, Bernie MS Excel MVP "CharlyFarly" wrote in message ... Hi there, I'm a bit stuck - I've written a fairly simple S/sht which is basically designed to hold the answers to a number of book based multi choice tests. Reason behind it was simply so that the student can enter the answer they think is appropriate without either marking their book, thus meaning they can't repeat that test, or without the need to write out the question numbers repeatedly. So, the actual comparison and "right or wrong" flag is fairly simple, but one extra bit of functionality I'd like to add is to add a running count of how many times a particular question has been answered incorrectly....so that hopefully the tests can be done several times and attention diverted to those topics causing the most problem. My issue is I don't know how to do this, as the standard worksheet functions would reset to zero when the original answer is deleted....I think it might be a macro job, but wouldnt' know where to start....any help gratefully received! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function for counting how many times a Q is answered wrong
Bernie,
Bit of fiddling around and that's worked an absolute treat! Thank you so much! "Bernie Deitrick" wrote: Charly, You could use the an event to record results and to clear the results table when you want to reset. For example, if the student enters answers into column B, and you have the Right or Wrong formulas in column C, then this code will keep a running count of how many times the answer has been answered incorrectly in column H. Copy the code, right click the sheet tab, select "View Code" and paste the code into the window that appears. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address = "$D$1" And UCase(Target.Value) = "CLEAR" Then Application.EnableEvents = False Range("H:H").ClearContents Application.EnableEvents = True Exit Sub End If If Target.Column < 2 Then Exit Sub If Target.Offset(0, 1).Value < "Wrong" Then Exit Sub Application.EnableEvents = False Cells(Target.Row, "H").Value = Cells(Target.Row, "H").Value + 1 Application.EnableEvents = True End Sub To clear the record, type "clear" (without the quote marks) into cell D1 of the same sheet. If you have trouble changing this code to reflect your actual sheet structure, then post back. HTH, Bernie MS Excel MVP "CharlyFarly" wrote in message ... Hi there, I'm a bit stuck - I've written a fairly simple S/sht which is basically designed to hold the answers to a number of book based multi choice tests. Reason behind it was simply so that the student can enter the answer they think is appropriate without either marking their book, thus meaning they can't repeat that test, or without the need to write out the question numbers repeatedly. So, the actual comparison and "right or wrong" flag is fairly simple, but one extra bit of functionality I'd like to add is to add a running count of how many times a particular question has been answered incorrectly....so that hopefully the tests can be done several times and attention diverted to those topics causing the most problem. My issue is I don't know how to do this, as the standard worksheet functions would reset to zero when the original answer is deleted....I think it might be a macro job, but wouldnt' know where to start....any help gratefully received! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function for counting how many times a Q is answered wrong
Charly,
You're welcome, and thanks for letting me know that you got it to work. Bernie MS Excel MVP Bit of fiddling around and that's worked an absolute treat! Thank you so much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of times data matches | Excel Worksheet Functions | |||
Counting the number of times a certain date appers | Excel Discussion (Misc queries) | |||
Counting # of Times an Item Shows Up in a Table | Excel Worksheet Functions | |||
after entering certain number of times wrong password file can cur | Excel Discussion (Misc queries) | |||
Counting cells with a specified frequency(how many times it appear | Excel Discussion (Misc queries) |