DNA survey - please respond

[sarah333]

Quakers on one side, Jewish on the other! No wonder I haven't found any real cousins here!

 

[Bazza43]

OK, here's an algorithm which not only makes logical sense but also produces much better results in most cases. What I've done in the final column is take the square root of the population of each county.

I'm attaching a revised spreadsheet with my results; you can either adapt your existing spreadsheet or copy your results into mine.

Thanks to everyone who is taking part!

OK Peter,

This is more of a hunch, based on previous work I've done, not a reasoned argument: how about using the logarithm of the county population rather than the square root? If you wanted a reasoned argument, how about this? The logarithm treats the relationship between two small counties with a 2:1 ratio of population as it does two large counties with the same ratio.

Sorry, I'm a mathematician.

 

[Bazza43]

OK, here's an algorithm which not only makes logical sense but also produces much better results in most cases. What I've done in the final column is take the square root of the population of each county.

I'm attaching a revised spreadsheet with my results; you can either adapt your existing spreadsheet or copy your results into mine.

Thanks to everyone who is taking part!

OK Peter, here is the result of my spreadsheet sorted, but not highlighted (until I can get my head around what it's supposed to highlight.

Also, since I've suggested it, is a spreadsheet with a revised algorithm using the log of the county population, and sorted by that criterion.

In both cases, I'm surprised at the prominence of southern English counties (and Lancashire) since I have no knowledge of any ancestry from them. See what you think.

Barrie

 

[jorghes]

OK Peter, here is the result of my spreadsheet sorted, but not highlighted (until I can get my head around what it's supposed to highlight.

The highlighting is to show the places where your 3x great grandparents are from. I gave a darker colour for 4 or more 3x GG, a shade lighter for 2-3, a shade lighter for 1, and another shade lighter for some fraction (i.e. when a 3GG had ancestry from several different counties)

 

[FredC]

Here goes.

 

[Bazza43]

The highlighting is to show the places where your 3x great grandparents are from. I gave a darker colour for 4 or more 3x GG, a shade lighter for 2-3, a shade lighter for 1, and another shade lighter for some fraction (i.e. when a 3GG had ancestry from several different counties)

OK. Here's my attempt with shading. Having heard from both Peter and jorghes, I thought I had four shades of yellow, but I don't so the least significant counties are pale bluish.

Barrie

 

[FredC]

Appears that my shading isn't to the latest rules. Appreciate some guidance.
Thanks

 

[peter]

Appears that my shading isn't to the latest rules. Appreciate some guidance.
Thanks

You're using an old version of the spreadsheet - there should be a message at the top with a link to the latest version. Please also show the number of ancestral lines from each county - it's based on the ancestry of your 3G grandparents - the colour scheme you choose isn't quite so important if this data is shown, but using different shades of the same colour is best. If you're using Excel there are plenty of colours to chose from.

Thanks!

 

[FredC]

Here's my latest.

 

[Bazza43]

You're using an old version of the spreadsheet - . . . . . Please also show the number of ancestral lines from each county - it's based on the ancestry of your 3G grandparents - . . . . Thanks!

If I used an old version of the spreadsheet, then I've added the desired column, the heading I put on it is 'Number of 3G grandparents with ancestors from county.' I'm surprised you didn't see it!

B.

 

[sarah333]

Well I used the new one after I couldn't get results from the old one, but I still didn't get the Wales row added. Also, as I said, since I know I have no Welsh descendants it doesn't matter, I guess.

 

[peter]

If I used an old version of the spreadsheet, then I've added the desired column, the heading I put on it is 'Number of 3G grandparents with ancestors from county.' I'm surprised you didn't see it!

You used the latest spreadsheet - it was FredC who didn't.

 

[canadianbeth]

Is there a blank version of the spreadsheet that I can use? Or do I just click on someone else's and delete those figures? What is coloured and what is not?

Also, I am not quite sure what to do. Am I looking at my Ancestry DNA results, searching by location and putting in the # of names in column C for each county? Does the program automatically adjust for columns D-F? I certainly hope so, although I can figure out myself who my 3G grandparents are by looking at my own tree. It seems to me that out of the 470-475 pages of results I have that most of those names connect to each other.

 

[peter]

Is there a blank version of the spreadsheet that I can use? Or do I just click on someone else's and delete those figures? What is coloured and what is not?

Also, I am not quite sure what to do. Am I looking at my Ancestry DNA results, searching by location and putting in the # of names in column C for each county? Does the program automatically adjust for columns D-F? I certainly hope so, although I can figure out myself who my 3G grandparents are by looking at my own tree. It seems to me that out of the 470-475 pages of results I have that most of those names connect to each other.

Most people already had a spreadsheet of their own from earlier in the discussion, but to make it easier for newcomers I've attached to this post a tidied up version of mine with the highlighting removed. Just change the figures in Column C, add figures to column F, and highlight rows as described in recent posts.

 

[canadianbeth]

Ok, I have it done and hope it is correct, but I cannot figure out how to upload it the way the others have. I hope it is saved properly since my Excel is a newer version. I saved it to a pdf file but now what? I also have not done any colouring. Two of my paternal 3x grandparents are from Surrey and three maternal ones from Middlesex. The others are unknown.

Also, because my great-grandparents were first cousins, my 3x grandparents would be doubled - right? So four instead of two?

 

[jorghes]

Also, because my great-grandparents were first cousins, my 3x grandparents would be doubled - right? So four instead of two?

I also have pedigree collapse in my tree (doesn't come up on my set of 3GGs though) - I probably only would have counted them once, but then I suppose there's a chance they gave you twice as much DNA...

 

[Bob Spiers]

Ok, I have it done and hope it is correct, but I cannot figure out how to upload it the way the others have. I hope it is saved properly since my Excel is a newer version. I saved it to a pdf file but now what?

You should save your Excel Spreadsheet in the Excel format of the version you have - probably by default*.xlsx - (or *.xls for earlier versions); not as a pdf document which is effectively only a copy of a spreadsheet. You then attach the saved EXCEL Spreadsheet - which might read (for example) canadianbeth.xlsx - when you post.

 

[peter]

You should save your Excel Spreadsheet in the Excel format of the version you have

It would be better to save the spreadsheet in xls format as some people might not be able to open an xlsx file.

Please use the fill tool to highlight the counties where you have ancestors, graduating the colours as others have done - this enables anyone to tell at a glance how successfully the algorithm has sorted the counties. For now double the numbers where you have pedigree collapse (but this might need to be adjusted later).

Also bear in mind that it's not where your 3G grandparents lived or were born that matters, but where their ancestors came from (if known).

Thanks for taking part in this trial.

 

[canadianbeth]

Ok, I am hoping this works. According to the Ancestry DNA website, I have ancestors in every county listed, although many of the ones listed as fourth cousins or better, appear in more than one. I had never even heard of some of these counties and I wonder how my ancestors wound up in them since the ones I know about all seem to have been in Surrey, Middlesex or Kent. And Wales.

I tried saving it with a .xls extension. But I cannot figure out how to upload it like everyone else's. So I just did a copy/paste.

County Population Matches (pages) Adjusted Result Number of 3G grandparents with ancestors from county

Anglesey 51416 1 0.5 453.50
Bedfordshire 149473 6 5.5 70.29
Berkshire 218363 9 8.5 54.98
Breconshire 57746 2 1.5 160.20
Buckinghamshire 176323 8 7.5 55.99
Caernarvonshire 119349 1 0.5 690.94
Cambridgeshire 185594 6 5.5 78.33
Cardiganshire 70270 2 1.5 176.72
Carmarthenshire 124864 3 2.5 141.34
Cheshire 644037 10 9.5 84.48
Cornwall 330686 11 10.5 54.77
Cumberland 250647 5 4.5 111.25
Denbighshire 111740 2 1.5 222.85
Derbyshire 401914 8 7.5 84.53
Devon 603595 18 17.5 44.40
Dorset 191028 8 7.5 58.28
Durham 867258 6 5.5 169.32
Essex 576434 13 12.5 60.74
Flintshire 8058 7 2 1.5 189.25
Glamorgan 511433 6 5.5 130.03
Gloucestershire 572433 14 13.5 56.04
Hampshire 593470 12 11.5 66.99
Herefordshire 121062 5 4.5 77.32
Hertfordshire 203069 6 5.5 81.93
Huntingdonshire 5949 1 3 2.5 97.56
Kent 977706 18 17.5 56.50
Lancashire 3454441 19 18.5 100.47
Leicestershire 321258 6 5.5 103.05
Lincolnshire 46991 9 8 7.5 91.40
Merionethshire 52038 1 0.5 456.24
Middlesex 2920485 18 17.5 97.65 3 3
Monmouthshire 211267 4 3.5 131.33
Montgomeryshire 65718 2 1.5 170.90
Norfolk 444749 10 9.5 70.20
Northamptonshire 272555 6 5.5 94.92
Northumberland 434086 7 6.5 101.36
Nottinghamshire 391815 7 6.5 96.30
Oxfordshire 179559 7 6.5 65.19
Pembrokeshire 91824 3 2.5 121.21
Radnorshire 23528 1 0.5 306.78
Rutland 21434 1 0.5 292.81
Shropshire 248014 6 5.5 90.55
Somerset 469109 16 15.5 44.19
Staffordshire 981013 11 10.5 94.33
Suffolk 356893 10 9.5 62.88
Surrey 1436899 14 13.5 88.79 4 4
Sussex 49050 5 12 11.5 60.90
Warwickshire 737339 12 11.5 74.67
Westmorland 64191 2 1.5 168.91
Wiltshire 258965 13 12.5 40.71
Worcestershire 380283 7 6.5 94.87
Yorkshire 2886564 23 22.5 75.51

 

[peter]

But I cannot figure out how to upload it like everyone else's. So I just did a copy/paste.

Sorry, but I can only work with spreadsheets. You should see 'Upload a File' next to 'Post Reply' (as mentioned on page 11 of this discussion).

Please note that the spreadsheet needs to be sorted according to column E (lowest to highest); if you're using my spreadsheet as a pro forma it'll probably come up as the default when you click 'Custom Sort'. Remember to include the number of 3G grandparents in column F and highlight the relevant boxes in column A..