Matt4319
Administrator
Joined: September 2003
Posts: 15,215
Staff
|
Post by Matt4319 on Apr 6, 2004 23:50:02 GMT -5
I know this seems early, but I'm trying to set up a year-end point system for my top 100, and I don't like the traditional inverse-points method when it stretches to 100 songs because it gives too much weight to long-lasting but not necessarily high-peaking songs (for my taste anyway). I want to use the following point values: #1 - 200 points #2 - 195 #3 - 190 #4 - 185 #5 - 180 #6 - 176 #7 - 172 #8 - 168 #9 - 164 #10 - 160 #11 - 157 ... (going down by 3 points per position) #39 - 73 #40 - 71 ... (going down by 2 points per position) #50 - 51 ... (going down by 1 point per position) #100 - 1 Those of you who are good at math, is there some kind of equation I can put into Excel that will approximate this? Thanks.
|
|
|
Post by Devil Marlena Nylund on Apr 7, 2004 14:48:18 GMT -5
I agree and that's prolly what I want too. If Bryan were here, he'd know.
|
|
j
4x Platinum Member
Joined: September 2003
Posts: 4,975
|
Post by j on Apr 7, 2004 18:05:45 GMT -5
Send me the 2 excel columns (Position - Points) and I'll put it into Stata or Minitab or SPSS to estimate the equation for you.
|
|
EvanJ
6x Platinum Member
Joined: September 2003
Posts: 6,371
|
Post by EvanJ on Apr 7, 2004 19:10:09 GMT -5
X: Position Y: Points Linear: y = -1.854131413x + 164.3436364 Quadratic: y = 0.019167469x^2 - 3.790045779x + 197.2541806
|
|
BlahBlahBlah
Platinum Member
Joined: September 2003
Posts: 1,964
|
Post by BlahBlahBlah on Apr 7, 2004 19:16:58 GMT -5
Matt, since you're using Excel, you can easily do regression by adding a trendline to the graph, and it would display the equation for it.
|
|
j
4x Platinum Member
Joined: September 2003
Posts: 4,975
|
Post by j on Apr 7, 2004 19:44:28 GMT -5
Matt, since you're using Excel, you can easily do regression by adding a trendline to the graph, and it would display the equation for it. Don't you need the extra Excel Stats Application package to do that tho?
|
|
BlahBlahBlah
Platinum Member
Joined: September 2003
Posts: 1,964
|
Post by BlahBlahBlah on Apr 7, 2004 20:36:19 GMT -5
Don't you need the extra Excel Stats Application package to do that tho? Nope. Just right click on the plots on a scatter plots, and then click Add Trendline.
|
|
Matt4319
Administrator
Joined: September 2003
Posts: 15,215
Staff
|
Post by Matt4319 on Apr 8, 2004 4:34:31 GMT -5
I tried the trendline thing (6th order polynomial), but for some reason it didn't work when I inputted it. The quadratic equation Evan provided works fairly well.
|
|
|
Post by af18c on Apr 8, 2004 15:47:48 GMT -5
X: Position Y: Points Linear: y = -1.854131413x + 164.3436364 Quadratic: y = 0.019167469x^2 - 3.790045779x + 197.2541806 ooooh my eyes
|
|
j
4x Platinum Member
Joined: September 2003
Posts: 4,975
|
Post by j on Apr 9, 2004 9:24:46 GMT -5
The quadratic equation Evan provided works fairly well. Method R^2 d.f. F Sigf bound b0 b1 b2 b3 LIN .930 98 1295.12 .000 164.344 -1.8541 LOG .930 98 1302.84 .000 281.582 -57.973 INV .352 98 53.14 .000 56.1007 281.633 QUA .996 97 11670.9 .000 197.254 -3.7900 .0192 CUB .998 96 20028.2 .000 205.119 -4.7018 .0416 -.0001POW .626 98 164.25 .000 1383.66 -.9372 EXP .902 98 900.46 .000 281.563 -.0360 LGS .902 98 900.46 .000 . .0036 1.0366 The Quadratic Model EvanJ provided explains 99.6% of the variance - A very good fit indeed. The cubic model explains 99.8% and thus has a higher correlation tho. The logistic, exponential etc. types don't work as well.
|
|
EvanJ
6x Platinum Member
Joined: September 2003
Posts: 6,371
|
Post by EvanJ on Apr 9, 2004 17:56:52 GMT -5
I didn't even think about calculating r or r2 for my equations. On my calculator it's a four-step process to calculate correlation unless it can calculate correlation and I don't know about it.
|
|
mst3k
New Member
Peese shut mouf.
Back from a 12 year hiatus.
Joined: September 2003
Posts: 345
|
Post by mst3k on Apr 9, 2004 19:03:52 GMT -5
Sheesh, I'm glad I only compile my own personal charts for fun. Although I guess all this math stuff might be fun for you guys. ;)
|
|
j
4x Platinum Member
Joined: September 2003
Posts: 4,975
|
Post by j on Apr 10, 2004 12:38:59 GMT -5
On my calculator it's a four-step process to calculate correlation unless it can calculate correlation and I don't know about it. 4 steps? That would involve knowing the exact formula, wouldn't it? You can just use "COR" in Excel.
|
|
Edf85
7x Platinum Member
Most definitely in the place to be. T-Boz. Chilli. Never forget Left Eye.
Joined: September 2003
Posts: 7,092
|
Post by Edf85 on Apr 10, 2004 13:05:10 GMT -5
|
|
j
4x Platinum Member
Joined: September 2003
Posts: 4,975
|
Post by j on Apr 10, 2004 13:53:34 GMT -5
I guess that explains your SAT score... ;) (Ouch.)
|
|
Edf85
7x Platinum Member
Most definitely in the place to be. T-Boz. Chilli. Never forget Left Eye.
Joined: September 2003
Posts: 7,092
|
Post by Edf85 on Apr 10, 2004 15:02:58 GMT -5
I guess that explains your SAT score... ;) (Ouch.) And my non-lack of personality ;)
|
|
EvanJ
6x Platinum Member
Joined: September 2003
Posts: 6,371
|
Post by EvanJ on Apr 10, 2004 18:31:19 GMT -5
4 steps? That would involve knowing the exact formula, wouldn't it? I'll try to explain it. After you type the two columns of numbers in. 1. Two-variable Stats for L1 (List 1) and L2 2. (L1 - x bar)(L2 - y bar) into L3 I don't know how to put a line over the x on the computer for x bar. 3. One-varible Statis for L3 4. Sigma x (sum of L3 values)/(n+1) where n is the number of values in the column. My statistics book also gave a procedure for caluclating r 2 directly that I don't remember.
|
|
|
Post by Adonis the DemiGod! on Apr 10, 2004 18:31:59 GMT -5
I'll try to explain it. After you type the two columns of numbers in. 1. Two-variable Stats for L1 (List 1) and L2 2. (L1 - x bar)(L2 - y bar) into L3 I don't know how to put a line over the x on the computer for x bar. 3. One-varible Statis for L3 4. Sigma x (sum of L3 values)/(n+1) where n is the number of values in the column. My statistics book also gave a procedure for caluclating r 2 directly that I don't remember. Cool Formula. I LOVE Math and I love Statistics. *goes to read Stats books*
|
|
BlahBlahBlah
Platinum Member
Joined: September 2003
Posts: 1,964
|
Post by BlahBlahBlah on Apr 10, 2004 19:23:53 GMT -5
I'll try to explain it. After you type the two columns of numbers in. 1. Two-variable Stats for L1 (List 1) and L2 2. (L1 - x bar)(L2 - y bar) into L3 I don't know how to put a line over the x on the computer for x bar. 3. One-varible Statis for L3 4. Sigma x (sum of L3 values)/(n+1) where n is the number of values in the column. My statistics book also gave a procedure for caluclating r 2 directly that I don't remember. I should ask my brother, since he's a stats major.
|
|
strong4PMB!
Diamond Member
Joined: September 2003
Posts: 17,394
|
Post by strong4PMB! on Apr 10, 2004 19:27:54 GMT -5
lmao I'm cool with my inverse points.
|
|
mst3k
New Member
Peese shut mouf.
Back from a 12 year hiatus.
Joined: September 2003
Posts: 345
|
Post by mst3k on Apr 10, 2004 19:54:55 GMT -5
I'm cool with my inverse points. Fo' shizzle.
|
|
j
4x Platinum Member
Joined: September 2003
Posts: 4,975
|
Post by j on Apr 10, 2004 20:50:34 GMT -5
I'll try to explain it. After you type the two columns of numbers in. 1. Two-variable Stats for L1 (List 1) and L2 2. (L1 - x bar)(L2 - y bar) into L3 I don't know how to put a line over the x on the computer for x bar. 3. One-varible Statis for L3 4. Sigma x (sum of L3 values)/(n+1) where n is the number of values in the column. My statistics book also gave a procedure for caluclating r 2 directly that I don't remember. That seems wrong. The formula for r is sum [ (xi - xbar)(yi - ybar) ] ------------------------------------ [std dev (x)]*[std dev (y)] r^2 is the square of the above.
|
|
EvanJ
6x Platinum Member
Joined: September 2003
Posts: 6,371
|
Post by EvanJ on Apr 11, 2004 16:33:57 GMT -5
Now I do remember it having standard deviations. I don't remember the last time I used the formula so I forget. When I typed n+1 I meant to type n-1.
|
|
strong4PMB!
Diamond Member
Joined: September 2003
Posts: 17,394
|
Post by strong4PMB! on Apr 11, 2004 19:08:29 GMT -5
|
|
j
4x Platinum Member
Joined: September 2003
Posts: 4,975
|
Post by j on Apr 11, 2004 21:23:00 GMT -5
Aren't you the one taking AP Stats next year?
|
|
|
Post by Devil Marlena Nylund on Apr 12, 2004 18:58:01 GMT -5
ok, so WHAT do you put in Excel?
|
|
strong4PMB!
Diamond Member
Joined: September 2003
Posts: 17,394
|
Post by strong4PMB! on Apr 12, 2004 20:42:33 GMT -5
Aren't you the one taking AP Stats next year? Next year.
|
|
superbu
New Member
Joined: April 2004
Posts: 375
|
Post by superbu on Apr 26, 2004 22:47:45 GMT -5
I use what I call inverse exponential points -- where each chart position is worth exactly double the points of the previous position.
Straight inverse points don't seem very fair, because then two weeks at #50 is equal to a week at #1. With this system, two weeks at #2 is equal to a week at #1, two weeks at #3 equal to a week at #2, etc.
But so far I've only done it for songs in the top 50 of the chart, because it gets into such tiny fractions that it's practically impossible (for me) to do on a calculator. I've only done charts for the years 1939-1962 so far. I'm undecided about which charts to use for the 70s through the 90s -- Billboard, Cash Box, R&R, or what.
It's not a terribly sophisticated system, but I like the results. :)
|
|
Keith3000
3x Platinum Member
Joined: September 2003
Posts: 3,369
|
Post by Keith3000 on Apr 26, 2004 22:56:56 GMT -5
Straight inverse points don't seem very fair, because then two weeks at #50 is equal to a week at #1. How? I always thought in an inverse point system, a week at #1 is worth fifty points and a week at #50 is worth one point (for a Top 50 chart).
|
|
Matt4319
Administrator
Joined: September 2003
Posts: 15,215
Staff
|
Post by Matt4319 on Apr 26, 2004 23:02:00 GMT -5
How? I always thought in an inverse point system, a week at #1 is worth fifty points and a week at #50 is worth one point (for a Top 50 chart). Yeah, but this is for a top 100. On the inverse point system, #1 would get 100 points and #50 would get 51 points. This brings up a good point - even with the system I proposed, four weeks at #50 would roughly equal a week at #1. Perhaps the #50 points should be around one-tenth that of #1's. (The difference between #1 and #50 on R&R's Pop chart is about 12-15x.) The inverse exponential thing is pretty wild though... for a top 50, #50 would get 1 point and #1 would get 562,949,953,421,312 (562.9 trillion) points. :o Even for a top 10, #1 would get 512 points compared to 1 for #10.
|
|