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 to it. You can copy & “paste down” to compute this for an entire column.

Download our white paper, “Analyzing NGS Data For Copy Number Events,” to learn:

• How to use the BAM algorithm to obtain both copy number and allelic events from NGS data (unique and exclusive to Nexus Copy Number)
• Why this Read-Depth method is the most reliable and efficient means to extracting copy number information out of NGS data
• How the algorithm compares to other CNV estimating algorithms such as CoNIFER and XHMM

Download White Paper →