Rounding to E Series in Excel

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

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.

Sep 7, 2009
2,803
594

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.