These Excel tools have been superceded by the javascript based tools in the
Y-DNA Comparison Utility



Genetic Distance Calculator Macro

Please note: This calculator differs from FTDNA calculations for YCA II a/b. FTDNA uses the infinite allele model on this marker while this calculator currently uses the stepwise model. Also, this calculator does not handle the rare cases of DYS464e/f/g the same as FTDNA. I hope to update the calculator soon. Please refer to FTDNA's The GAP Quick Interpretation Guide for more information

Also, if you cut and paste from the FTDNA generated surname web page you may need to insert a blank row between the marker titles and the allele values.

In Excel, select the rows containing the haplotypes for which you want to determine genetic distances.

This macro intends to get the same results as the Ysearch genetic distances. I have compared the results of this macro with the genetic distance calculations on Ysearch and they match for all the cases I tested. Please let me know if you find cases where they don't match and what specific ID's don't match.

Note that the header rows must identify the markers corresponding to each column. The macro allows various formatting of the marker identification. You can use direct cut-and-paste copying from FTDNA or Ysearch haplotype comparison tables, or you can have your own identifiers as long as the header information doesn't go more than 11 rows.

The macro tries to identify the start of the haplotype data by first searching for the DYS390 identifier (it just looks for "390"), then goes down that column until it finds the first number after 390. Then it goes left until it finds the first non-numeric entry or a number larger than 100. The last valid number is deemed to be the upper left corner of the overall table. This is used to identify where the marker identifiers start.

Then the macro tries to find the special cases (DYS389i, DYS389ii, and DYS464a..DYS464g). If it finds these special cases, it uses a specific way of determining the genetic distance for these markers.

There are two worksheets that are created when the Genetic Distance macro is executed: 1)"Genetic Distance", and 2)"Statistics". Please look at the statistics worksheet to verify that all the markers you expected were used and that the special cases that exist in your data were used the number of times that there are entries in the original table.

Execute the "FindGeneticDistance" macro and the following table results:

Note that the first row is normally displayed horizontally. To get the vertical display for a more readable table, in Excel, select the first row then select [Format][Cells][Alignment] and in the orientation window type 90 in the "degrees" box or drag the alignment needle to 90 degrees.

Dean McGee

McGee Surname DNA Project

Page last updated on June 26, 2004