Rescale

[Rappy]

I need to rescale student scores to reduce the fail rate, for example if our
exam failure rate exceeds 25% (ie 25% of students receive exam score of less
than 50%) then I need to amend the pass score so that at least 75% of
students have passed. How can I acheive this using Excel? Any help would be
appreciated.
Thank you in advance.
Tomai

 

[Max]

Perhaps a simple set-up to illustrate one possible
approach, which uses a one-variable data table ?

Assume the sample table below is in A1:B5
with data from row2 down

Name Marks
Student1 39
Student2 67
Student3 25
Student4 34

Put

in E1: PassMark (a label)
in F1: 35 (just a arbitrary value)

in E2: FailRate (a label)
in F2: =1-AVERAGE(C2:C5)
Format F2 as percentage

Select E1:F2

Click Insert > Name > Create > Check "Left column" > OK

This will name the single cells F1 and F2
as "PassMark" and "FailRate" respectively

Put in C2: =IF(B2>=PassMark,1,0)
Copy C2 down to C5

F2 will return the failure rate
based on the passing mark set in F1

-------
Now to create a "one variable" data table
-------------
Put in I1: =FailRate
Put in I2: =E2

Put / Fill down in I2:I9 the values

15, 20, 25 .. 50

(these will be various possible passing marks)

Select I1:J9

Click Data > Table

Enter:

For row input cell: I11
(Can be any empty cell outside I1:J9)

For column input cell: F1

Click OK

Format J2:J9 as percentage

--
In J2:J9 will be the calculated FailRates
for the corresponding passing marks in I2:I9

Experiment by re-entering/filling any other values
for the passing marks in I2:I9

The corresponding FailRates will be
immediately calculated in J2:J9

The data table would enable you to see how
the FailRates will vary with the passing marks
and hence help decide which passing mark to set
to achieve a desired FailRate

 

[Max]

sorry, typo correction ..

Line

Put in I2: =E2

should read:

 

[Harlan Grove]

Perhaps a simple set-up to illustrate one possible
approach, which uses a one-variable data table ?

....

?!

What possible reason is there not to calculate the 25% percentile of student
scores directly?

BTW, this was multiposted to .worksheetfunctions (at least).

 

[Max]

.. What possible reason is there not to calculate the 25
percentile of student scores directly?

None! (guess it just plain slipped my mind)
Thanks for the pointer, Harlan.

.. BTW, this was multiposted to .worksheetfunctions
(at least).

This is .worksheetfunctions?

Think OP multi-posted to .public.excel
and .public.excel.misc as well

 

[Harlan Grove]

This is .worksheetfunctions?

....

No, this is .worksheet.functions (note the dot between worksheet and
functions).

 

[Max]

No, this is .worksheet.functions
(note the dot between worksheet and functions)

Uh-uh, you're right. I missed the dot.
Guess it's time to change me old lenses <g>

 

[AlfD]

Hi!

I've a bit of sympathy for the OP because it is rather a lottery as t
wheher any given test is harder or easier than another test. If you'v
seen what is happening in national systems like mine where dependanc
on the veracity of tests leads otherwise intelligent people to believ
the attainment of their cohorts of students rises and falls by as muc
as 3% year-on- year you would maybe think again about normalising o
scaling test results.

However: enough of that.

OP could list his test results in a column, say A1 to A100.. Let'
assume there are 100 such results, therefore. Enter =large(A1:A100,25
in a vacant cell. This will give the score which 25 out of the 10
exceed. The simple quartile. Take it from there. There seems n
particular virtue in scaling the marks (linear? fancy?). All tha
shifts in the end is the cut-off point.

Al

 

[Rappy]

Thank you Max.

sorry, typo correction ..

Line

should read:

 

[Max]

You're welcome ..