Excel Formula Help - making quizzes

[cpheonix]

I'm relatively ok at Excel and I've designed a simple quiz. But now I want to advance it and was wondering if any Excel whizzes can help with the following questions (I've had no luck anywhere else!):

1) I've seen some quizzes where you can type in the answer but if its wrong but close i.e. spelt incorrectly, it comes up "almost" or something like that in the answer box. How's that done??

and

2) if you tally up a score, is there another formula linked to it where if you get a certain amount, a cell could have the words "crap", if its a low score? and different words for different amounts e.g. 1-10 = crap, 2-20 = ok etc? Cheers!

[CheekyChops]

2). You can use a nested IF statement.

Say the score is in B10, you can type in B11 a formula which says:

=IF(B10>30,"Well Done",IF(B10>20,"Fantastic",IF(B10>10,"Nearly",IF(B10<11, "Crap"))))

Which would give "Well Done" for over 30
"Fantastic" for 21-30
"Nearly" for 11-20
"Crap" for less than eleven.

You could probably use a nested IF statement to do the first part of your question as well, using incorrect spellings to return results instead of scores less than 10 etc.

[CheekyChops]

Can't you tell I am bored, I have just made one for the first part of your question as well.

My answer is in cell A12 and the correct answer is Prince Philip.

=IF(A12="Prince Philip","Correct",IF(A12="Prince Phillip","nearly",IF(A12="The Duke of York","almost!","Wrong!")))

Prince Philip will get the answer 'Correct'
Prince Phillip will get 'nearly'
The Duke of York will get 'almost'
Anthing else will get 'Wrong!'

I don't know how you can cover all possible miss-spellings though as you can only nest 7 IF statements, perhaps you should just go for the most 'popular'.

[cpheonix]

That's brilliant! Much appreciated, you dont know how many people I've asked to help me with this. I never knew you can only nest 7 IF's, so thanks for letting me know that too.

However your first formula had a problem, the last section wasnt recognised
IF(B10& It;11, "Crap"))))
But going by logic from the rest of the formula you gave, I put in
IF(B10<10,"crap"))))
and that seems to have worked.

But both answers have been a massive help - thank you very very much!

[lafrancaise]

Hi cpheonix
I've just read this thread and couldn't believe doing that was possible (gob-smacked) so thanks for your question and wow, congratulations to cheeky chops.
I'm now going off to do a few little quizzes myself!

[Steve_ref]

Just a littletip when using nested IF's.
What I tend to do is write down the whole formula first (OK time consuming but better in the long run). I then count the number of "( " and then the number of ")". If they are equal, then I'm sure I've got the formula right.
Another way is to write down formula and then join up the (), starting from outside. If the formula is right, you shouldn't have any odd "(" or ")".
Latest EXCEL versions will do this for you anyway.