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.

=IF(ISNUMBER(SEARCH(“*High*”,D1)),2,IF(ISNUMBER(SEARCH(“*Gain*”,D1)),1,IF(ISNUMBER(SEARCH(“*Homozygous*”,D1)),-2,IF(ISNUMBER(SEARCH(“*Loss*”,D1)),-1,0))))

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