Hello,
Is there a way to averge the scores of each person on the attached sheet (sample). I am stumped.
Hello,
Is there a way to averge the scores of each person on the attached sheet (sample). I am stumped.
Just enter this formula in B17
=AVERAGE(B2:B16)
or, through vba:
Sub test()
Range("c17").Value = Application.WorksheetFunction.Average(Range("B2:B16"))
End Sub
sorry, clarification :
I am looking to find a way to pull the average for each person.
There may be at any given time, different amounts of scores, per person.
I would be looking to average, on this example, Alyssa's score, then Robyns ... etc.
The number of rows in each range may change , so I am looking to make this dynamic somehow.
Now that I reread your first post I see that I wasn't paying attention. Here's the code I got. It works, but since I've only been doing VBA for about 2 weeks it's probably not the most efficient way to do it.
[vba]Sub test()
Dim First As Integer
Dim Last As Integer
Dim RowNum As Integer
RowNum = 2
Begin_Loop:
Do While Range("A" & RowNum).Value <> 0
If Range("A" & RowNum).Value = Range("A" & RowNum).Offset(-1, 0).Value Then
If Range("A" & RowNum).Value <> Range("A" & RowNum).Offset(1, 0).Value Then
Last = Range("A" & RowNum).Row
End If
Else
First = Range("A" & RowNum).Row
RowNum = RowNum + 1
GoTo Begin_Loop
End If
Range("C" & Last).Value = Application.WorksheetFunction.Average(Range("B" & First), _
Range("B" & Last))
RowNum = RowNum + 1
Loop
End Sub[/vba]
I hope this suits your purposes. If not, let me know and I'll see what I can do. One weakness I can see already is if there isn't a name for every score. If there isn't a name for every score, the macro will stop at that first blank.
Pretty darn good for someone with 2 weeks experience....
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
It looks like it might do what I am looking for ... I have tried to rearrange the code a little bit , I still cannot get it to run through the loop . Could you maybe post a test sample w/ the code?
thanks, but it's not that impressive considering how much time i've been able to dedicate to it...i got an internship and my boss wants me to automate a lot of stuff, so i've been doing it for more like 3 weeks for 8 hours a day.but thanks anyway
how did you rearrange the code? I just ran the macro straight off the test file you gave, but I'll post it anywayOriginally Posted by debauch
I get 'type mismatch' error as soon as I try to run it ... I am trying to run by Alt+f8 , selecting the macro ... it then errors out on 'type mismatch' , and highlights
[vba]Do While Range("A" & RowNum).Value <> 0[/vba]
Maybe excel 97 cannot do this?
hmm ... maybe I am missing an add-in?
Oh...I'm sorry, I don't know if I can help.
I'm using Excel 2003. I don't know if the differences between 1997 and 2003 are enough to give you this kind of error. The code ran without any problem at all on xl03. To be honest, I don't know what to do now. Is there anyone who could help us with this?
Maybe try Cells(RowNum, 1) instead of Range("A" & RowNum)
Both work fine inb Excel 2003 but maybe the range was introduced later than your version
mismatch error usually indicates that it cant find the row or column or sheet....shouldn't have anything to do with addins. Will test it on 97 in a few minutes and post back. be patient.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Its definitely a ver 97 problem......back to the drawing board
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
in that case, i've got nothing. i don't know anything about 97 vba
ahh ... this is not good.
be patient....it can be done in 97. We just need someone who is familiar with the differences to come along.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Try to see if this works.
Input formula in cell C2 and hold down:
Ctrl+Shift+Enter
and copy down.
The formula is an-array
=IF(SUMPRODUCT(--($A$1:A3=A3))>1,"",AVERAGE(IF($A$2:$A$100=A2,$B$2:$B$100)))
Or
non-array
just enter formula and copy down.
=IF(SUMPRODUCT(--($A$1:A3=A3))>1,"",SUMIF($A$2:$A$100,A2,$B$2:$B$100)/COUNTIF($A$2:$A$100,A2))
Last edited by Shazam; 06-28-2006 at 11:18 AM.
Hi Shazam,
Very neat, but your second code fails if there is a single name/result
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Sweet ... it worked great. THe array formula was crashing my excel due to the number of records, but the non-array version of it works wonders.
This will be very useful for many reports. Thanks very much to everyone !
Posting Permissions
Reply With Quote
