DNA survey - please respond

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

 

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.

 

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

 

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)

 

Here goes.

 

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

 

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

 

Here's my latest.

 

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.

 

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.

 

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.

 

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?

 

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...

 

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.

 

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