Assigning specific values to strings and/or numbers

**xyba** · 10-21-2010, 08:39 AM

Hi

I've attached an example of the worksheet I'm creating. It's basically a score sheet for quiz questions.
The answer value is in column C and the contestants answers in columns D-J under their respective names. Columns K-Q will record their scores and these are the columns I want to create a formula for to input their score. They receive 2 points if the answer is correct (for both a string and a number answer) and 1 point (for numeric answers only) for the nearest answer only if no one has answered correctly. More than one person can have 2 points if they are correct or 1 point if they are equally the closest (either + or -).

Would anyone please be able to help me with the formula for the scoring. (I've added a notes column on the attached to help, hopefully) The questions in each row could change from numeric to strings from one week to another so any formula would have to cater for both string or number if this is possible.

**DonkeyOte** · 10-21-2010, 09:30 AM

@xyba, the point I was referring to in the other post regards Text "closest match" was what happens if:

Q: Best football team on earth
A: Tottenham Hotspur

and the answers are:

Tottingham (submitted by Ossie Ardiles)
Spurs
Arsenal

what then... technically 2 responses are correct and one participant is seriously miguided

If you're concerned only with specific text matches then it's pretty straightforward add-on from prior solution.

If you're concerned with "near" matches in text terms things become quite convoluted (quite quickly)

**xyba** · 10-21-2010, 09:33 AM

Thanks for the reply.

The text matches would be specific and "near-misses" are counted as incorrect.

**DonkeyOte** · 10-21-2010, 09:39 AM

OK, so before we decided to use R5 for the smallest numeric variance rather than repeating the calculation for each column - let's adjust that:

Please Login or Register  to view this content.

Then:

Please Login or Register  to view this content.

**Marcol** · 10-21-2010, 09:47 AM

Try this workbook

If the question is numeric then the answers are checked against the minimum value from the helper table.

Hope this helps

**xyba** · 10-21-2010, 01:17 PM

Thanks for your help both. Sorted now.

**Marcol** · 10-21-2010, 05:38 PM

On behalf of both, happy to have been of help.

Assigning specific values to strings and/or numbers

LinkBack

Thread Tools

Rate This Thread

Display

Assigning specific values to strings and/or numbers

Re: Assigning specific values to strings and/or numbers

Re: Assigning specific values to strings and/or numbers

Re: Assigning specific values to strings and/or numbers

Re: Assigning specific values to strings and/or numbers

Re: Assigning specific values to strings and/or numbers

Re: Assigning specific values to strings and/or numbers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions