Lookup, averages and custom dates?

[adaircameron]

Hello,

I'm hoping someone can help me with the following scenario?

I'm using Excel 2010 and got a worksheet that has a score (1 to 10) in column A with the dates in row 1. The values are often quite consistent and are made up of names (not numbers).

What I'm hoping for is on another worksheet I can work out the average score for each name by date? My worksheet looks along the lines of;

Score 25 Aug 11 26 Aug 11 30 Aug 11
1 Example 1 Example 1 Example 2
2 Example 2 Example 2 Example 1
3 Example 3 Example 3 Example 3

Is this possible through LOOKUPs etc?

Thanks

 

[venkat1926]

try this macro and see about 5 rows below the main data

RUN ONLY "TEST' THOUGH ALL THE MACROS MUST BE PARKED IN THE MODULE.

if you want to retest
run undo and then
run only 'test"

Dim r As Range, j As Integer, avg As Double, cfind As Range, filt As Range
Dim cfilt As Range, m As Integer, tot As Double, add As String


Sub uniquevalues()
For j = 2 To Range("B1").End(xlToRight).Column
Range(Cells(2, j), Cells(2, j).End(xlDown)).Copy Cells(Rows.Count, "H").End(xlUp).Offset(1, 0)
Next j
Range("H1") = "heading"
Set r = Range(Range("H1"), Range("H1").End(xlDown))
Set filt = Range("A1").End(xlDown).Offset(5, 0)
r.AdvancedFilter xlFilterCopy, , filt, True
Set filt = Range(filt.Offset(1, 0), filt.End(xlDown))
Range("H1").EntireColumn.Delete
End Sub

Sub test()
uniquevalues
tot = 0
m = 0
Set r = Range("A1").CurrentRegion
Set filt = Range("A1").End(xlDown).Offset(6, 0)
Set filt = Range(filt, filt.End(xlDown))
For Each cfilt In filt
Set cfind = r.Cells.Find(what:=cfilt.Value, lookat:=xlWhole)
m = m + 1
'msgbox m
tot = tot + Cells(cfind.Row, 1)
'msgbox tot


add = cfind.Address


Do
Set cfind = r.Cells.FindNext(cfind)
'msgbox cfind.Address
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
If m > WorksheetFunction.CountIf(r, cfilt.Value) Then GoTo proceed

m = m + 1
'msgbox m
tot = tot + Cells(cfind.Row, 1)
'msgbox tot
proceed:

Loop

avg = tot / m
cfilt.Offset(0, 1) = avg
m = 0
tot = 0
Next cfilt
End Sub

Sub undo()
Range(Range("A1").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A")).EntireRow.Delete

End Sub

 

[adaircameron]

Thanks! I will try giving this a go