Calculating Test Score Averages

  • Thread starter Thread starter ASteele
  • Start date Start date
A

ASteele

I am trying to create a spreadsheet where I can track test scores for given
tests. I would like to automate, through VBA, the process of doing a query
for a test between two given dates and producing the overall average for that
test.

i.e. I may have given a specific test in the month of June 2009. This test
was given three times, in that month, to a group of 12, 9, and 5 individuals
respectively. I may give several different tests over the course of a year.
How can I query out a specific test and all the related scores for that test
between two given dates? Additionally, I need to calculate the average score
only for the results of the query.
 
You could probably use a pivot table to do what you described. See pivot
table tutorial in Excel.
 
It could also be done with just formulas, if you haven't yet mastered pivot
tables. Use a couple of fixed cells, say at the top of the sheet, where you
can input the begin and end dates of the period you want to include in your
results. Then fill a column next to your score records with an if/then
formula that returns the score if within the date range, and returns "" if
not in the range. Then you can sum the column and divide by COUNT to get an
average.

Come back if you need help with how to write the formulas.
 
Have not worked well with Pivot Tables in the past. I have dabbled in them,
a little. But an expert, I am not. I will, however, give a shot. Thanks
 
I have tried to write the If/Then statement. However, I can not seem to get
the order correct when trying the nest an if/then within an if/then
statement. I am thinking this is what I have to do to get all test scores
between a begin and end date scenario.

My test dates are in column C with test scores in columns D through S and an
average of the test scores, for the individual test in column T. I put my
"Begin Date" in cell d1 and "End Date" in cell f1. However, when I write the
if statement, my results are incorrect. The formula I wrote is as follows:

IF(C11>=$D$9,IF(C11<=$F$9,AVERAGE(D11:S11)," "),AVERAGE(D11:S11))

I will keep plugging away, but any help you can offer, I would greatly
appreciate.
 
I changed the If/Then formula to:
IF(AND(C7>=$D$1,C7<=$F$1),AVERAGE(D7:L7)," ")

This did provide me with an average for the test scores between given dates.
Now I have to figure out how to calculate only a specific test score between
said dates. The current formula provided an average for all test scores
within the date range given. Still working on the issue.
 
With the further detail in your last post regarding the complexity of
measures you want to calculate for the date range, JLGWhiz' suggestion of
pivot tables really is the way to go. There are some points in building an
Excel skill set where you just have to grit your teeth and get over the hump.
A good tutorial might be the way to go.
 
Back
Top