No announcement yet.

Charting Segments in Excel

  • Filter
  • Time
  • Show
Clear All
new posts

  • Charting Segments in Excel

    How can I automatically create a horizontal bar of a segment per row in Excel?


  • #2
    I've done it, but I bet someone else knows a more memory-efficient way to do it.

    What I do is create a base data table showing the start, stop points of all the segments I'm viewing.

    I have two different versions of this, one that looks at all 22 chromosomes and a different one that focuses only on one chromosome at a time. There's a trade off. The comprehensive 22-chromosome version can only handle about 4 series of data (i.e., 4 different DNA donors), but the one that focuses on only one chromosome can handle as many donors as you care to look at. It's just a matter of memory management.

    Anyhow, on a separate sheet, I create a display area.

    I first run a set of bp reference figures along the top, representing the end point of each increment I'm looking at. I can adjust these to increase or decrease the resolution at will.

    Then, in the rows below, I run a series of queries to find out whether the corresponding row from the data sheet falls between the beginning and ending points of the bp reference figures from the top of the sheet. If it does, it returns some number, between 1 to 5, depending on the length of the segment in cM. If not, it returns a blank cell.

    Then I use Excel's conditional formatting feature to apply a color fill accordingly. I also adjust the font's color to match, so it all blends into one uniform color bar, just like the chromosome browser at Gedmatch or FTDNA.

    I like it. It allows me to customize and expand on the # of donors and display resolution that those other formats don't. Plus, I can alter the display by sorting the donors on the data entry sheet. Usually I find that the easiest way to understand sequence is by sorting according to the median point of each segment. Sometimes, though, I add an additional sort category, by whether I attribute the segments to my paternal or maternal lines, or whether two donors are particularly closely related.

    On the other hand, it can be crazily memory intensive. I'm sure there more efficient ways to do this. I just don't know them.


    • #3
      Thx, I will give that a try.