ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Version 1.8
I don't like MISTAKES! Help me eliminate mine! PLEASE email Chad.Bornholdt@gmail.com or find me on Facebook if you find a mistake! AND note the VERSION # !!!
There is a macro used only for auto-sizing the Cell Comments. The macro is safe. I (Chad Bornholdt) wrote it.
2
The Sheets & Workbook are password protected only to prevent accidental typing. Leave it protected or you could damage it. The password is TOLKIEN.
3
HOW TO USE:The ancestry guide is on the ANCESTRY tab.
4
5
RESEARCH:The majority of the Ancestry spreadsheet is hyperlinks to Tolkien Gateway. Click any blue hyperlink to research.
6
7
I included every named character that Tolkien did not explicitly scrap.
8
For a teaching example, we'll use "Arwen"
9
10
EXPLANATION:
11
Column:Description of Column:Arwen's Example:Chad's Description of Arwen's Example
12
Column ACharacter_Name (Any other names, nicknames, & abbreviated titles are in parentheses)Arwen (Undómiel, Elerondiel, Elrenniel)The other names will make it easier to search & find a character if you only know them by one of their many names.
13
Column B:

(HIDDEN)
I used this to ensure I had no typos.
You should NOT unhide this Column.
Do not worry about this HIDDEN Column.
14
Columns C through F:

(Birth Date)
Many characters don't have a defined birth date.
When the dates are unspecified, a window of "Earliest_Possible_Birth_Date" & "Latest_Possible_Birth_Date" can be derived.
These dates are derived by citing the dates of events in which that character participated, such as a battle or another character's lifespan.
When a character has a cited birth date, both the "Earliest_Possible_Birth_Date" & the "Latest_Possible_Birth_Date" will have the same dates.
For Arwen, these numbers are the same because we know the exact year she was born.
This won't be so easy for some characters.
15
Column C:Earliest Age in which the character could have been born.T.A.The earliest Age in which Arwen could have been born was the Third Age.
16
Column D:Earliest Date within the [Column C] Age that the character could have been born.241The earliest Date within the Third Age that Arwen could have been born was 241.
17
Column E:Latest Age in which the character could have been born.T.A.The latest Age in which Arwen could have been born was the Third Age.
18
Column F:Latest Date within the [Column E] Age that the character could have been born.241The latest Date within the Third Age that Arwen could have been born was 241.
19
Columns G through J:

(Death Date)
The [Death Date] system works the same as the [Birth Date] system.Just like her birth, we also know the year of her death, so this is easy.
Many won't have the precise date, so derivations must be made.
20
Column G:Earliest Age in which the character could have died.Fo.A.The earliest Age in which Arwen could have died was the Fourth Age.
21
Column H:Earliest Date within the [Column G] Age that the character could have died.121The earliest Date within the Fourth Age that Arwen could have died was 241.
22
Column I:Latest Age in which the character could have died.Fo.A.The latest Age in which Arwen could have died was the Fourth Age.
23
Column J:Latest Date within the [Column I] Age that the character could have died.121The latest Date within the Fourth Age that Arwen could have died was 241.
24
25
Columns K through N:

(HIDDEN)
These 4 hidden columns are how I translated the [Age + Year] into straight Sun-years (Löar) after the Ainulindalë.
You should NOT unhide these Columns.
They reference a hidden Tab called "Years", which contains all the relative number of Sun-years in that Age.
You should NOT unhide the Tab.
This is hidden but I'm explaining
here for your understanding.
Because we know Arwen's exact dates, these numbers match.
They won't match if a character's exact dates are unknown.
26
Column K:Earliest possible time, in Sun-years, after the Ainulindalë that the character could have been born.724272Arwen was born, at the least, this many Sun-years after the Ainulindalë.
27
Column L:Latest possible time, in Sun-years, after the Ainulindalë that the character could have been born.724272Arwen was born, at the most, this many Sun-years after the Ainulindalë.
28
Column M:Earliest possible time, in Sun-years, after the Ainulindalë that the character could have died.727173Arwen died, at the least, this many Sun-years after the Ainulindalë.
29
Column N:Latest possible time, in Sun-years, after the Ainulindalë that the character could have died.727173Arwen died, at the most, this many Sun-years after the Ainulindalë.
30
31
Columns O through P:

(Life Span)
Now that I know the possible Sun-years after the Ainulindalë, I can state the character's age at time of death.Because we know Arwen's exact dates, these numbers match.
They won't match if a character's exact dates are unknown.
32
Column O:Youngest the character could have been at time of death.2901Arwen was at least this many Sun-years old when she died.
33
Column P:Oldest the character could have been at time of death.2901Arwen was at most this many Sun-years old when she died.
34
35
Columns Q through R:

(Age during War of the Ring)
I did this just to give relevant ages during the events of Lord of the Rings (since this info will interest many.)

(This must be disregarded if the character wasn't living in 3019.)
Because we know Arwen's exact dates, these numbers match.
They won't match if a character's exact dates are unknown.
36
Column Q:Youngest the character could have been in 3019.2778Arwen was at least this many Sun-years old in 3019.
37
Column R:Oldest the character could have been in 3019.2778Arwen was at most this many Sun-years old in 3019.
38
39
Columns S through AD:

(HIDDEN)
I used these Columns to ensure the "Quick Reference" numbers would match up.
You should NOT unhide these Columns.
40
41
Column AE:GenderFemaleArwen was female.
42
43
Column AF:
The FATHER is copied from Column A. NOTE: [Columns AJ through FI] REQUIRE this Cell to be IDENTICAL to the father's [Column A] Cell (because it figures the blood percentage by FINDING the parents & averaging!!!)
Elrond (Elerondo, Peredhel, Half-elven)Elrond was Arwen's father.
44
Column AG:
The MOTHER is copied from Column A. NOTE: [Columns AJ through FI] REQUIRE this Cell to be IDENTICAL to the mother's [Column A] Cell (because it figures the blood percentage by FINDING the parents & averaging!!!)
CelebríanCelebrían was Arwen's mother.
45
46
Columns AH through AI:

(Quick Reference
for "Half-elven")
These 2 Columns give a quick reference (saves the need to scroll) if the character is [Part Elf].

NOTE: This is only those 2. Melian's strand of Maia blood is not accounted for in the Green/Blue because it has its own Column.
This Quick Reference is just for [Part Elf].

(Note that Arwen is part Maia. 78.13% + 18.75% = 96.88%.
That means there is 3.12% Maia blood not accounted for in the Green/Blue because it is in a subsequent Column.)
47
Column AH:Percentage of Elven blood.78.13%Arwen is 78.13% Elf.
48
Column AI:Percentage of Human blood.18.75%Arwen is 18.75% Human.
49
50
The REST of the Columns!!!
Columns AJ through FI:
All the rest of the Columns use Excel's VLOOKUP function to:
a. find the parents
b. figure the parents' blood percentage of THAT Column
c. calculate that number by an adjustable "unknown parent factor" (This is explained last.)
51
Each [Column Set] is arranged where the top-most label is the earliest Clan.
When Clans subdivided into sub-Clans, those are separated into separate Columns under the top-most.
(& so forth)
52
53
FOR EXAMPLE!!!!
54
55
Columns AJ through AN:

(Ainur)
These Columns cover all possible breakdowns of AINUR.
Yellow Cells are the earliest ancestors. White Cells are the descendants of those earliest ancestors.
56
Column AJ:The VALAR subset of AINUR. If a character has any VALA blood percentage in them, this cross-Cell will not be 0%0%Arwen has no Vala blood in her.
57
Column AK:
The VALIER subset of the VALAR subset of AINUR. If a character has any VALIE blood percentage in them, this cross-Cell will not be 0%
0%Arwen has no Valie blood in her.
58
Column AL:The MAIAR subset of AINUR. If a character has any MAIA blood percentage in them, this cross-Cell will not be 0%3.125%Arwen has 3.125% of Melian's blood strand in her.
59
Column AM:
The BALROG subset of the MAIAR subset of AINUR. If a character has any BALROG blood percentage in them, this cross-Cell will not be 0%
0%Arwen has no Balrog blood in her.
60
Column AN:
The ISTARI subset of the MAIAR subset of AINUR. If a character has any ISTA blood percentage in them, this cross-Cell will not be 0%
0%Arwen has no Ista blood in her.
61
62
Columns AO through BB:

(Elvish)
This is repeated for the Elvish blood subsets[VARIOUS]Arwen has Vanyar, Noldor, Falmari, & Eglath blood
63
64
Columns BC through BF:

(Elvish Status)
These 4 Columns tell you the blood % of Elves who saw the Trees.
The yellow Cells physically saw the Trees. The white Cells are descendants of those who saw the Trees.
[VARIOUS]Arwen has the blood of Amanyar, Exiles, & Moriquendi
(besides being a Moriquendi herself. If the Amanyar isn't yellow, they're Moriquendi.)
65
66
Columns BG through CG:

(Human)
This is repeated for the Human blood subsets[VARIOUS]Arwen has the blood of Haleth, Bëor, & Hador.
67
68
Columns CH through EC:

(Dwarven)
This is repeated for the Dwarven blood subsetsArwen has no Dwarf blood.
69
70
Columns EP through EW:

(none of the above)
This is repeated for the other blood subsetsArwen has no [other] blood.
71
72
Columns EX through FI:

(Enemies)
This is repeated for the non-human enemy blood subsetsArwen has no enemy blood.
73
74
ROW 11For each Column in Row 11, you have a 100% or a 50%.
These numbers can be adjusted, but they are for evaluating the consequence of knowing only 1 parent.
If only 1 parent is known & the number in Row 11 is 100%, then that child will adopt the same % blood as that 1 parent had in that subset.
If only 1 parent is known & the number in Row 11 is 50%, then that child will adopt 1/2 the % blood as that 1 parent had in that subset.
50%Look at Cell AL11. This Cell assigns the child 50% of the parent's Maia blood value, regardless of whether only 1 parent is known.
Arwen has 3.125% Maia blood, passed down from Melian.
We know both of Arwen's parents. Only Elrond had Melian's blood. So 50% of Elrond's Maia blood % is passed down to Arwen.
Had we only known 1 of Arwen's parents, we would still assign 50% of that parent's Maia blood, because AL11 says 50%.

Now look at AT11. This Cell assigns the child 100% of the parent's Eglath blood value if only 1 parent is known, but 50% of each if we know both. (Basically, if only 1 parent is listed (usually the father), we assume the father married within his own clan.)
Arwen has 40.63% Eglath blood, passed down from Celeborn.
We know both of Arwen's parents. So 50% of Celebrían's Eglath blood % is passed down to Arwen.
Had we only known 1 of Arwen's parents, we would assign 100% of that parent's Eglath blood, because AT11 says 100%.
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100