Wjhonson's Pageviews and Running Total Spreadsheet

Discussion using Calc (written 19 Aug 2009)

For some time, as those who follow me know, I have experimented with creating a list of all my Knols, in Pageview order, and showing a Running Total.  It's not difficult to create such a page, the problems come however when you want to update it from day-to-day or week-to-week.  As some pages move-up the table quicker than others (they are more popular) you will need to move rows as you update.  The Knol WYSIWYG editor provides an easy way to delete or add a row, but not an easy way to move a row.  Also I'm unclear on whether there are actual functions built into Knol to allow adding a column of numbers and displaying the result in another cell of a table.

In my previous write-up Wjhonson's Knols with Pageviews and Running Total I discussed how moving a row is trivial in HTML mode, and this is how I had been doing it a few weeks ago. To move a row in HTML mode, you simply find where it starts at the <tr> tag, and when it ends at the </tr> tag.  You cut from the <tr> tag to the </tr> tag and then paste that entire text into a new location.  Click update and your table will display with the row in its new location.

However moving in-and-out of HTML mode with large tables encounters serious lag issues and so is really unworkable except with tables of perhaps twenty items or less.  I believe this is likely to be because when you go into HTML mode, Knol sends all your table data to your computer locally, and when you move out of it, it sends it all back to Knol again.  I showed in that previous Knol how you could avoid going into HTML mode by creating a mini-table for each line-item and using a special editing technique.  The method outlined there, does not encounter the lag issue and thus is far more workable, i.e. less frustrating.  You still however have to manually re-add the running total column as you go, when you move a row.  In the current Knol, I will show how the process can be improved more perhaps, by using a spreadsheet program.

There is a free, downloadable program called OpenOffice.  It has multiple sub-programs, one of which is called Calc.  Calc opens looking more-or-less like an Excel spreadsheet.  You could probably do the same sequence I outline below, using any spreadsheet program, provided it can export your result in HTML more.

I opened OpenOffice Calc and set-up each cell in the third column to make a partial sum (running total).  I suppose there must be a way to make it more automatic, right now I had to create the sum function for each cell in the third column.  Seems like there should be a way to make a single function for the entire column.  As you will see below, I got lazy after the first forty or so cells, and starting summing only every tenth cell beyond that.

At any rate, using the on-site "My Knols", listed by Pageviews, I created a table in Calc, by manually cutting and pasting each item and it's current pageview total. When that table was complete, I used the Export function in Calc, to turn it into a local web-page (HTML) on my computer.  I then opened that HTML, selected all the text in it, and cut-and-pasted it here into a new Knol.  And then I added this discussion you're reading.

There are two more things I wanted to do however.  I like that Knol adds borders around a table, but I can't see how to do that directly in Calc, when I pasted the result here, it came up without any border. So after I copied it here, I wanted to add a border.  To do this, I add a "dummy" table to the top of this Knol making it just two-by-two.  The size of the "dummy table" doesn't matter because we're going to delete it right away.

Next, I open the page in HTML mode, and at the top find the <table> directive.  You can now see why I added the dummy table, because doing this, directly provides us with the standard Knol <table> directive without the need to remember it.  Now I strip out the old <table> directive that came from Calc, and leave in the Knol <table> directive and then strip out the "dummy table" row and column tags.  For reference the standard Knol <table> directive is <table style="border-color: rgb(136, 136, 136); border-width: 1px; border-collapse: collapse;" border="1" bordercolor="#888888" cellspacing="0">.  That is the standard <table> directive that Knol creates when you make a new table.  And then we see the final result below, with the standard border.

The other thing I wanted to do regards the Heading of the table.  I don't see how in Calc to make a heading that runs across the top of the whole table, but it's easy to do in Knol.  Open the HTML editor, and find the line that looks like this <td style="text-align: left; width: 3.9437in;"> <p>Wjhonson's Pageviews and Running Total</p>

Now change it to look like this <td colspan=3 style="text-align: left; width: 3.9437in;"> <p>Wjhonson's Pageviews and Running Total</p>

The colspan directive tells the table parser that this cell should span three columns, instead of the default one.  Then take out the now-extra <td></td> directives and the extra <p></p> directives from that row.  Then maybe center it and make it larger and you're done.  See the final result below.

[Edit]

The Table

Wjhonson's Pageviews and Running Total as of 8 Jan 2010
Ringworm (Tinea Corporis, Tinea Faciei)	15734
Chairpotato Presents : Full Movies on YouTube	14280
Perioral Dermatitis (Periorificial Dermatitis)	5926
Chairpotato Presents : All Twilight Zone Episodes	5070
Genie:The Wild Child	4995
Chairpotato Presents : Science Fiction Movies on YouTube	4526
Chairpotato Presents : Fantasy Movies on YouTube	4480	63722
Athlete's Foot (Tinea Pedis)	4419
Chairpotato Presents : Comedy Movies on YouTube	4292
List of Top Viewed Authors on Knol	4171
Chairpotato Presents : Adventure Movies on YouTube	3716
Genealogy of Adam and Eve according to the Bible	3677	75286
Chairpotato Presents: Romantic Movies on YouTube	3423
Chairpotato Presents : Horror Movies on YouTube	3410
Chairpotato Presents : Henry Jaynes Fonda	3270
Westerns (Movies) on YouTube	3075
Chairpotato Presents : Dramatic Movies on YouTube	2969	91433
1950s Movies on YouTube	2957
Chairpotato Presents : Alice Ghostley	2567
1940s Movies on YouTube	2572
Chairpotato Presents : Classic Literature on YouTube	2443	104360
1930s Movies on YouTube	2388
Chairpotato's "Night at the Movies!"	2182
Montgomery Clift	2100
Rock and Roll Number One Hits	2055
Musical Movies on YouTube	1983
Genie:The Wild Child : Chapter 2	1947
		114627
My Knol Marketplace	1888
List of Top Viewed Knols	1839
Wizard of Oz	1795
The 100 Dumbest Movies of All Times	1707
Suspense Movies on YouTube	1680
English-language Knol Project	1642
Net Operating Loss Carryforward	1535
Ancestry of Warren Buffett	1512
List of Knols about the Swine Flu	1439
		129664
Jerry Lewis Movies on YouTube	1427
Top 25 Romantic Comedies	1421
Chairpotato Presents : Outer Limits Episodes 1:1 - 1:8	1420
Chairpotato Presents: Full Movies on YouTube by Actor	1406
Biographical Movies on YouTube	1384
Best Picture Oscar Winners on YouTube	1374
1960s Movies on YouTube	1361		midpt
Biography Index	1353
Ahnentafel "Ancestor Table" of Diana Spencer, Princess of Wales	1312
		142122
Ancestry of Farrah Fawcett	1301
Tearjerker Movies on YouTube	1293
Biography of Farrah Fawcett	1281
Law and Order Movies on YouTube	1233
The Royal Ancestry Bible	1205
Lincolnshire Pedigrees : Volume II	1186
Calculating Your Net Operating Loss (contd.)	1170
Juanita Storch (1895-1987)	1110
Jane Austen on YouTube	1108
		153009
Chairpotato Presents: Star Trek Episodes 1:1 - 1:8	1102
1970s Movies on YouTube	1096
Ancestry of Adam West	1094
Audrey Hepburn Movies on YouTube	1092
Calculating Your Net Operating Loss (contd.)	1084
Will Johnson	1068
Genealogy of Clovis, King of the Franks 482-511	1064
English Speaking Knolians	1048
Ancestry of Angelina Jolie	1037
		162694
Ancestry of Jodie Foster, actress	990
Jimmy Stewart Movies on YouTube	985
Entertainment Portal	978
Bessie Wallis Warfield	977
Alec Guinness Movies on YouTube	959
Calculating Your Net Operating Loss	953
Celestion Ditton 100 Bookshelf Speakers	940
Celebrity Family Trees	931
Anderson Lawler (1902-1959), a biography	919
		171326
Curtis Bean Dall (1896-1991)	879
Why Ayn Rand would decry Jimmy Wales' Wikipedia	852
English Meta-Knol Portal	848
Dean Corll	846
Anna Nicole Smith	841
Genealogy of Noah according to the Bible	840
Dictionnaire de la Noblesse	826
Genealogy Portal	817
Lincolnshire Pedigrees : Volume I	793
		178868
Consumer Electronics Portal	789
Marybeth Tinning	788
The Ancestry of all of the US Presidents	756
Chairpotato Presents : Outer Limits Episodes 1:17 - 1:24	748
List of the Episodes of "Bewitched"	743
Buddy-Film Movies on YouTube	740
Greatest Kids Films	729
Nannie Doss	722		2thds
Lists of Greatest Movies	721
		185604
Ancestry of Hilary Duff	716
Bose 201 Series II Direct Reflecting Bookshelf Speakers	713
Eba Lawton Anderson (d.1919)	709
English Americans	701
Bette Davis Movies on YouTube	697
Chairpotato Presents : Outer Limits Episodes 1:9 - 1:16	692
Real Life Movies on YouTube	689
Norman Labs Model 332 Floor-Standing Speakers	685
Jeanne Crain Movies on YouTube	681
		191887
Music Videos on YouTube	681
Legend (Movie)	675
Chairpotato Presents : Outer Limits Episodes 1:25 - 1:32	674
Lincolnshire Pedigrees : Volume III	671
The New England Historical and Genealogical Register Online	667
Ancestry of Shirley Temple	666
Star Wars Episode 2: Attack of the Clones	665
Snodgrass Source Book for Arkansas	659
Knol Biography Portal	650
		197895
Family-Life Movies on YouTube	645
The Importance of Being Earnest, by Oscar Wilde	638
Entertainment on YouTube	635
Marlon Brando	627
Bewitched, 1964-1972, Television Series with Elizabeth Montgomery	624
Planet of the Apes without makeup	623
Barbara Stanwyck Movies on YouTube	622
Coming-of-Age Movies on YouTube	621
Oscar Wilde on YouTube	620
		204783
The Complete Peerage	617
How to catch the Swine Flu	616
Chairpotato Presents : All Beverly Hillbillies Episodes	606
Additions and Corrections to Movies on YouTube	590
Cary Grant Movies on YouTube	589
Errol Flynn Movies on YouTube	588
Disable Ask.com as default search engine	585
Van Johnson, a biography	579
Ahnentafel "Ancestor Table" of Sarah Ferguson, Duchess of York	574
		208894
Wjhonson's YouTube Music Playlists	573
Marilyn Monroe	571
Ancestry of Ed Wood Jr	569
Helpsvc.exe Goes Crazy	567
List of Knols on Television	564
Clark Gable Movies on YouTube	562
Gary Cooper Movies on YouTube	561
Elizabeth Taylor Movies on YouTube	559
List of Top Viewed Knols (Page 2)	553
		213973
Agnes Moorehead	545
Matilda (1101-69), Queen of England 1141	543
Male-line descendants of John Hunt (1749-1831) of New Haven, Connecticut	534
Families of Ancient New Haven	533
KLH AV4001 Speakers	531
Snodgrass One-Name Genealogy Survey	529
What Am I Listening To Right Now?	529
List of Television Episodes lists	526		*
List of Fastest Rising Knol Authors	517
		218760
Burt Lancaster Movies on YouTube	513
The Knols of Wjhonson	510
Twilight Zone : On Veoh.com	504
Farrah Fawcett on YouTube	499
Chairpotato Presents: Star Trek Episodes 1:9 - 1:16	495
Lady Windermere's Fan : A Play About a Good Woman, by Oscar Wilde	494
Ahnentafel an Ancestor Table	489
Disable Google Thumbnails in Firefox	487
Ahnentafel "Ancestor Table" of Charles Windsor, Prince of Wales	484
		223235
Ancestry of Alfred Hitchcock	483
A&E Biography	480
Carl Panzram (1891-1930)	476
Biography/Ranked/25Jan09	474
Dictionary of National Biography	473
The Ancestry of Brad Pitt	473
Ancestry of Audrey Hepburn	455
Mary Poppins	446
Boston Acoustics CR6 bookshelf speakers	446
		227441
Bertha of the Franks	442
Collin's Peerage, augmented by Sir Egerton Brydges 1812	440
Judy Garland Movies on YouTube	438
Why the Britannica Online Encyclopedia Will Fail	437
KLH Model Six Speakers	435
Charles Manson	433
Wjhonson's Pageviews This Week	431
Ahnentafel "Ancestor Table" of Diana Spencer, Princess of Wales	430
The Golden Compass	430
		231357
Montgomery Clift on YouTube	429
Katherine Hepburn Movies on YouTube	426
Henry Fonda Filmography	426
The Picture of Dorian Gray, by Oscar Wilde	425
Santa Cruz' Kitchen Temple	423
List of Real Estate Knols	419
Randolph Scott Movies on YouTube	416
Current Events	413
William Wyler	412
		235146
Ten Greatest Books of All Time	409
Twilight Zone : Where is Everybody?	405
Ancestry of Sonia Sotomayor	403
Calendar of the Fine Rolls	403
Eugene O'Neil	399
List of Knols on Entertainment	397
Joan Crawford Movies on YouTube	394
The Knols of Wjhonson - page 2	394
List of sources at newspaperarchive.com	383
		238733
The Ancestry of Joseph Biden	381
List of English-language Knols about Wikipedia	381
Henry Fonda Movies on YouTube	380
Review of the Best National MLS Websites	379
Ancestry of Dick Van Dyke	378
Best '80s Movies	375
Working with Knol Tables	374
Ancestry of Sally Kirkland, actress	370
List of Most Prolific Knol Authors	368
		242119
Most Popular Wikipedia Pages	360
Entertainment Biographies	355
An Ideal Husband, a play by Oscar Wilde	354
Ahnentafel "Ancestor Table" of Diana Spencer, Princess of Wales	352
Top 25 Science-Fiction Movies	352
Ahnentafel "Ancestor Table" of Diana Spencer, Princess of Wales	348
Arthur Miller	348
Walt Disney's Animated Films	345
Chairpotato Presents: Star Trek Episodes 1:17 - 1:24	345
		245278
Category:Nebraska	344
The life and times of Wjhonson	343
Boston Acoustics HD5 Bookshelf Speakers For Sale	340
Wjhonson's Pageviews and Running Total Spreadsheet	339
Daddy King: Martin Luther King, Sr.	334
Animal Portal	334
Television Portal	333
Calendar of the Close Rolls	327
Collin's (1812) / Earl Poulett	325
		248297
Twilight Zone: One for the Angels	324
Erasure Playlist	323
Knols about Walt Disney, Disneyland, Disneyworld	319
Ancestry of Dale Bumpers, Governor of Arkansas 1971-1975	317
Wjhonson's Stock Pick Details	310
Ancestry of Gary Merrill, actor	309
Wikipedia ArbCom Elections 2009 - My Picks	305
Descendants of Richard Cecil	305
Too Young To Die	305
		251114
Peter Lorre Movies on YouTube	305
Wjhonson's Knols with Pageviews and Running Total	304
Henry Fonda Filmography : Page Two	303
Television on YouTube	302
Other Classic Literature on YouTube	300
Twilight Zone : Where is Everybody? Complete Synopsis	299
RCA STS-520 Cat # 40-5022 Bookshelf Speakers	296
BIC America Venturi V-52 Bookshelf Speakers	294
Snodgrass Source Book for Adair County, Missouri	294
		253811
Pay Dirt : Divorces of the Rich and Famous	289
Knol Geography Portal	288
Boston Acoustics CX4 4" Speaker	287
Jamo S100 Bookshelf Speakers	286
Longest Surviving Matriline	278
Pandro S Berman	276
Snodgrass Source Book for Andrew County, Missouri	274
Snodgrass Source Book for Missouri	270
Snohomish County, Washington Real Estate For Sale	266
		256325
Stock Market Buys and Sells	265
Fritz Kuhn	260
Arthur C. Miller	258
Allen Ginsberg	256
Sources for Medieval Genealogy at BYU Online	257
Ohm Acoustics Tweeter ST7001	255
Wjhonson's YouTube Movie Playlists	253
Category: Entertainment	252
Albert Anastasia	249
		258630
Business Portal	247
JBL Arc Sat Speakers	246
Best Pasta Recipe	245
Jon Hopwood Recovery Project	240
JVC AL-F350 Turntable	237
John Ford	236
Biography/Ranked/13Apr09	235
Henry Jaynes Fonda: List of Source Documents	234
Genesis Chapter 2	231
		260781
General James Cudworth	231
Haunted Los Gatos, the funeral home restaurant	226
Knols about Michael Jackson	223
Wiki Oscurity : Wiki's Which Failed	222
John Osborne	219
Henry Fonda on Television on YouTube	218
Knol United States Portal	217
Top 31 Horror Movies	216
My cousin Bill Clinton	213
		262766
Henry Jaynes Fonda : 1966	213
Edie Sedgwick	211
Wjhonson's YouTube Television Playlists	211
Entertainment Celebrities Portal	211
Damn Yankees	210
Encyclopedia Dramatica on Wikipedians	209
Miriam Elaine Sakewitz, rabbit hoarder	209
Adsense on Knol, Is It Working?	208
The 25 Most Exquisitely Sad Songs in the World	207
		264655
The King James Bible : Genesis	206
Genesis Chapter 5	204
Frank Work Dead of Pneumonia at 92 (obit)	204
Nicholas Tufton and Frances Cecil	203
Genesis Chapter 3	201
Sumner Redstone	200
Genesis Chapter 1	197
Genesis Chapter 4	196
GLBT biographies on Knol	195
		266461
How-to stop bleeding gums	194
Working with Knol Tables - Lesson 2	193
Tim Kaine	192
Ten Companies that will lead us out of Recession	191
Knol Instructions Portal	190
Monarchs of France	189
Nikki Levin Recovery Project	186
It Happened One Night	185
Hunt Family	185
		268166
Charles Bukowski	184
Pick, Universe, Unidata resources	182
William S. Burroughs	178
Wikipedia on Google Groups	170
The Burning Bed (1984)	168
List of the Mayors of Santa Cruz, California	162
Harold Pinter	161
Wikipedia	158
Thomas Milton Tinney, Sr (born 1941)	157
		269686
Annotated Lincoln County Tennessee Wright Marriages	152
Namsung Dual Model LU40PB Satellite Speakers	151
Santa Cruz County, California Real Estate For Sale	151
Annotated Missouri Snodgrass Death Certificates	147
Wikivoices Episode 45	146
Genesis Chapter 6	145
Miriam Elaine Sakewitz, rabbit hoarder	141
Twilight Zone: Mr Denton on Doomsday	134
Frank H Work (1819 - 1911)	133
		270986
The Knols of Wjhonson - page 3	132
What are Dreams?	131
Wikipedia Admins Behaving Badly	130
Wjhonson's Thrift Store	129
Jack Wrangler (1946-2009)	128
Google's Sidewiki	127
Santa Cruz, California	126
People with articles in both Wikipedia and News of the Weird	125
Insignia NS-A2111 Bookshelf Speakers	124
		272138
Thomas Fawcett (1823-1873) of Sonoma	123
Most Prolific Foundation-l Users	116
Why You Should Hire Me	113
Relation Proximity, Consanguinity, Affinity	109
Wikiproject : Wikipedians for Local History / Members	108
Kitty May Ellis	107
Strategies for winning Risk (aka Attack on Facebook)	105
Deluvina Vigil y Montes de Santa Ana DesMarais (1830-1894)	105
Pilchuck Valley Chapel	100
		273124
The Internet Behaving Badly	95
Online genealogical resources for Arkansas	90
Vector Research VRP-275 Satellite Array Speakers	80
Genesis Chapter 8	80
Genesis Chapter 7	80
Descendants of Griffin R Wright	78
What is a Spreadsheet Form?	77
Wikipedia ArbCom Case Index	77
Web Sites Behaving Badly	76
		273857
Web Resources Behaving Badly	74
Acoustic Research AR-4x Speakers	70
WikiProject: Wikipedians for Local History	67
Genesis Chapter 9	57
Calendar of Inquisitions Post Mortem	56
Charles Constantine of Vienne	43
KLH Model 19 Speakers	40
Nick Caruso Dec 2009	40
Rachel Uchitel	37
		274377
Ahnentafel "Ancestor Table" of Camilla Parker-Bowles, Duchess of Cornwall	36
Wikipedia Watch	32
Calendar of the Patent Rolls (1216-1452)	30
English Wikipedia Portal	24
Ancestral Roots, 8th Edition	24
Genesis Chapter 10	24
Robin Hughes	24
Amazon Editors Pick the Best Books for March 2009	23
Ancestry of Lex Barker	15
		274573
Information flavor accounts for Pick flavor people	14
Knol's Recent Vanishing Act 6 Jan 2010	13
Veropedia	11
Aethelweard's Chronicle vs. the Anglo-Saxon Chronicle	10
Early Anglo-Saxon Charters	10
Lipoatrophy and Medi-Cal	10
Achim Koerver on Wikipedia	10
Google Watch	10
98201 Snohomish County, Washington Real Estate For Sale	8
		274669
Aethelweard's Chronicle vs. the Anglo-Saxon Chronicle (Aethelwulf)	7
How to edit your webpage in Firefox	6
The Chroniclers	5
Integrating local content with your browser	4
Orderic Vital, History of England and Normandy	3
Franklin County, Arkansas Genealogy	3
Contacts and Credits	0
An Incident at Four O'Clock	0
		274697
Collections
Knol Directory	1254
People and Society	276
Full-length movies on YouTube	272
Gardens and Gardening Collection	185
Recipe Collection	177
Internet Business Collection	157
		277018

[Edit]