Bible Workbook
File this under "The last thing you'd expect from John Walkenbach." It's the Bible in an Excel workbook.
I found a text file with the complete King James Version of the Bible. I wrote a few macros and dumped it all into an Excel workbook. Each book is on a separate worksheet, and each verse is in a separate cell.
It has a handy hyperlink table of contents so you can jump to any book. I also wrote some summary formulas to calculate the number of characters in each book, and the average number of characters per verse. Then I added a word count feature: Enter a word and it displays the number of occurrences in each book. Finally, I set up a User Form that displays a random verse.
You can download it here. It's a 1.8 Mb zip file, and it expands to a 6.1Mb Excel workbook. Even if you have no interest in the content, you may discover a few useful techniques. You'll need to enable macros to use the random verse feature.
Toad:
Guaranteed to be the first bible from an atheist spreadsheet author! I can’t wait for your Powerpoint version of The Book of Mormon, John.
21 February 2006, 9:23 pmTerry Grignon:
Cool! Now if you get holy water into a spreadsheet too we can attack that vampire I unleashed in your contest!
21 February 2006, 9:45 pmDick Kusleika:
“…to calculate the number of characters in each book…”
Interesting. I’ve heard that Jesus was quite a character.
21 February 2006, 11:57 pmpivot peer:
I love it! Can’t wait for Excel 2007 “large print and pretty colors” version
22 February 2006, 2:53 amBillkamm:
I had a professor in college by the name of Dr. David Dailey. He is a computer science professor at Slippery Rock University. He used to do random stuff like this ALL THE TIME. Infact we had assignments where the task was to “find a bunch of random data like a dictionary or bible and do something cool with it”.
I think you two would be really great friends if you knew each other. He was one of my favorite professors and you are one of my favorite authors, so it all makes sense.
22 February 2006, 8:02 amJohn Walkenbach:
I had no idea that the Bible had so much Excel content. Here are some word counts:
cell: 70
22 February 2006, 9:20 amrows: 95
columns: 0
sheet: 4
scroll: 2
icon: 7
range: 297
borders: 43
print: 5
windows: 30
Ken Clifton:
I really like this! How long did it take?
22 February 2006, 9:02 pmIoannis:
Very good!!
23 February 2006, 9:01 amI think, the function “Count of a text string”
{=SUM(LEN(UPPER(INDIRECT(”‘”&C5&”‘!B2:B”&D5)))-………}
It doesn’t work for the first row B1.
Yes?
Ioannis, Athens
John Walkenbach:
Ken, I think it took about an hour to get all of the text in there, separated into sheets. Then I probably spent anther two hours or so adding new features to it.
23 February 2006, 9:38 amJohn Walkenbach:
Good Catch, Ioannis. Change both occurrences of “B2″ to “B1″.
The other array formulas have the same error. I just posted a corrected version.
23 February 2006, 4:15 pmYourea Fool:
See here … http://www.thedailywtf.com/forums/62227/ShowPost.aspx .. fool
1 March 2006, 6:46 amjohni:
This is so neat but I’d like it much better if it were the New Revised Standard Version!
1 March 2006, 8:36 amTom:
Has anyone found a word that only appears once?
1 March 2006, 11:12 pmTom:
smart.
1 March 2006, 11:19 pmDarren Clark:
This is actually pretty cool. When I have some time I have to make a Red Letter version.
2 March 2006, 8:39 pmSige:
Hi there,
I was wondering … could you “convert”
{=SUM(LEN(UPPER(INDIRECT(”‘”&C5&”‘!B1:B”&D5)))-………}
into an array-formula that counts only the visible rows?
Sort of:
{=SUBTOTAL(9;(LEN(UPPER(INDIRECT(”‘”&C5&”‘!B2:B”&D5)))-………}
Which does not do it of course …
Anyone?
3 March 2006, 3:24 amBest Regards Sige
AndyMc:
Ostriche appears only once in the whole bibile (in Lamentations)
9 March 2006, 12:59 pm