Spotty Logic (spottylogic) wrote,
Spotty Logic
spottylogic

The formula that ate my life...

OMG, I just spent three hours working on this one.

Here's my situation: I'm creating a hypothetical class's results on an assessment set. There's twenty levels of difficulty on the test, from A-T. Getting 5 errors on a level A test is terrible, but because there's so much more material on a level T test, getting 5 errors on a Level T test is great.

Since I didn't know what level of test each student was working with, I created a scale from 1% to 100%, and then created a data table showing what a 95% would look like on each level of the test.

Each test has three scoring brackets. Essentially, good/fair/poor. From test to test, the definition of what range of scores is, say, "fair" changes. But a 90% or less is always "poor," and a 95% or better is always "good."

So my challenge was to create a formula that would look at the percentile, pull up the score ranges for that level of the test, and figure out, if the student was fair, what that score range was, and based on how fair (a 91% is low fair, a 94% is high fair), the number of errors the student would have had to have gotten to reach that % score, had he ACTUALLY taken the test.

Here's my final formula:

=ROUNDUP(IF(F8<90,((90-F8)*(VLOOKUP($C$8,Sheet3!A4:B23,2,FALSE)/10)
+VLOOKUP($C$8,Sheet3!A4:B23,2,FALSE)),IF(F8<95,((95-F8)*0.2)*
(VLOOKUP($C$8,Sheet3!A4:C23,3,FALSE)-VLOOKUP($C$8,Sheet3!A4:D23,4,FALSE))
+VLOOKUP($C$8,Sheet3!A4:D23,4,FALSE),((((100-F8)*20)/100)*
VLOOKUP($C$8,Sheet3!A4:D23,4,FALSE)))),0)

but in retrospect, I just feel kind of dirty after committing it. The hardest part was remembering my high-school math so that a *higher* percent score produced a *lower* number of errors. *sigh* English majors shouldn't have to do this sort of thing!
Subscribe
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

  • 2 comments