brettk
05-30-2011, 05:52 PM
I'm a political science teacher and okay with standard Excel worksheet functions. This is my first foray into VBA, however, and I'm stuck.
I have a unique formula for curving students' grades, which basically varies the amount of boost students get on an exam score inversely with respect to the class average. In other words, an exam on which everyone does well gets only a small curve factor, but an exam which everyone bombs has a strong curve to prop the average back up.
So what I want is a worksheet function that takes as an argument a range of numbers, calculates an average of this range, and then, in inverse proportion to how high that average is, apply the curve formula (which is 100^(1-average)*individual grade^(average)).
I can't figure how to read in a range of cells (which may vary in location, size, etc.) into VBA and then how to perform calculations on it. I would guess the idea is to import the data as an array (or as a range, I'm not certain as to the diff.?) and then sum and count the data from there, but that hasn't worked for me.
Here's my own paltry effort so far, simply to get data that I can count.
Function Counter(inRange) As Integer
Counter = Range(inRange).Count
End Function
I have a unique formula for curving students' grades, which basically varies the amount of boost students get on an exam score inversely with respect to the class average. In other words, an exam on which everyone does well gets only a small curve factor, but an exam which everyone bombs has a strong curve to prop the average back up.
So what I want is a worksheet function that takes as an argument a range of numbers, calculates an average of this range, and then, in inverse proportion to how high that average is, apply the curve formula (which is 100^(1-average)*individual grade^(average)).
I can't figure how to read in a range of cells (which may vary in location, size, etc.) into VBA and then how to perform calculations on it. I would guess the idea is to import the data as an array (or as a range, I'm not certain as to the diff.?) and then sum and count the data from there, but that hasn't worked for me.
Here's my own paltry effort so far, simply to get data that I can count.
Function Counter(inRange) As Integer
Counter = Range(inRange).Count
End Function