Share this post

Maslow’s hammer says “if all you have is a hammer, everything looks like a nail”. Ask my colleagues and they will tell you my two favorite hammers are Nexus Copy Number and Microsoft Excel. I was recently running a query on a dataset for a specific biomarker (in this case erbb2 amplifications), and then wanted to a database lookup and some calculations in Excel. I had a small problem — Nexus Copy Number gives me wonderfully descriptive results like “homozygous copy loss”, and “High Copy Gain”. While there is no mistaking this, what I needed for my particular analysis was a number — like “-2” or “2” !

Of course you could script something pretty simple in perl (and more quickly if you know the regex syntax by heart and don’t need to look at the help pages), I discovered a way to do this simply in Excel.

The formula is simple. It creates a copy number state of -2, -1, 0, 1 or 2 (corresponding to homozygous loss, single copy loss, diploid, single copy gain, and high copy gain respectively). In the below example, cell “D1” contains the text describing the call.


That’s all there is too it. You can copy & “paste down” to compute this for an entire column.