# Test Time vs BER and Confidence Level Using Excel

Quote of the Day

When a thing is done, it's done. Don't look back. Look forward to your next objective.

— George C. Marshall. I have met many people who spend their lives constantly looking back and expressing regret for opportunities lost. Learn what you can from your experience and move forward. As Marlon Brando used to say,  "Regret is useless in life. It's in the past. All we have is now."

I am currently working as a project manager on a serial data channel qualification task. During this work, I need to estimate the time required to perform dozens of Bit Error Rate (BER) measurements under different scenarios (see Figure 1). In these measurements, we are working to ensure that our BER is always 1E-10 or less. I have performed this calculation many times and have derived all the associated formulas here. BER is a statistical parameter and its measurement is a function of the confidence level you want in your answer.  In this case, we want the highest confidence level possible consistent with a two-week test effort.

The calculation requires solving Equation 1 for n, the number of bits required, to confirm a maximum BER p at a specific confidence level CL and error number N.

 Eq. 1 $-n\cdot p=\ln \left( 1-CL \right)-\ln \left( \sum\limits_{k=0}^{N}{\frac{{{\left( np \right)}^{k}}}{k!}} \right)$

To convert the number of bits transferred to a test time τ, we need to evaluate Equation 2 with the bit transfer rate, r.

 Eq. 2 $\displaystyle \tau =\frac{n}{r}$

I normally perform this calculation using Mathcad, but in this case, other engineers wanted it written in Excel because they are familiar with that tool. The calculation is a bit messier in Excel because:

Figure 3 is a screenshot of the workbook's test time calculation.  The worksheet is easy to use – just input your p, N, CL, and rate values (highlighted in yellow), then press iterate. This calculation tells me how long a single BER measurement will take. Remember that I need to perform dozens of these measurements, so even small reductions in the single-test time make a big difference to the overall test time.

For those interested, my Excel workbook is here. It includes a very simple macro (shown below) that calls the Goal Seek function for each confidence level and allowed error number. The macro also changes the iteration setting.

Sub Iterate()

' Change the interation number and tolerance
IterTF = Application.Iteration = True
IterStep = Application.MaxIterations

' enables iterations .MaxIterations is the number you would wanna change
With Application
.Iteration = True
.MaxIterations = 1000
.MaxChange = 0.00001
End With

c = Range("_CLlist")
r = Range("_Elist")
r1 = Range("_rate")
For i = 1 To UBound(c)
Range("_CL").Value2 = c(i, 1)
For j = 1 To UBound(r, 2)
Range("_E") = r(1, j)
Range("_calc").GoalSeek Goal:=Range("_CL").Value2, ChangingCell:=Range("_n")
Range("_ttime").Cells(i, j) = Range("_n").Value2 / (r1 * 60)
Next j
Next i

'restores to the original setting
With Application
.MaxIterations = IterStep
.Iteration = IterTF
.MaxChange = 0.001
End With
End Sub
This entry was posted in Electronics, Excel. Bookmark the permalink.

### One Response to Test Time vs BER and Confidence Level Using Excel

1. Ron says:

Hi Mark,
I downloaded the spreadsheet and attempted to use this calculator for a 10GBase-T calculation with 10^-12 BER. For Rate, I entered 10000 (10Gbps) and for p I entered 1.0E-11. The time outputs were larger than expected. Believing I had run outside a coded range, I ran the numbers shown above in the image and the output was 10^6 larger than expected. I'm wondering if you can take a look and let me know where I've run afoul?