An E96 formula: How Can You Resist It?

Spreadsheets are brilliant for filter design, and I write much less design software these days. Don’t get me wrong, I love the BASIC interpreter that I use, BBC BASIC, available in a native Windows version and a multiplatform version (Windows, x86 Linux, MacOS, Android, Raspberry Pi and even iOS) using SDL2.0. It’s a compact but complete Windows programming environment, sharing a direct lineage with the BASIC dialect found in the original BBC Micro, which if you are young, or not British, you may never even have heard of. It’s so compatible that I can run filter synthesis, analysis and optimization programs I wrote up to 35 years ago completely unmodified – just orders of magnitude faster than on the “Beeb”’s 2 MHz 6502. If I post BASIC programs relevant to this column, that’s what I’ll use. But this piece isn’t about BASIC; it’s about spreadsheets.

These days, I often code design equations for filter circuits directly into sequences of Excel cells. And, for the Microscoffs out there: yes, you can use other spreadsheets, including free ones. In fact, this work began in the far-off Quattro Pro days. Early versions of Calc (from the OpenOffice suite) seem to have lost the ‘general’ number format, which makes formatting some sheets much harder work than it should be, IMHO. The Google Docs spreadsheet seems to work, though I’ve not tried it out on complex stuff. Both also have a ‘solver’ capability, which is great for circuit optimization, and I’ll cover that in more detail in some other columns.

Anyway, how do you get from the many decimal places shown for each calculated component value, to a design that uses components that you can actually get ? You have to turn them into ‘preferred values’. I do this manually for capacitors – it’s a habit from my formative years where you purchased accuracy in resistors but selected it into capacitors. Accurate capacitors aren’t readily available in a structured, closely spaced sequence. In fact, these days, accurate capacitors aren’t available, period. Active filter design is being deprived of its life-blood – wail, gnash, sob. But you have to work with what you can get, and at least you can get closely spaced tight tolerance resistors in the E96 range to go with the few stable capacitors still on the market.

Even with the bounty of 96 values in each resistance decade, the value spacing is still wide compared to the tolerance (0.1% resistors are readily available at low cost). Designing an entire filter and then replacing all the theoretical values with the closest E96 value in one go is sub-optimal. You can get sizeable implementation errors, especially in sensitive ‘sharp’ filters with values in close ratio.

A much better approach (OK, my column, my opinion) is to form the design process into a series of steps, at each of which a new component value is calculated. This is then quantized onto the available component grid and locked before moving onto the next component calculation, which takes the prior, already-chosen values, into account. The spreadsheet paradigm is ideal for automating this approach. But still, how to determine which E96 component value to use?

We could loop over the choices available from a big list, until we find the closest fit. But spreadsheets don’t do looping very well, unless you’re prepared to write some code in VBA, and that rather defeats the object. If I’m going to write a BASIC program, I’ll write it in BBC BASIC! So, what I needed, way back in the 20th Century, was a tractable, non-iterative cell formula for turning a floating-point resistor value into the nearest E96 component value, so that each component can be fixed and then used in the subsequent calculations.

The E96 value multipliers are distributed logarithmically over a decade. Each successive value is higher than the previous by the one-ninety-sixth root of 10. Round off the results of the resulting geometric progression:

1, 1.024275221, 1.049139729, 1.074607828, 1.100694171…

to three significant digits, and Hey Presto! The key multipliers in the E96 series fall straight out. The people who devised this series must have sensed that we’d want to automate our design process one day. All that is needed to convert an actual resistance value is to extract the power of ten for the actual decade the resistor is in, find the E96 multiplier, and then put the whole value back together.

The scheme also appears to work well for E48, but I’m not sure the rounding always gives the accepted value for the E192 scale (I’ve never been forced to use ‘em so haven’t tested this to any depth). And the more workaday scales, E24 and below, don’t fully overlap with the E96 range.

So, here’s a cell formula that turns a positive real resistance into the nearest E96 component; copy it straight out and paste it in wherever you need it. The value to be converted is in cell E11, in this example:

=( 10^TRUNC(LOG(E11))*0.01*TRUNC((0.5 + 100*10^(1/96)^TRUNC(0.5 + 96*(LOG(E11) – TRUNC(LOG(E11)))))) )

This has served me well in all sorts of filter design spreadsheets over many years; enjoy! Just occasionally, the formula selects the value ‘on the wrong side’ of the closest one. Changing the offset in the innermost TRUNC() function biases the selection to one side (sometimes useful if you always want next-higher or next-lower for a particular scheme). So I sometimes use a more laborious conditional version which tests which is closer:

=IF( ( 10^TRUNC(LOG(E11)) * 0.01 * TRUNC((0.5 + 100*10^(1/96)^TRUNC(0.75 + 96*(LOG(E11) – TRUNC(LOG(E11))))))) * ( 10^TRUNC(LOG(E11)) * 0.01 * TRUNC((0.5 + 100*10^(1/96)^TRUNC(0.25 + 96*(LOG(E11) – TRUNC(LOG(E11)))))) ) > E11*E11 , ( 10^TRUNC(LOG(E11)) * 0.01 * TRUNC((0.5 + 100*10^(1/96)^TRUNC(0.25 + 96*(LOG(E11) – TRUNC(LOG(E11)))))) ) , ( 10^TRUNC(LOG(E11)) * 0.01 * TRUNC((0.5 + 100*10^(1/96)^TRUNC(0.75 + 96*(LOG(E11) – TRUNC(LOG(E11))))))) )

You can pre-calculate some of the cells if you’re a bit frightened of long formulae; adapt them to your own needs. I just hope I’m not over-celling myself here! – Kendall

3 comments on “An E96 formula: How Can You Resist It?

  1. Tucson_Mike
    March 17, 2019

    I am going to try and paste in a word document with a snip showing some recent prices – snip might not come across, No it didn't, darn – oh well I found some < $0.01 in 100s even in 1% C0G, 0201, E24 steps, 25V MLCC caps from Murata. Full part number for the 100pF 1% tolerance was GRM0335C1E1010FA01D. 

    Hey Kendall,


    Nice to have that E96 equations, but I think some of your comments apply more to some years back. Oddly, on another discussion doing MFB filters I went back and checked again for e24 values in finer tolerance than 5%. Wow, they have really come down. I found 2% and 1% both under $0.01 in 100s at Mouser (Murata, GRM series)



    I don't see why we should not use 1% C0G caps now – but since the E24 are in such large steps, what I do it kind of iterate to some standard C values first (in the LP MFB), then solve exactly for the 3 R's then go on from there for E96 selections for best fit. 

  2. kendallcp
    March 18, 2019

    Thanks for that, Mike!  I guess my incessant moaning (and that of others with larger production runs ) about the lapse in cap supply must have paid off some time in the last decade!  The radio guys certainly need plenty of steps in the sup-100pF range, for RF matching networks and filters.

    I certainly agree that exact design pays off for single amplifier biquads.  I've found that cell-based suvvessive design works better for tunable state-variables.  And as for FDNR ladder derived designs, they are generally so insensitive that you can convert everything to E96 in one go and still get something that's almost indistinguishable from the prototype.

    Good to hear from you, keep in touch!

  3. michaelmaloney
    April 9, 2019

    It's frustrating to have to try and work out things across different platforms, but I guess it's good to be able to learn all sorts of different systems to make your ability to work more flexible across the board. Glad to have this all laid out on the table though. Perhaps it might help the next time I'm trying to crunch some fingers so thank you very much for sharing all of this information!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.