Re: New variants spreadsheet

Joel Hartley

Thanks, Jared,

That should be a great resource. I'll send some of my SNPs your way once I sort them out. YFull has them as:

Best Quality (5)
Acceptable (6)
Ambiguous (20)

I'm guessing you just want the 1st 2 categories?


On 2/20/2017 6:44 PM, Jared Smith wrote:
I have uploaded a new spreadsheet to

This likely has limited utility for anyone other than me, but I
thought I'd share it. This file is used for analyzing Y-DNA mutation
variants (SNPs, insertions/deletions, etc.) that us Z16357 people
have. It's a very large spreadsheet with complex calculations - minor
changes like sorting can take a long time to calculate.

The Variants tab includes all 68,355 unique variants that we have.
These were collected from Big-Y VCF files.

You can use the Lookup tab to query specific DNA position numbers to
see the values each of us have at that position.

The Shared Variants tab shows all known variants ***AT OR BELOW
Z16357*** that at least 2 of us have. This allows easy analysis of the
consistency of SNPs and determination of their position on our
branches. A "+" indicates a positive test for that variant. A "***"
indicates the variant was identified, but the test quality is
questionable. A blank box indicates EITHER a negative result OR no
test coverage (be careful - you can't assume too much from a blank box
without analyzing the BED file for read coverage).

The Unique Variants tab lists most of the variants that are unique to
only one of us. I'd be happy to add any new ones from YFull, if any of
you who have tested there would like to e-mail them to me. Note that
some Insertions/Deletions (these are kinda like hiccups in your DNA)
show "Count" as 0 because Big Tree calculates the position info for
INDELs a bit differently than the VCF file. These are retained for

The primary function of this spreadsheet is to easily add VCF data to
Variants for new Big-Y testers, then immediately determine which
existing SNPs from our branch they have, and which Unique Variants are
then no longer unique and need to be moved to Shared Variants.


Join to automatically receive all group messages.