PDA

View Full Version : Solved: dynamic name Rage? (or better solution)



debauch
06-28-2006, 07:08 AM
Hello,

Is there a way to averge the scores of each person on the attached sheet (sample). I am stumped.

compariniaa
06-28-2006, 07:32 AM
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

:beerchug:

debauch
06-28-2006, 07:59 AM
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.

compariniaa
06-28-2006, 08:34 AM
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.
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
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.

lucas
06-28-2006, 08:52 AM
Pretty darn good for someone with 2 weeks experience....

debauch
06-28-2006, 09:00 AM
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?

compariniaa
06-28-2006, 09:01 AM
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

compariniaa
06-28-2006, 09:06 AM
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?
how did you rearrange the code? I just ran the macro straight off the test file you gave, but I'll post it anyway

debauch
06-28-2006, 09:17 AM
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
Do While Range("A" & RowNum).Value <> 0
Maybe excel 97 cannot do this?

debauch
06-28-2006, 09:18 AM
hmm ... maybe I am missing an add-in?

compariniaa
06-28-2006, 09:23 AM
Maybe excel 97 cannot do this?
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?

jungix
06-28-2006, 09:25 AM
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

lucas
06-28-2006, 09:36 AM
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.

lucas
06-28-2006, 09:49 AM
Its definitely a ver 97 problem......back to the drawing board

compariniaa
06-28-2006, 10:12 AM
in that case, i've got nothing. i don't know anything about 97 vba

debauch
06-28-2006, 10:21 AM
ahh ... this is not good.

lucas
06-28-2006, 10:25 AM
be patient....it can be done in 97. We just need someone who is familiar with the differences to come along.

Shazam
06-28-2006, 10:47 AM
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))

mdmackillop
06-28-2006, 11:25 AM
Hi Shazam,
Very neat, but your second code fails if there is a single name/result
Regards
MD

debauch
06-28-2006, 11:29 AM
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 !

Shazam
06-28-2006, 11:36 AM
Hi Shazam,
Very neat, but your second code fails if there is a single name/result
Regards
MD

Hi mdmackillop,

It still works. Look at the attachment below.

mdmackillop
06-28-2006, 11:43 AM
Seems OK now. Just one of those MS puzzles I guess. ...so 2 bits of neat formulae:clap: :clap: