[Leo Heuser[_2_]]

Hi Jas

Here's one way to do it:

Sub Points()
'Leo Heuser, 16-7-2003
Dim Counter1 As Long
Dim Counter2 As Long
Dim Dummy As Single
Dim HowMany As Long
Dim PointRange As Range
Dim PointRangeValue As Variant
Dim RankRange As Range
Dim RankRangeValue As Variant

Set RankRange = Range("B2:B6")
RankRangeValue = RankRange.Value

Set PointRange = Range("F2:F6")
PointRangeValue = PointRange.Value

For Counter1 = 1 To UBound(RankRangeValue, 1)
HowMany = Application.WorksheetFunction. _
CountIf(RankRange, RankRangeValue(Counter1, 1))
Dummy = 0

For Counter2 = 1 To HowMany
Dummy = Dummy + _
PointRangeValue(RankRangeValue(Counter1, 1) + _
Counter2 - 1, 1)
Next Counter2

RankRange.Cells(Counter1, 1).Offset(0, 1).Value = _
Dummy / HowMany
Next Counter1
End Sub


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only, please.

"J P Singh" <noemail@asIhatespam skrev i en meddelelse
...
Hi All

I need to throw a quick solution for one of my clients and don't know what
to do, wonder if someone is willing to help

I have two list of data as below. I want to enter the position of each
person and pickup the relevant points from the second list.

Name Position Points

Jas 1
Ian 2
Peter 2
James 2
Ilona 5

There is another look up list like this

Position Points

1 50
2 40
3 30
4 20
5 10


What I would like to do is to enter the position in the first list and get
the corresponding no of points from the second list? Now this is simple
and
I could use a vlookup function to do that. but there is a slight twist to
the above rule explained as below

For Position No 1 the points should be 50 which is fine as only one person
with position 1

Now the other four guys have all got the position 2 so rather them getting
40 points for position 2 the points should be calculated by getting the
average of 2nd , 3rd & 4th position(as they would have occupied those
positions if they wouldn't be on the same position)

so they should all recieve (40+30+20)/3 = 30 Points each

if there was only one person with 2 position than ofcourse he should
recieve
40 points as in the list

I am looking to do this in VBA, please help if you can !!!!!!!!!!!!!!

Thanks in advance

Jas