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!

 

You could create a macro that does this pretty easily. You may be hard pressed to find it as a formula.

 

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.

 

Should be able to do it with one of these:
http://office.microsoft.com/en-gb/excel-help/vlookup-HP005209335.aspx
If you put the list of available resistor values in the second column and midpoints between them in the first column.

 

Should be able to do it with one of these:
http://office.microsoft.com/en-gb/excel-help/vlookup-HP005209335.aspx
If you put the list of available resistor values in the second column and midpoints between them in the first column.

Doh! I even just used that a few months ago!

 

Standard resistor values are equally spaced on a logarithmic scale.

%MATLAB code
E6 = [ 100 150 220 330 470 680];
E12 = [ 100 120 150 180 220 270 330 390 470 560 680 820];
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];

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.

 

I only just read Markd77's post #4.
VLOOKUP( ) should do it just the way he describes it.
Sorry about my long winded solution.

 

Should be able to do it with one of these:
http://office.microsoft.com/en-gb/excel-help/vlookup-HP005209335.aspx
If you put the list of available resistor values in the second column and midpoints between them in the first column.

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.

 

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.

 

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.

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.

 

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.

 

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 %

 

How often would this be used? It seem like more work than memorizing the E12 values per decade and using a calculator.

 

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.

 

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.

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 suspect that few people would need to do it very often.

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