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."

Figure 1: BER Test Data Table for a Single Unit. There are 18 measurements for each unit.

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

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

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:

• We need to come up with an iterative solution for Equation 1 because it does not have a closed-form solution. In Excel, I used the Goal Seek function with a macro.
• Evaluating summations are a bit messy in Excel. In this case, I decided to use an array formula, which allowed the formula to be evaluated in a single cell.
• You must configure the Excel spreadsheet to use iteration and reduce that maximum change allowed. You adjust these values in Excel by going to File/Options/Formula and checking the following boxes.
  

  Figure 2: Excel Iteration Settings.

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.

Figure 3: Screenshot of My BER Workbook.

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