# Boards

## excel question

I KNow! exciting friday afternoon here

anyway, teacher has got a google form with various multiple choice questions - with sentence type answers, is there a way the responses sheet can produce a if the answer is suchandsuch then make cell 1, or 0 so if essentially can then self mark?

I am v bad at excel but feel it should be possible...all I have managed is to turn the correct answers green :)

## I know how to do this in Paint, but not Excel, sorry

## This makes little sense!

Will the sentence answers be exactly the same everytime?

## yes

"His eyes are more famous that I."

## this

If it's free text, then it'll be difficult, if not then you can do an =countif above the column and it'll only include those that fit the criteria, I do believe.

someone else explain this better please, I'm going home

## =if(A1="correct answer",1,0)

like that?

Use find function, this will return the character number where the matching text is, nest this in an if function so if the number is greater than 0 it gives the value you define

=if(Find('correct text,a1,1)>0,''correct'',''incorrect'')

A1 is the cell with the answer in the 1 tells it to start looking at the first character in a1

## it sounds like you want an array with the answers

so maybe one column with the answer number, then one column with the corresponding correct answer.

then next to the responses, use something like an If function with an index and match function to see if the answer is correct.

## I am finding most of these answers incomprehensible tbh

what John said is what I want I think, i am going to need to learn some excel stuff over the next week

## learn vlookup

you could use that to lookup the correct answer instead of having to hard code all of the answers into the 'if' formula

## actually yeah, although index and match is more powerful than vlookup

there's no need in this instance. the If bit would still return the 1 or 0 though, rather than just returning an answer, which you'd still need to compare in some way. e.g =if(vlookup(StudentAnswers!$a1, CorrectAnswers!$a$1:$b$20,2,0)= StudentAnswers!$b1 ,1,0) (assuming the question number is in column A of both sheets and there are 20 questions and the answers are in column B)

the index/match combo is truly the key to excel

## pleased with this that I made last week

=INDEX(INDIRECT("'" & D1 & "'!$B$2:$AZ$99"),MATCH(D2,INDIRECT("'" & D1 & "'!$A$2:$A$99"),0),MATCH(D3,INDIRECT("'" & D1 & "'!$B$1:$AZ$1"),0))

so you can specify in cells D1, D2 and D3 the sheet, row and column that you want to look up.

nice, I've not used indirect before, could definitely get some use out of it

## =IF(answer,0,1=NO,WTF,OMG,A1,1D,R2D2)>0,9( . )( . )

## Excellent question

:)