>>12753805method for collecting this data.
Step 1) Go to their video page
Step 2) Scroll to the very end, highlight every video
Step 3) Copy everything, paste into a text editor
Step 4) Scroll through the text. Make sure that the line with the video-view total is exactly 7 rows down from the next one. You will find in some cases it isn't, so in those cases just press enter a few times and add a few lines. *This is the most time consuming step, although it can be done rather quickly*
Step 5) Paste all of this info into a single column into your spreadsheet program
Step 6) Using a formula, you can grab every 7th line and line it up in a column. Just google how to do this. Basically you will scroll down this column and copy every 7th row, and therefore grab the view-total for every single video into a single column
My formula looks like this:
>=OFFSET($A$2,(ROW(C2)*7)-11,0)This is grabbing the information from column A, and lining it up into column C. Column B is skipped for no particular reason
Step 7) Using a formula, strip the string "K views" out of the column
>=SUBSTITUTE(C2,"K views","")This is parsing the information in column C, and placing it in column D
Step 8) Using another formula, strip the string "M views" out of the column
>=SUBSTITUTE(D2,"M views","")This is parsing the information in column D, and placing it in column E
Step 9) multiply by 1000
Column F is simply Column D*1000
>=D2*1000But this will not work for the million-view totals
Step 10) Fill the gaps
>=IF(ISNUMBER(F2)=0,E2*1000000,F2)This fills in the gaps left in step 9. If column F has an illegal value, substitute in column E times a million. Place the result in column G. Now column G has ALL the view totals. There might be better ways to do this, but this was my way.
Step 11) Next, set up column J to be equal to column G. We will be graphing with this column
Step 12) Set column I to be numbers counting downward. From the video total down to 1.
Step 13) Set column K to be your weighted average. Like this:
>=AVERAGE(J2:J11)Step 14) Now use column I, J, K to do your graph. Make the x-axis column I.
Step 15) You can also do some statistical averages on this data. Standard deviations and such.
