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.
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.
Cool! Now if you get holy water into a spreadsheet too we can attack that vampire I unleashed in your contest!
“…to calculate the number of characters in each book…”
Interesting. I’ve heard that Jesus was quite a character.
I love it! Can’t wait for Excel 2007 “large print and pretty colors” version :D
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.
I had no idea that the Bible had so much Excel content. Here are some word counts:
cell: 70
rows: 95
columns: 0
sheet: 4
scroll: 2
icon: 7
range: 297
borders: 43
print: 5
windows: 30
I really like this! How long did it take?
Very good!!
I 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
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.
Good Catch, Ioannis. Change both occurrences of “B2? to “B1?.
The other array formulas have the same error. I just posted a corrected version.
See here … http://www.thedailywtf.com/forums/62227/ShowPost.aspx .. fool
This is so neat but I’d like it much better if it were the New Revised Standard Version!
Has anyone found a word that only appears once?
smart.
This is actually pretty cool. When I have some time I have to make a Red Letter version.
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?
Best Regards Sige
Ostriche appears only once in the whole bibile (in Lamentations)
14 years later, I’d like to access your work: Bible in Excel (old version is OK). That link in your blog is no longer working. So, it will be great if you could help me here. Thank you so much.
I think John took his site down. I found a copy on archive.org.
http://dailydoseofexcel.com/excel/king-james-bible.zip