Average in selected columns

[pcrowley]

I have a spreadsheet that presents the results from a test.The number of columns depends on the number of questions and the number of rowsdepends on the number of test takers.

I need to look across row 4 and where ever the word “Score”appears I need to average the list of scores in the rows below and put theaverage in the first blank row below the list of scores. The average should beexpressed as a percentage to one decimal place. If the percentage is less than75%, the cell should be filled in yellow.

Thank you for your help.

 

[Eric W]

How is your data getting populated? Are you looking for a formula solution, or a macro? Do you want an average per test-taker? If so, do you want the average on the same line? Where? Or do you want all of the averages in the first blank row below the list of scores? Or do you want an overall average?

The cell can be colored yellow via Conditional Formatting, or via a macro if you go that route.

 

[pcrowley]

Thank you for the reply.

The data in my spreadsheet is generated by the testingsoftware. Each row below row 4 represents the result for an individual testtaker and are summarized for each test taker. I am looking at the performanceof each question. I want the percent of test takers who answered each questioncorrectly. If less than 75% of test takers answer a question correctly we needto review the question to make sure it is a valid question or review thetraining to make sure the topic is covered.

What I need is a macro that will loop through each column inrow 4 and when it gets to a column that has the word Score in it, the percent (toone decimal place) of how many answered the question correctly will go in thefirst blank cell below the list of scores. The scores are 1=correct,0=incorrect. If the macro could format the cell so that it turns yellow if thepercent is less than 75%.

I will probably have to copy the macro into the workbook eachtime the report is run because I don’t know if I can change the reporttemplate.

Thanks again!

 

[Eric W]

Give this a shot:

Sub RateQuestions()
Dim c As Long, a As Double, lr As Long

    Application.ScreenUpdating = False
    Cells.Interior.Color = xlNone
    For c = 1 To Cells(4, Columns.Count).End(xlToLeft).Column
        If LCase(Cells(4, c)) = "score" Then
            lr = Cells(Rows.Count, c).End(xlUp).Row
            a = WorksheetFunction.Average(Range(Cells(5, c), Cells(lr, c)))
            Cells(lr + 1, c) = a
            If a < 0.75 Then Cells(lr + 1, c).Interior.Color = vbYellow
        End If
    Next c
    Application.ScreenUpdating = True
    
End Sub

 

[pcrowley]

Perfect! Thank you so much for your help.

 

[Eric W]

Glad to help!

 

[pcrowley]

One last question - is there a way to autofit the column that the word Score appears in? When the percentage is 100% it is too wide for the cell. I don't want to autofit the entire sheet because there are a lot of columns that are best left alone.

Thanks again.