Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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


--
RiotLoadTime
------------------------------------------------------------------------
RiotLoadTime's Profile: http://www.excelforum.com/member.php...o&userid=35956
View this thread: http://www.excelforum.com/showthread...hreadid=565138

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default Calculate mean of test scores from rows of test answers

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 08:11 PM
Hiding Rows if the linked rows are blank KG Excel Discussion (Misc queries) 9 May 18th 05 02:32 AM
Pivot Table for survey results with set of possible answers in PT's Rows Field? [email protected] Excel Discussion (Misc queries) 0 May 16th 05 10:35 PM
Insert rows Mr. G. Excel Worksheet Functions 3 March 31st 05 03:49 AM
Adding Rows to Master Sheet Excel Newbie New Users to Excel 1 December 23rd 04 11:56 PM


All times are GMT +1. The time now is 04:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"