Register

Creating A Pedigree Instantly Using Excel

Discussions and questions about how best to keep your breeding program running smoothly.
2 years of membership2 years of membership
Posts: 157
Joined: April 27, 2016
Location: Kenosha, WI
United States of America Male
Thanks: 46
Thanked: 60 in 40 posts
BunnyBucks: 907.00

Creating A Pedigree Instantly Using Excel

Post Number:#1  Unread postby KenoshaRabbits » Tue May 24, 2016 3:00 pm


Hi there, I thought I would share how to make a pedigree using excel. With this you could type in the name of a rabbit, or its ear tattoo, or any sort of identifier and it will fill in a pedigree for as many generations as you want. I will have a blank Excel at the end of the post.

To do this we use two EXCEL Formulae, MATCH and INDEX. These are two specialized formulae. A lot of people probably are familiar with simple formula like " =A1+A2" which will add the numbers in A1 and A2. There are also formula like =SUM(A1:A2) which will also add the numbers in A1-A2.

The formula MATCH is written =MATCH(A, B, C) which means look for term A in column B with a match value of C; and this will return a value corresponding to the row term A is in. For an exact match C=0

The formula INDEX is written =INDEX(X, Y) which means look at row Y in column X; and this will return whatever is written in that cell.

We can combine the two like so =INDEX(X, MATCH(A, B, 0)) which will look up term A in column B and tell us what is in column X of that same row. So I could type in a name of a rabbit and it could output the name of sire of that rabbit. I could paste a similar formula in a different box but replace term A with the cell that has the sire of the rabbit and it would tell me the sire of the sire; or grand sire. do that again and you can have the great grand sire.

-----------------------
So how to accomplish this. First write in the information you want to record. You could record whatever information you want; perhaps you want to record a registration number or a genetic code.
Sample Table.png
Sample Table.png (35.32 KiB) Viewed 1542 times

Next you want to make the information into a table. To do this; highlight the information and near the top of your Excel screen should be an option to format as a table. Make sure the "My table has headers" box is checked after you click on the style you want you table to be in.
Format as Table.png
Format as Table.png (77.3 KiB) Viewed 1542 times

This should create a table. The next step is to name the table, although this is optional as the table will have some sort of default name like Table 1. First click on the table, anywhere on the table will do. If you look at the top of your screen, there should be an option near the right side labeled "Design" under the words "TABLE TOOLS"; click this. This will allow you to change the look of your table. On the left side of the screen there is a box which allows you to name the table. Type in whatever you want to call it. This will be the name you enter in the INDEX/MATCH formulae above. I called mine master.
Name Table.png
Name Table.png (77.93 KiB) Viewed 1542 times


When you have your table finalized you can add information at the bottom and it will attach to the table, such as adding information about a new rabbit. You can also add a column to the right and it will attach. You can also insert a column in the middle of the table.

Now that the table is named we can use write the formula to find specific information. So =INDEX(X, MATCH(A, B, 0)) X and B written in the form TABLENAME[ColumnHeaderName] and A refers to the cell with the information to look up. So my table is named Master and has columns Name, Sire, Dam.
So if I type the name of the rabbit in A1 and want the name of the sire in B1 I would type in the cell B1 =INDEX(Master[Sire], MATCH(A1, Master[Name], 0)) this will look for whatever is in cell A1 in the Name column of table Master and tell me what is in the Sire column of table Master.
Now if I want to find the sire of the sire in cell C1 I would write =INDEX(Master[Sire], MATCH(B1, Master[Name], 0)) and this will look for whatever was returned in cell B1 in the Name column of table Master and tell me what is in the Sire column of table Master.
If I wanted the breed of the grandsire listed below in cell C2 I would have =INDEX(Master[Breed], MATCH(C1, Master[Name], 0))

You can have a separate sheet in the Excel book for pedigrees. Simply have a Cell where you enter some sort of unique identifier for the rabbit and then base the INDEX/MATCH off that cell.

Here is a copy if this is too confusing for anyone. There is a tab where you can type in the rabbit's name and get a pedigree, and a tab where you can type in the rabbit's ear tattoo and get a pedigree.
Sample Pedigree.xlsx
(22.06 KiB) Downloaded 286 times

* Feel free to use the template for your own personal use or modify it with the particular information you keep track of.

***TIPS***
1) The formula will only return information that is in your table. So in the sample sheet I have information about Oscar such as weight color sire and dam. But I don't have any of that information on the sire and dam. So if I entered Oscar's name on the pedigree, it would fill in his tattoo, date of birth, etc. It would fill in the name of the sire and the dam, but it wouldn't fill in the tattoo of the sire or the sire's sire and so on. It would just show up as #N/A

2) Make sure your identifiers are unique. This could be an issue if you reuse names, tattoos or any other identifier. So if you have two rabbits named Yankee or tattoo RAB01 there will be problems. It appears that the formula will return the instance nearest the top, which might not be the rabbit you need.

3) Make sure information matches up. Misspellings and differences do matter. If your rabbit Lima's sire is X-Ray and you have a row with X-Ray's information; make sure you enter the name X-Ray (with a hyphen) in the name column. If you type XRay the formula won't pick this up. However uppercase/lowercase differences do not matter. X-Ray in the sire column will link to x-rAy or X-raY in the name column.

4) Sorting a column or filtering the column will not effect the ability to create a pedigree. So if you wanted to filter the list to only show rabbits that are currently in your rabbitry, you would only see the rabbits that are on hand. The rows are hidden but they are still in the table. So the pedigree chart will still pull their information. If you sort by gender or weight or ear number this won't mess things up. If you insert columns or rows this won't mess things up either. So if you want to have a column for genetics or # of kits or whatever you can insert them wherever you want.

If you have any questions feel free to ask.

-- Tue May 24, 2016 3:00 pm --

***Printing***
If you make a pedigree in Excel don't worry about trying to format cell sizes so that it fits on one page. Click on "FILE" then on "PRINT". In the settings there is an option to scale the sheet. It's default should be "No Scaling". You can change it to "Fit sheet on one page". This should scale everything so that it fits on one page.
Fit on Shee.png
Fit on Shee.png (67.56 KiB) Viewed 1537 times


-- Tue May 31, 2016 12:34 pm --
*******
Feel free to use the template for your own personal use or modify it with the particular information you keep track of.
Last edited by KenoshaRabbits on Tue May 31, 2016 11:35 am, edited 1 time in total.

The following user would like to thank KenoshaRabbits for this post
Bre, Homer, HOWsMom, jimmywalt, Nymphadora, PSFAngoras, SoDak Thriver, SuburbanHomesteader, Zass

Site Supporter
4 years of membership4 years of membership4 years of membership4 years of membership
User avatar
Posts: 2773
Joined: August 11, 2014
Location: Idabel, OK
Thanks: 58
Thanked: 689 in 568 posts
BunnyBucks: 15,052.00

Re: Creating A Pedigree Instantly Using Excel

Post Number:#2  Unread postby alforddm » Tue May 24, 2016 5:45 pm


Wow that is impressive. Thanks for writing all that up.

2 years of membership2 years of membership
Posts: 157
Joined: April 27, 2016
Location: Kenosha, WI
United States of America Male
Thanks: 46
Thanked: 60 in 40 posts
BunnyBucks: 907.00

Re: Creating A Pedigree Instantly Using Excel

Post Number:#3  Unread postby KenoshaRabbits » Tue May 24, 2016 6:11 pm


alforddm wrote:Wow that is impressive. Thanks for writing all that up.

No problem. I figured people would like to learn how to do this in Excel. It's quicker than having to look up the information on 15 separate rabbits and entering all the data.

Site Supporter
2 years of membership2 years of membership
User avatar
Posts: 150
Joined: April 28, 2016
Location: Northeast Kansas
United States of America Female
Thanks: 69
Thanked: 36 in 31 posts
BunnyBucks: 857.00

Re: Creating A Pedigree Instantly Using Excel

Post Number:#4  Unread postby Winterwolf » Tue May 24, 2016 8:13 pm


Thank you so much for this tutorial, KenoshaRabbits! Very helpful information. I'll have to give this a try. :)
Misty Creek Rabbitry
Mini Lops and French Angoras
Northeast Kansas, USA
http://mistycreekrabbitry.weebly.com
https://www.facebook.com/MistyCreekRabbitry/

2 years of membership2 years of membership
Posts: 157
Joined: April 27, 2016
Location: Kenosha, WI
United States of America Male
Thanks: 46
Thanked: 60 in 40 posts
BunnyBucks: 907.00

Re: Creating A Pedigree Instantly Using Excel

Post Number:#5  Unread postby KenoshaRabbits » Tue May 31, 2016 11:37 am


I made a little note on the above post. Feel free to use the attached template for your own personal use or modify it with the particular information you keep track of.

I figured most people would assume I was giving them permission to use it; but just in case I'll make it explicit. Download it, enter your own info, add new columns, create tabs for individual bunnies, etc.

4 years of membership4 years of membership4 years of membership4 years of membership
User avatar
Posts: 520
Joined: June 4, 2014
Location: Michigan
Thanks: 222
Thanked: 50 in 38 posts
BunnyBucks: 3,797.00

Re: Creating A Pedigree Instantly Using Excel

Post Number:#6  Unread postby jimmywalt » Tue May 31, 2016 8:20 pm


You ABSOLUTELY ROCK!!!!!!!

Thank you, thank you thank you!!!!!!!!

:thankyou: :thankyou: :thankyou: :thankyou: :thankyou: :thankyou:

The following user would like to thank jimmywalt for this post
KenoshaRabbits

Site Supporter
3 years of membership3 years of membership3 years of membership
User avatar
Posts: 215
Joined: November 12, 2015
Location: South Dakota near the river.
Male
Thanks: 49
Thanked: 64 in 43 posts
BunnyBucks: 1,191.00

Re: Creating A Pedigree Instantly Using Excel

Post Number:#7  Unread postby SoDak Thriver » Wed Jun 01, 2016 11:02 am


This is wonderful!
Homeschooling dad of 4 (aged 14, 10, 8, 3), accountant, and rabbit noob apprentice.

The following user would like to thank SoDak Thriver for this post
KenoshaRabbits

Posts: 5
Joined: August 26, 2018
Thanks: 2
Thanked: 0 in 0 post
BunnyBucks: 25.00

Re: Creating A Pedigree Instantly Using Excel

Post Number:#8  Unread postby Bre » Sun Sep 09, 2018 7:57 am


Thanks a lot! I was looking for something like this!

Do you know if there is a way to add the inbreeding coefficient in Excel? I know how to calculate them, but my excel abilities are too rusty to be able to do it right now.

-- Sun Sep 09, 2018 7:57 am --

Here is the formula for the inbreeding coefficient according to Wright

I cannot add the formula nicely, but here it should be understandable
FX = Σ[(1/2)to the power of n1+n2+1 times(1+FA)]

FX Inbreeding coefficient of the animal in question
FA Inbreeding coefficient of the common ancestor
n1 Number of generation from the sire to the common ancestor
n2 number of generation from the dam to the common ancestor

On paper, or on an online pedigree, it is quite easy to count the number of generations to the common ancestor, but with excel I do not know how to do it. Can anyone help me?

Who is online

Users browsing this forum: No registered users and 2 guests