Can someone point me towards the right direction to ask advice on excel and spreadsheet formulas.

 

I want to know how to do a formula that IM SURE is used when people do that "point system" where number 1 = 100, 2= 99 and so on.

 

Any links will be GREATLY appreciated.

Can someone point me towards the right direction to ask advice on excel and spreadsheet formulas.

 

I want to know how to do a formula that IM SURE is used when people do that "point system" where number 1 = 100, 2= 99 and so on.

 

Any links will be GREATLY appreciated.

 

I do mine using my brain power! :lol: I just add whatever the position is to another number which will equal 101. (e.g #45 + 56 points = 101) Although it would be quite helpful to have a formula for it!

Edited February 29, 201213 yr by Klumzee

Put into the sum formula 101-1, 101-2, 101-3, 101-4, 101-5 etc :)

I do mine using my brain power! :lol: I just add whatever the position is to another number which will equal 101. (e.g #45 + 56 points = 101) Although it would be quite helpful to have a formula for it!

 

Agreed! it would save LOADS of time!

 

Surely they use a formula to add the personal chart up :/

I'm not sure I entirely understand the question....

 

How do you do it now? Are you trying to auto-fill your chart or do you just want something to add up to make a total?

I'm not sure I entirely understand the question....

 

How do you do it now? Are you trying to auto-fill your chart or do you just want something to add up to make a total?

 

Basically If use Jessie J as an aexample..

 

If Domino had a chart run of (1-3-7-11-15-x)

 

I'd want to find the total for its points.

so 1 would equal the value of 15, 3-13, 7-9, 11-5 and 15 would be 1. so the total would be 43.

 

Basically I want to assign the numerical value of a number to a different number :P

 

V. complicated i know :cry:

What I have just done is if you have 1-100 in column A and the rest of the information in colums B, C and so on.

 

Do a Descending sort on column A only then you will reverse the numbers only, and Keep the info where it is.

I just use the same spreadsheet as for my YTD's - I merely change from assigning a sales figure to each position, to a value: #1=200, #2=199... #200=1, then the spreadsheet does all the number-crunching. :)

I just use the same spreadsheet as for my YTD's - I merely change from assigning a sales figure to each position, to a value: #1=200, #2=199... #200=1, then the spreadsheet does all the number-crunching. :)

 

How do you assign that:) and thanks vidcapper. !! :D

How do you assign that:) and thanks vidcapper. !! :D

 

I don't know how experienced you are with Excel, but I use a vlookup table.

 

Basically, each week it looks up what position a song/album charted at, then assigns the sales figure I've estimated for that week/position, then totals these figures up for however many chart weeks have elapsed that year. Then I just sort them into descending order.

 

Obviously it's rather more complicated in reality (lots of cross-checks, etc.), but that's the gist of it.

 

To further illustrate...

 

I have two separate tables, one for chart positions, and one for sales estimates - the clever bit is how excel pulls it all together. :)

 

As for assigning values to chart positions, I have one row for each of the 200 positons, and one column for each week : e.g.

 

Pos Wk1 - Wk2 - ... Wk52

#1 200 200

#2 199 199

#3 198 198

.

.

.

#200 1 1

 

You can see where I'm going with this... :)

 

In my YTD spreadsheets, I just have sales estimates instead of 200, 199, 198 etc.

Edited February 29, 201213 yr by vidcapper

What I used to do is actually very easy.

 

Here's the example

 

http://i958.photobucket.com/albums/ae67/Pavvii/fkjgh.jpg

 

Points=(41, if it's Top 40)*(number of cells that contain a number - there's a 'COUNT' function in Excel for that)-(sum of all chart positions)

 

Very useful for YTD charts if you update it regularly.

 

You can also fit the whole year into one tab.

Edited February 29, 201213 yr by Ljósið

That looks great! THANKS.

 

BUT

 

i tried doing the first one .. 1,1,2.

 

and i got -697 :L

 

I put (41*Count(A2:A4)-820)

 

:/

 

That looks great! THANKS.

 

BUT

 

i tried doing the first one .. 1,1,2.

 

and i got -697 :L

 

I put (41*Count(A2:A4)-820)

 

:/

 

Shouldn't the last bit be -4? Seeing that 1+1+2=4 :P

Never mind FINALLY got it. I LOVE YOU thank!!!!!!!!!!!! yeeeeeeeeeees SO AHPPY

Gawwd sorry guys but one more question. How do make the whole spreadsheet go in the order of the points. so i wouldnt have to move a song it would do so naturally :)

Gawwd sorry guys but one more question. How do make the whole spreadsheet go in the order of the points. so i wouldnt have to move a song it would do so naturally :)

 

 

 

Use Sort under the "Data"Tab.

Gawwd sorry guys but one more question. How do make the whole spreadsheet go in the order of the points. so i wouldnt have to move a song it would do so naturally :)

If you do it the way I just showed, you just copy the song title and points somewhere else and sort it by number of points. This way you won't be messing up the scoreboard. Either way, there's no way to do that automatically (at least that I know of) but using the sort function everytime you need to get updated results. Not that it takes more than a few seconds anyway.

Ok thank you. And 1 more thing again ha-ha

 

Say i just wanted to see the songs by Rihanna. is there a way for me to type Rihanna. and only songs by her will show?

(Auto)filter. A very useful thing.

is that under data, auto filter?