I am trying to create a table of calculated values on sheet3 from values on my Datasheet.
What my intention was is to filter the data on "Datasheet" by class. Count the number of members in each class(used to work out averages) and create a table for each class of final results variation from average etc on another sheet. In this workbook that sheet is call "SampleOut".
I have started on some code but not sure if I am even getting the base structure right. I have attached a spreadsheet.
http://www.mediafire.com/file/o4eibshjd5lajon/SampleDataSheet.xlsm
This will basically be the final step in my project but I just don't have the experience to quite acheive this myself yet.
What my intention was is to filter the data on "Datasheet" by class. Count the number of members in each class(used to work out averages) and create a table for each class of final results variation from average etc on another sheet. In this workbook that sheet is call "SampleOut".
I have started on some code but not sure if I am even getting the base structure right. I have attached a spreadsheet.
Code:
[FONT=Arial][SIZE=2][COLOR=#000000]Sub Fomulas()
With Worksheets("Datasheet")
Dim ClassCount As Integer
Dim Anum, Bnum, Cnum, Dnum, Fnum As Long
Dim Aavg, Bavg, Cavg, Davg As Long
' Filter by column B(Class id)
.Range("B").AutoFilter Field:=1, Criteria1:="=1", Operator:=xlAnd
' Using minus 1 to ensure headers aren't counted in Row Count
ClassCount = Cells(Rows.Count, "B").End(xlUp).Row - 1
FinalRow = Worksheets("Datasheet").Range(Cells("E:E"), Cells(LastRow, 5)).Cells
' Find the avg of all values in column and divide by number members
Aavg = (Sum("E2:E & FinalRow")) / ClassCount
' Find the difference for each member between their score and the average
Anum = ("E2:E & FinalRow") - Aavg
' Output values to sheet3
End With
End Sub[/COLOR][/SIZE][/FONT]</pre>http://www.mediafire.com/file/o4eibshjd5lajon/SampleDataSheet.xlsm
This will basically be the final step in my project but I just don't have the experience to quite acheive this myself yet.
