Designing passive LC-filters typically involves looking up prototype filter component values in a table in reference books like “Handbook of Filter Synthesis” by Zverev or “Design of Microwave Filters, Impedance-Matching Networks, and Coupling Structures” by Matthaei et. al., then transforming the values to produce a filter with the desired impedance and cut-off frequency. This can be a bit tedious and error prone, so when I found out about the classic Matthaei book, which is downloadable via the link above, and that it contains the math required to calculate the prototype filter component value tables, I set out to create a spreadsheet to help with the filter design.

In addition to calculating the (previously) magical prototype tables for Butterworth and Chebyshev (with user-specified pass-band ripple) filters, the spreadsheet also performs the frequency and impedance transformation for filters of orders from 1 to 10.

To make the design process even quicker and better, I added a feature to create LTSpice schematics of the selected filter so that the filter properties can be simulated (and perhaps manually adapted to standard component values and to include parasitics) using LTSpice. I used the SI prefix formatting function I wrote about in the previous blog post to write out the component values in a pretty manner.

The usage of the spreadsheet should be fairly self-explanatory, but there are also usage instructions on the first tab. Basically, the user should fill out the values in yellow cells and leave the rest alone. I did not lock any cells, since I often get annoyed by spreadsheets with locked cells and I encourage others to modify and improve it.

Here is a link to the Excel 2002 file:

FilterSynthesis_v1.0.xls

Make sure that macros are enabled if you want to use the LTSpice export features.

Here are some screenshots

Excel does not have any built-in way of formatting numbers such that the numbers are displayed in a neatly readable way using SI prefixes, like m for milli (10^-3), k for kilo (10^3), µ for micro (10^-6) etc. Here I provide a VBA function I wrote to fill this need. In addition, I added support for using the prefix notation of Spice circuit simulators where both m and M are interpreted as milli and Meg has to be used to signify mega. Also, u has to be used instead of µ to indicate micro in Spice.

As icing on the cake, I also wrote a function to convert the resulting strings back to numbers.

The functions are called FormatPrefix and ValPrefix. Source code with detailed descriptions of the input arguments are shown further down in this post.

Below is a link to an Excel document with the functions built into a VBA module and a worksheet with an example table of values fed to the function to display its output for a large variation of input arguments:

ExcelPrefixFormattingFunction.xls

Here is a screenshot demonstrating the formatting:

The source code is shown below:

Public Function FormatPrefix(value As Double, _
                             Optional precision As Integer = 3, _
                             Optional blankZero As Boolean = False, _
                             Optional space As Boolean = False, _
                             Optional spice As Boolean = False) As String
    ' Convert a number to a string, usually with an SI prefix at the end.
    '
    ' Arguments:
    ' value is the value to be converted.
    ' Optional precision argument determines number of significant digits to be printed (default 3).
    ' Optional blankZero argument determines if zero should return the empty string (default false).
    ' Optional space argument determines if a space should be inserted after the number but before
    '  a possible prefix (default false).
    ' Optional spice argument determines if Spice prefixes should be used instead of metric ones
    '  (default false). The only differences are Meg vs M and u vs µ.
    ' Values above or equal to 1e15 or below 1e-18 are printed using scientific notation without
    ' prefix.
    ' The decimal indicator is always a point, even if the locale says it should be a comma.
    '
    ' Written by Per Magnusson, Axotron, 2016-02-26
    ' This code is public domain and comes without any warranty. Enjoy!
    
    ' Handle the value 0
    If value = 0 Then
        If blankZero Then
            FormatPrefix = ""
        Else
            If space Then
                FormatPrefix = "0 "
            Else
                FormatPrefix = "0"
            End If
        End If
        Exit Function
    End If
    
    ' Handle negative numbers
    sign = 1
    If value < 0 Then         sign = -1 ' store the sign         value = -value ' make value positive until later     End If          ' Calculate exponent and mantissa     exponent = Int(Log(value) / Log(10#))     mantissa = Round(value / (10 ^ exponent), precision - 1)     If mantissa >= 10 Then
        ' Handle the case where the mantissa is rounded up to 10
        exponent = exponent + 1
        mantissa = mantissa / 10
    End If
    exponent3 = 3 * Int(exponent / 3) 'exponent rounded down to nearest multiple of 3
    
    ' Find the right prefix
    If exponent >= 15 Then
        ' Too big to represent with the supported prefixes, use scientific notation
        prefix = "sci"
    ElseIf exponent >= 12 Then
        prefix = "T"
    ElseIf exponent >= 9 Then
        prefix = "G"
    ElseIf exponent >= 6 Then
        If spice Then
            prefix = "Meg"
        Else
            prefix = "M"
        End If
    ElseIf exponent >= 3 Then
        prefix = "k"
    ElseIf exponent >= 0 Then
        prefix = ""
    ElseIf exponent >= -3 Then
        prefix = "m"
    ElseIf exponent >= -6 Then
        If spice Then
            prefix = "u"
        Else
            prefix = "µ"
        End If
    ElseIf exponent >= -9 Then
        prefix = "n"
    ElseIf exponent >= -12 Then
        prefix = "p"
    ElseIf exponent >= -15 Then
        prefix = "f"
    ElseIf exponent >= -18 Then
        prefix = "a"
    Else
        ' Too small to represent with the supported prefixes, use scientific notation
        prefix = "sci"
    End If
        
    If prefix = "sci" Then
        ' Use scientific notation
        formatStr = "0"
        If precision > 1 Then
            formatStr = formatStr & "."
            For ii = 1 To precision - 1
                formatStr = formatStr & "0"
            Next
        End If
        formatStr = formatStr & "e-0"
        prefix = ""
        value = sign * mantissa * 10 ^ exponent
    Else
        decimals = precision - (exponent - exponent3) - 1
        If decimals > 0 Then
            formatStr = "0."
            For ii = 1 To decimals
                formatStr = formatStr & "0"
            Next
        Else
            formatStr = "0"
        End If
        value = sign * mantissa * 10 ^ (exponent - exponent3)
    End If
    optSpace = ""
    If space Then
        optSpace = " "
    End If
    FormatPrefix = Replace(Format(value, formatStr) & optSpace & prefix, ",", ".")
    'FormatPrefix = formatStr & optSpace & prefix
End Function



Public Function ValPrefix(str As String) As Double
    ' Convert a number formatted as a string using FormatPrefix() back to a number.
    '
    ' Arguments:
    ' str is the string to be converted
    ' Error checking is very limited.
    '
    ' Written by Per Magnusson, Axotron, 2016-02-26
    ' This code is public domain and comes without any warranty. Enjoy!
    
    exponent = 0
    prefix = ""
    strlen = Len(str)
    
    If StrComp(Right(str, 1), "A") >= 0 Then
        ' There is a letter at the end of the value
        ii = 1
        While StrComp(Mid(str, strlen - ii, 1), "A") >= 0
            ii = ii + 1
        Wend
        prefix = Mid(str, strlen - ii + 1)
        Select Case prefix
            Case "T"
                exponent = 12
            Case "G"
                exponent = 9
            Case "M"
                exponent = 6
            Case "Meg"
                exponent = 6
            Case "k"
                exponent = 3
            Case "m"
                exponent = -3
            Case "u"
                exponent = -6
            Case "µ"
                exponent = -6
            Case "n"
                exponent = -9
            Case "p"
                exponent = -12
            Case "f"
                exponent = -15
            Case "a"
                exponent = -18
            Case Else
                ' Error, handle it somehow?
                ValPrefix = "Prefix error"
                Exit Function
        End Select
    End If
    ValPrefix = Val(str) * 10 ^ exponent
    
End Function