Calculate mean of test scores from rows of test answers

I gave a test with 2 multiple part questions to 100 different people. All the data is entered into a database where the first row is the question number/part and the subsequent rows are the responses (between 0-60) for each individual person. There's a specific scoring algorithm for the test and I'd like to be able to get the mean of the test scores WITHOUT creating another table of each individuals overall score.

An example of the database:

| Question 1: | A) | B) | C) | Question 2: | A) | B) | C) |
| | 24 | 56 | 22 | | 33 | 47 | 51 |
| | 33 | 34 | 46 | | 21 | 55 | 45 |

(in the above example, columns A and E are blank except for the first row--cell A1 and E1 are just placeholders to help us read the worksheet without confusing questions)

The scoring algorithm:
Let 1a represent their response to Question 1, part A (so column B in the above example):

{[(99-1a)+(99-1b)+(99-1c)+(99-2a)]/8}*15

Is there anyway to score all 100 people, using the above algorithm, AND find the average of all their scores, all in one cell? I don't care about anything but the mean of their scores, so I'd like to do this without creating additional tables (i.e. without creating a table of each persons score, then using the AVERAGE function over it).

Thanks,
-RiotLoadTime

Try:
=AVERAGE((99*4-B2:B101-C2:C101-D2:D101-F2:F101)/8*15)
Use Ctrl-Shift-Enter to complete the formula because it's an array formula.
If done correctly you will see { } around the formula automatically.

-Simon

"RiotLoadTime" wrote:

>
> I gave a test with 2 multiple part questions to 100 different people.
> All the data is entered into a database where the first row is the
> question number/part and the subsequent rows are the responses (between
> 0-60) for each individual person. There's a specific scoring algorithm
> for the test and I'd like to be able to get the mean of the test scores
> WITHOUT creating another table of each individuals overall score.
>
> An example of the database:
>
> | Question 1: | A) | B) | C) | Question 2: | A) | B) | C) |
> | | 24 | 56 | 22 | | 33 | 47 | 51 |
> | | 33 | 34 | 46 | | 21 | 55 | 45 |
>
> (in the above example, columns A and E are blank except for the first
> row--cell A1 and E1 are just placeholders to help us read the worksheet
> without confusing questions)
>
> The scoring algorithm:
> Let 1a represent their response to Question 1, part A (so column B
> in the above example):
>
> {[(99-1a)+(99-1b)+(99-1c)+(99-2a)]/8}*15
>
> Is there anyway to score all 100 people, using the above algorithm, AND
> find the average of all their scores, all in one cell? I don't care
> about anything but the mean of their scores, so I'd like to do this
> without creating additional tables (i.e. without creating a table of
> each persons score, then using the AVERAGE function over it).
>
> Thanks,
> -RiotLoadTime
>
>
> --
> RiotLoadTime
> ------------------------------------------------------------------------
> RiotLoadTime's Profile: http://www.excelforum.com/member.php...o&userid=35956
> View this thread: http://www.excelforum.com/showthread...hreadid=565138
>
>