Software

Ask something
Question Locked

Which Formulas to use in Excel

0 helpful
3 replies

by anita.fairhurst

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello all

I am trying to create quizzes for work, similar to the type that I see on the internet. Can anyone tell me which formula to use to return the value of either correct or incorrect in a spreadsheet, for example if i ask the question which operating system are you using, i would want my student to say XP, i then want the spreadsheet to check the contents of the cell and return a value of either incorrect or correct depending on what the student entered. i have been all around the formula help screens and the wizard with no joy. thank you

3 replies

by locolobo

Assumptions:
Column A: questions
Column B: answers
Column C: feedback (correct or incorrect)

Using your example say in cell A4 is your question about the OS. In B4 the student types in XP. If XP is the correct answer then the formula in cell C4 would be something like:

=IF(ISBLANK(B4),"",IF(B4="XP","Correct","Incorrect"))

The problem is what if the student types in "Windows XP" or "Win XP". The formula will still return "Incorrect"

To correct the problem suppose I put the answers in H4. You would want to hide column H. Now the formula can be:

=IF(ISBLANK(B4),"",IF(MID(B4,SEARCH(H4,B4,1),2)=H4,"Correct","Incorrect"))

This may not be the best solution as the student could type in Linux XP and get "Correct". That's why a lot of teachers rely on multiple choice questions. They are a lot easier to grade, programitically or by eye. Then you can use the first formula and just look for A, B, C or D.

This discussion is closed to new replies.