Finding Highest Ranked Item from Date

[SAO]

Hi,

I'm working on a project for a radio station and I'm stuck trying to
determine the top 15 songs played for the year 2003 in an Excel spreadsheet.

I have a list of songs, the date the song was added and the number of plays
that song has been played to date. The problem is, the songs that have been
recently added haven't received as many plays as the songs that have been
added since the beginning of the year. I don't know if you have to take the
mean, variance or something to get the highest 15.

Is there any way to do this?

Thanks,
Shelly
(e-mail address removed)

 

[Ron Rosenfeld]

Hi,

I'm working on a project for a radio station and I'm stuck trying to
determine the top 15 songs played for the year 2003 in an Excel spreadsheet.

I have a list of songs, the date the song was added and the number of plays
that song has been played to date. The problem is, the songs that have been
recently added haven't received as many plays as the songs that have been
added since the beginning of the year. I don't know if you have to take the
mean, variance or something to get the highest 15.

Is there any way to do this?

Thanks,
Shelly
(e-mail address removed)

Here's one thought.

Let's say you have this set up:

A B C
1 Title DateAdded NumPlays
2 song1 6/15/03 70
3 song2 2/05/03 162
4
5

Add Column D -- PlaysPerDay
Add Column E -- Rank

The formula in D:
=NumPlays/(MIN(TODAY(),DATE(2003,12,31))-DateAdded)

The formula in E:
=RANK(PlaysPerDay,$D$2:$D$500)

Then, with the cursor in the table, select Data/Sort and sort either by
PlaysPerDay descending or by Rank ascending to see the top 15.

Having the RANK function helps to clearly identify any songs that are ranked
identically.


--ron