PDA

View Full Version : Excel help needed



step_andy
17th March 2008, 04:29 PM
Folks,
I need some assistance here:

I have a following problem on my hand where I need to calculate an exam score based on 3 assessments. Each assessment consist of number of tasks where a student is awarded a score for each task which then is used in calculations. The issue is, that I need to discard the lowest scoring assessment.
So.
Assessment 1: consists of 5 tasks, eg score: 6 6 7 7 5 resulting in Assessment overall 6
Assessment 2: consists of 5 tasks, eg score: 5 5 6 5 5 resulting in Assessment overall 5
Assessment 3: consists of 5 tasks, eg score: 3 6 4 4 4 resulting in Assessment overall 4

Exam overall: (drop lowest, eg Assessment 3) and Assessments 1 and 2 are used to calculate the overall score.

Several issues:
There are two equal low values - which one to drop?
How do I instruct excel to select the right task scores in calculation overall?

Thanks in advance

teamshaw
17th March 2008, 04:43 PM
You could possibly have the average calculated manually by using the sum of the range of scores - minimum of the range of scores (MIN function) then dividing this by 4.

geektechnu
17th March 2008, 05:36 PM
Here's a little example of how you could do something like this...

Lets say the 5 scores are in cells A1 to E5:

=( SUM(A1:E5) - MIN(A1:E5) ) / ( COUNT(A1:E5) - 1 )

MIN is used here to get the lowest number.
The equation then gets the average of 4 scores.

Hope you can adapt this to your needs.

step_andy
18th March 2008, 09:47 AM
Thanks for the suggestions, but this isn't what I need.
I will try from different angle ..

I have 3 data arrays, A1; A2; A3 each consisting of collection of 5 values.
I need to use 2 arrays with highest average values in calculating overall score.

Example here
http://img215.imageshack.us/img215/8025/examplebc0.th.jpg (http://img215.imageshack.us/my.php?image=examplebc0.jpg)

So depending on the values as calculated Average 1 in cell F2 and Average 2 in L2 and Average 3 in R2 I need to use 2 highest averages in calculating overall average.
Then, I need to round the number based on values in the Arrays used on calculating the overall average; in this instance values In Array 2 and Array 3. I need to round up to 6 if the majority (85%) of the achieved scores are above 5.9 (so, 85% of 10 scores is 8 values must be above 5.9). In this instance 5.9 would be rounded down to 5.

I have figured out the rounding part.
The problem is how do I select the right arrays to calculate my rounding!

neo
18th March 2008, 10:08 AM
Hi step_andy,

Hope this is more what you need. (Formulae shown in row 9 calculation cells.)

http://www.aplin.com.au/wp-content/uploads/2008/03/excel-screenshot.PNG

(Edit: Cell T7 should have read "Average of Top 2 Assessments". Sorry about any possible confusion.)

scokim
18th March 2008, 10:09 AM
Does replacing A1:E5 in the suggested formula above with F2,L2,R2 work ?

step_andy
18th March 2008, 01:28 PM
Hi Neo,
Thanks for that.
Whilst this allows Excel to use 2 highest average values, how do I instruct Excel to look into respective arrays?
In your example, array 3 results in lowest Average, so values in the Arrays 1 and 2 should be used in determining if the average of 2 highest assessments should be rounded up or rounded down.
Note, that I am not using arithmetic rounding, eg if above .5 it rounded up and if below, rounded down.
I am looking for something that will say, if Average for Assessment 3 = min, use Arrays 1 & 2 in rounding calculation.

neo
18th March 2008, 04:06 PM
Hi step_andy,


I am looking for something that will say, if Average for Assessment 3 = min, use Arrays 1 & 2 in rounding calculation.
I thought that my example actually did that. I've updated the sample to include simple rounding (below).


http://www.aplin.com.au/wp-content/uploads/2008/03/mactalkexcelv2.PNG

I'll explain how I've set it up, so you can perhaps help me understand the holes in my understanding of what you need...

Row P: (Rounded) Average of Assessment 1
Row Q: (Rounded) Average of Assessment 2
Row R: (Rounded) Average of Assessment 3

Row S: The lowest (Rounded) Assessment value. (If the three rounded average assessment values are 2, 3 and 2 respectively, then this value will be 2...doesn't explicitly matter if we're discarding Assessment 1 or or Assessment 3.)

Row T: Adds ALL rounded average values from the three assessments, then removes the lowest rounded assessment value (as calculated in row S). This added value (here the rounded average of assessments 1 & 2) is divided by two to get the overall average assessment value (from the two 'included' assessments - 1 & 2).

...I think this is what you're after.







BUT - here is my $0.02:

I would think it would be MUCH fairer to use the calculations from my first example (ie DON'T use the rounding).

If you don't round, you effectively find the average of the tasks from the 'included' assessments.
With rounding, you bias the results; finding the average of the rounded (up or down) averages of the 'included' assessments.I've made an example for two students, showing their non-rounded scores in yellow, and their rounded scores in green:


http://www.aplin.com.au/wp-content/uploads/2008/03/mactalkexcelv3.PNG


Student 1 did better in Assessment 1, Task 1 and Assessment 2 Task 1.

Using the rounded technique, Students 1 and 2 both get 6.
Using the non-rounded technique, Student 1 gets a better mark (6.4) than Student 2 (5.6).

I think the non-rounded technique is fairer...don't you?