Rounding to E Series in Excel

Discussion in 'Programmer's Corner' started by edwardholmes91, Apr 12, 2013.

  1. edwardholmes91

    Thread Starter Member

    Feb 25, 2013
    185
    19
    Hello,

    I am using Excel to calculate resistor values for a circuit and it suddenly occured to me... wouldn't it be great if you could somehow use the ROUND function in Excel to round to a range rather than the nearest number of DP.

    I'm not sure if this is the relevant section on the forum or if this is even possible... but it would be really useful if it is.

    I have been Googling it and using Excel help but not had much luck. I am going to keep looking and if I find a way of doing it or creating your own ranges I will let everyone know. In the mean time though, any help would be greatly appreciated!
     
    Last edited: Apr 12, 2013
  2. tshuck

    Well-Known Member

    Oct 18, 2012
    3,531
    675
    You could create a macro that does this pretty easily. You may be hard pressed to find it as a formula.
     
  3. ErnieM

    AAC Fanatic!

    Apr 24, 2011
    7,392
    1,606
    When I use excel to do my calculations I just get to the close values, then use a web browser to chick my favorite distributor to get what values actually exist in the physical size and tolerance I need.

    I'd never bother to try to define these values as they change for any given resistor line, and nothing says they stay in production. Plus when I check the value I can also check their stock so I know I can get it NOW.
     
  4. Markd77

    Senior Member

    Sep 7, 2009
    2,803
    594
  5. tshuck

    Well-Known Member

    Oct 18, 2012
    3,531
    675
  6. MrChips

    Moderator

    Oct 2, 2009
    12,447
    3,363
    Standard resistor values are equally spaced on a logarithmic scale.

    Code ( (Unknown Language)):
    1.  
    2. %MATLAB code
    3. E6 = [ 100 150 220 330 470 680];
    4. E12 = [ 100 120 150 180 220 270 330 390 470 560 680 820];
    5. E24 = [ 100 110 120 130 150 160 180 200 220 240 270 300 330 360 390 430 470 510 560 620 680 750 820 910];
    6.  
    Here is how I would tackle the problem.
    Suppose you want to find the nearest value from the E12 series (12 values).
    Take the linear range from 100 to 1000.
    Find the the straight line fit from log(100) to log(1000) spread over 25 values 1 to 25
    The coefficients are

    a0 = 4.5092
    a1 = 0.0959

    Convert this back to 25 linear values R = exp(a0 + a1*x)
    where x is a number from 1 to 25.

    For example
    x = 1 R = 100
    x = 2 R = 110
    x = 3 R = 121
    x = 4 R = 133
    x = 5 R = 147

    If you round and truncate you get

    x = 1 R = 100
    x = 2 R = 110
    x = 3 R = 120
    x = 4 R = 130
    x = 5 R = 150

    but you don't get the correct value of the series for all x.

    Select the R for even numbers of x
    For example

    x = 2 R = 110
    x = 4 R = 133

    If your R value falls between 110 and 133,
    select x = 3, R = 120

    That is, you use the R ranges from the next higher series,
    for example, E24, to determine the limits of each E12 value.

    Once you find the correct x value, use a lookup table for the E12 value because rounding and truncating does not always give the the exact value of the series.

    I could do this easily in MATLAB but it would take much more effort in Excel.
    It might be just as easy to have a lookup table of E24 series values and do a search to find the which band the resistor falls into.

    I hope this makes sense.
     
    Last edited: Apr 13, 2013
    screen1988 likes this.
  7. MrChips

    Moderator

    Oct 2, 2009
    12,447
    3,363
    I only just read Markd77's post #4.
    VLOOKUP( ) should do it just the way he describes it.
    Sorry about my long winded solution.
     
  8. WBahn

    Moderator

    Mar 31, 2012
    17,757
    4,800
    But keep in mind that "midpoint" is an ambiguous term. Does it mean the arithmetic midpoint (i.e., the average) or the geometric midpoint (i.e., the square root of the product)? In general, the standard values are a geometric progression, but which makes the most sense to use depends on your particular application. But it is a pretty easy matter to have a formula can computes either the arithmetic or the geometric midpoints based on the value in a single cell so that you can use whichever is most appropriate at the moment.
     
  9. MrChips

    Moderator

    Oct 2, 2009
    12,447
    3,363
    To find the midpoint and range, use the values from the next higher up series.
    For example, to find the midpoint and range of E12 series, use the E24 series.
     
  10. WBahn

    Moderator

    Mar 31, 2012
    17,757
    4,800
    At first blush that sounds reasonable, but there are a few problems with it. First, that will give you an approximation to the geometric midpoint, but it may not be the most appropriate midpoint to use for a particular application. Remember, the choices of the values in the series are based on allowing for errors that are proportional to value. In many measurement situations you have errors that are more absolute.

    Second, it fails completely if trying to find the midpoints of the values in the E24 series by using the E48 series. Of the 24 values in the E24 sequence, only three even appear in the E48 sequence, namely 10, 11, and 75. So let's say you wanted to find the midpoint of 120 and 130, do you use 121 or 127? Or if you wanted the midpoint of 130 and 150, do you use 133, 1140, or 147.

    Finally, even when there is only a single E48 value between two E24 values, it is not always a reasonable value to use as a midpoint. For instance, between 470 and 510 there is only 487, but a much better choice would be 490 since the geometric mean is 489.6. The reason that 487 is so low is because the surrounding values are 464 and 511, so the 464 biases the intermediate value to a lower value.
     
  11. MrChips

    Moderator

    Oct 2, 2009
    12,447
    3,363
    Yes, you are correct.
    Looking at 510 in E24, using the E48 as limits would suggest 511 and 536.
    So I have to fall back and use log(100) to log(1000) linear regression.
     
  12. andrewmm

    Active Member

    Feb 25, 2011
    30
    6
    This used to be a programming question at university,

    if you used a look up table, that got you 80 % of all the marks available. The equation got you the possibility of 100 %
     
  13. GopherT

    AAC Fanatic!

    Nov 23, 2012
    6,061
    3,823
    How often would this be used? It seem like more work than memorizing the E12 values per decade and using a calculator.
     
  14. WBahn

    Moderator

    Mar 31, 2012
    17,757
    4,800
    I suspect that few people would need to do it very often. If you only need it once in a while, then do it manually and move on. It's a useful exercise for a number of reasons, but one of them is almost certainly not to produce a tool that is going to be used in practice. Now, I suspect there are also people that need this on a regular basis or that when you do need to do it, you need to do it for multiple values. In those cases it may well make sense to automatic the computation in some way.
     
  15. GopherT

    AAC Fanatic!

    Nov 23, 2012
    6,061
    3,823
    I started with the same thought but, after thinking about it for a minute, i just couldn't find a situation where this was true. Therefore, i concluded the same as you...

    I am interested in learning what type of task/occupation/designer would find this useful.
     
Loading...