Find the Median???

[Alex19nz]

OK try this:

I want to get the Median number from the "This Week" column for Cher. The output should hopefully tell me that Cher has a score of 7.

In addition to this.. I want to be able to create a report which tells me the medians for all the artists in the sheet.

Any ideas??

Cheers, Alex.

 

[alastair69]

OK try this:

I want to get the Median number from the "This Week" column for Cher. The output should hopefully tell me that Cher has a score of 7.

In addition to this.. I want to be able to create a report which tells me the medians for all the artists in the sheet.

Any ideas??

Cheers, Alex.

Can you post some dataso i can have a play about and find the best way of doing this for you.

 

[Ron_dK]

Create a query which should look something like this :

SELECT Avg(This Week) AS [Average Cher]
FROM YourTable
WHERE (((YourTable.Artist) Like "Cher*"));

Hope this helps.

 

[Alex19nz]

heres 1999

Ok here is 1999 Charts for New Zealand Top40.. Basically I want the medians for each artists song.

Charts.mdb

 

[Alex19nz]

oops! meant to say each artists song according to the "This Week" column..

 

[Sergeant]

There is a difference between Median and Average.
Median is the value at the center of an ordered list.
Average gives the distributed weight of a column in the list.

 

[Alex19nz]

True.. and thats part of what i need to do.. EG, for Cher.. When sorted by Date her median for 'Believe' is 7.

Once i get the medians for all the songs, I can then sort them and find the top 40 songs for the entire year.

See?

Cheers. Alex!

 

[alastair69]

Have a look at this...

 

[Alex19nz]

Oh wow!

...and i was just getting ready to slam my head in the door

Cheers bud.. ill have a play with that

alex.

 

[alastair69]

Is the database you are using going to be for personal use or for commerical uses.

I hope that attachment was ok and of use to you, it should give you some ideas for futher development of all your data.

Alastair

 

[Alex19nz]

Well is more personal research. The attachment was great.. It solved one of my problems. Still need to work out the median for each song.

The reason being Britney Spears - Baby One More Time was in the chart 26 times however her placings were fairly low (She was only at Number 1 for four weeks where as Lou Bega - Mambo #5 was at No.1 for six Weeks)

This would place Lou higher than Britney but with the Title Count it doesnt work.

It still gives me an indication on how many weeks the song was in the chart which is half of what i needed to solve so now its just a matter of finding the median for each individual song. I can do this in excel but its going to take a heck of a long time to do it manually.

Have a think

Cheers, Alex.

 

[Alex19nz]

Ideas?

I know there's a way to get the median somehow

 

[alastair69]

I know there's a way to get the median somehow

Are you wanting all the this weeks to be displayed I.E. 1,2,3,4,5,6,7,8,9,10 and so on upto 50+

Alastair

 

[Alex19nz]

No, just the median number of the weeks it was in the chart. So for the Foo Fighters - Learn To Fly, They had 6 entries and during that period they were at positions 43,43,38,38,40,32. It should give them a median of 39

Cheers. Alex

 

[Bob Scarborough]

Hi Alex,
Found the simplest way of getting this was to add a crosstab to get number of weeks a song was in the charts and the positions it occupied in the top 40. I then exported same to Excel and used the 'MEDIAN' Function to calulate same for each song. If you thumb through the worksheets in the Excel spreadsheet, we now have following for each song/artist.... # weeks in the chart and the Median Value for it's position. I'm not sure how you are going to weigh the relative scores of songs with only a few weeks in the charts and a low median score versus those with many weeks and a slightly higher Median score.

Let me know how you go.

Bob

 

[Alex19nz]

Let me know how you go.

blinda!

thats perfect mate

cheers!

Alex.

 

[raskew]

Hi -

Try this. The test refers to Northwind's Orders table.

Function MedianF(ptable As String, pfield As String) As Single
'*******************************************
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight") <enter.
'Output:    41.36 (may vary according to how much
'           you've fiddled with this table).
'*******************************************

Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Integer
Dim sglHold  As Single

    strSQL = "SELECT " & pfield & " from " & ptable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    
    If n Mod 2 = 1 Then 'odd number of elements
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function

HTH - Bob