Forums :: Resources :: Features :: Photo Gallery :: Vintage Radio Shows :: Archives
Support This Site: Contributors :: Advertise


It is currently Feb Sun 23, 2020 6:01 am


All times are UTC [ DST ]





Post New Topic Post Reply  [ 13 posts ] 
Author Message
 Post subject: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Mar Fri 20, 2015 9:06 pm 
Member

Joined: Dec Wed 24, 2014 7:34 pm
Posts: 2073
Location: Weimar, Texas
I put together a simple spreadsheet to document resistors in a project and determine if they are out of tolerance. The last column is for the amount of difference between the tested value and the color-band value of the resistor. To the left of that is a column for the amount of tolerance ± allowed.

Eventually I may make the value turn red if it's out of tolerance but for now you have to compare the numbers yourself.

For instance- if Allowable Difference ± is 2.00 and Actual Difference is 2.8 or - 2.8 then the resistor is out of tolerance.

Bold-face blue values are inputs. Black are results. Bold-face black are the two numbers to compare.

https://dl.dropboxusercontent.com/u/494 ... stors.xlsx

_________________
It's hard to solve an equation if every term is an unknown.

73
NE5U

Mike


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Mar Fri 20, 2015 10:00 pm 
Member
User avatar

Joined: Feb Sun 02, 2014 1:58 pm
Posts: 1095
Location: Tennessee 38058
Looks like a helpful item! Thanks for sharing.

_________________
Mike
KN4GGM

“Technology is a useful servant but a dangerous master.”
C. Lange


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Mar Fri 20, 2015 10:05 pm 
Member

Joined: Dec Wed 24, 2014 7:34 pm
Posts: 2073
Location: Weimar, Texas
You're welcome. I think it's handy. When it comes time to order parts I just highlight any bad one in red. And it lets me keep track of what resistors each device has in it. I just started using it. The first tab is kind of the master. I just copy it to a new tab when I add something

_________________
It's hard to solve an equation if every term is an unknown.

73
NE5U

Mike


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Mar Sun 22, 2015 5:25 am 
Member
User avatar

Joined: Aug Thu 18, 2011 4:48 am
Posts: 4545
Location: Stamford, NY
I love this. Thank you! Yeah, highlighting red when out of tolerance would be awesome. I'd do it myself but I'm not totally sure how to. Cool. Saved!

_________________
Vincent


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Mar Sun 22, 2015 2:14 pm 
Member

Joined: Dec Wed 24, 2014 7:34 pm
Posts: 2073
Location: Weimar, Texas
radiopicker wrote:
I love this. Thank you! Yeah, highlighting red when out of tolerance would be awesome. I'd do it myself but I'm not totally sure how to. Cool. Saved!



I replaced the old version with one that automatically bold faces the out of tolerance value and changes the color. I had to knock of some cobwebs on my excel skills but it's pretty easy to do with conditional formatting.

The formula is ABS(actual difference) > max allowed difference format bold faced and red. Since the difference can go positive or negative I just took the absolute value of the difference and compared it to the max allowed.

This thing should also work with caps or any other "thing" that you're comparing the tolerance of to.

I think the link stays the same but just in case:

https://dl.dropboxusercontent.com/u/494 ... stors.xlsx

_________________
It's hard to solve an equation if every term is an unknown.

73
NE5U

Mike


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Apr Sun 05, 2015 4:09 am 
Member
User avatar

Joined: Jan Thu 01, 1970 1:00 am
Posts: 2143
Location: NorthWest TN.
Wow, thanks Mike! This is really nice work.

_________________
I never loose. I either win or I learn.


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Apr Mon 06, 2015 9:08 pm 
Member

Joined: Mar Tue 24, 2009 7:42 pm
Posts: 42
Location: Tacoma, WA
Great job!!! Thanks


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Apr Mon 06, 2015 10:01 pm 
Member
User avatar

Joined: Mar Tue 04, 2014 3:29 am
Posts: 2086
Location: Long Beach, CA 90808
Very handy!

Will get this onto my Shop Mac ASAP -- Thanks!

_________________
Aloha, Ken

No Console Left Behind!
Come join us @ http://vintagehifi.net/


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Apr Mon 06, 2015 10:51 pm 
Member

Joined: Dec Wed 24, 2014 7:34 pm
Posts: 2073
Location: Weimar, Texas
I'm glad to see people are interested in it. If there's something else I can build let me know. I'm not going to guarantee I can make it but if it has a formula in it I can generally deal with it in Excel

_________________
It's hard to solve an equation if every term is an unknown.

73
NE5U

Mike


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Apr Tue 14, 2015 12:38 am 
Member

Joined: Dec Wed 24, 2014 7:34 pm
Posts: 2073
Location: Weimar, Texas
I updated the sheet again. I added a % of tolerance column and then when the percentage is above 1/2 of the tolerance column it turns the value orange (yellow was too hard to see) and if it's above the tolerance value it turns it red just like in the value column.

I did this because sometimes a resistor might be within a few tenths of tripping the out of tolerance red color and it would be easy to miss. Personally, on the older gear that I work on, if a 20% resistor is off by more than 1/2 the tolerance (10% or greater) then I change it. That's how I came up comparing it to tolerance x .5. Even if you allow a greater tolerance difference being able to see a yellow value if it's greater than 1/2 of the tolerance and red if it's out of tolerance is pretty handy. Since tolerance is a percentage having the percent difference is also handy.

The link above should work. Here it is just in case: https://dl.dropboxusercontent.com/u/494 ... stors.xlsx

_________________
It's hard to solve an equation if every term is an unknown.

73
NE5U

Mike


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Sep Fri 18, 2015 1:44 am 
Member

Joined: Mar Wed 16, 2011 10:44 pm
Posts: 1122
Location: Peekskill, NY
" Personally, on the older gear that I work on, if a 20% resistor is off by more than 1/2 the tolerance (10% or greater) then I change it."

Then count on changing ALL the 20 percent tolerance units!

Resistors are not made with the tolerance color codes on them. They're manufactured,
measured, sorted according to tolerance, and then the final tolerance is marked on them.

So the values start out as a normal (gaussian) distribution. The 20 percent
values will be the high and low band wings, with a hole in the center.

The ten percent band will be bi-modal, with a smaller hole in the center of that.

The five percent ones are simply picked from the center of the distribution.

Try it: find a 20 percent tolerance resistor that's within five percent of the nominal value.
Hint - you won'!


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Sep Fri 18, 2015 2:04 am 
Member

Joined: Dec Wed 24, 2014 7:34 pm
Posts: 2073
Location: Weimar, Texas
jim rozen wrote:
" Personally, on the older gear that I work on, if a 20% resistor is off by more than 1/2 the tolerance (10% or greater) then I change it."

Then count on changing ALL the 20 percent tolerance units!

Resistors are not made with the tolerance color codes on them. They're manufactured,
measured, sorted according to tolerance, and then the final tolerance is marked on them.

So the values start out as a normal (gaussian) distribution. The 20 percent
values will be the high and low band wings, with a hole in the center.

The ten percent band will be bi-modal, with a smaller hole in the center of that.

The five percent ones are simply picked from the center of the distribution.

Try it: find a 20 percent tolerance resistor that's within five percent of the nominal value.
Hint - you won'!


I have changed my "replacement spec" since this post, actually during. I think it was Leigh that pointed out that I was going to be pretty busy if used 10%

_________________
It's hard to solve an equation if every term is an unknown.

73
NE5U

Mike


Top
 Profile  
 
 Post subject: Re: Resistor In or Out of Tolerance Spreadsheet
PostPosted: Sep Fri 18, 2015 5:52 pm 
Member

Joined: Jan Thu 01, 1970 1:00 am
Posts: 4541
Location: Charleston, W.Va.
jim rozen wrote:
Try it: find a 20 percent tolerance resistor that's within five percent of the nominal value.
Hint - you won'!

Hi jim,
I agree with you on your explanation of original tolerance markings.

Your statement holds true for fresh-from-the-factory resistors, but not necessarily for older ones either NOS or used, or even for relatively new ones which have been in warehouse stock for a while.

It is possible to find a 20% tolerance resistor that measures "dead-on". But if its markings were correct it was not originally that way; it started out lower and has drifted up to marked value. If we assume that all factory tolerance markings were correct, then its original value was between 11%-20% below marked value, since if it were 10% or less it would presumably have gotten a gold or silver band. So if NOS should we use it, or if in a radio should we leave it? I would, but others might disagree due to the fact that having already drifted, it might continue to do so. Unfortunately there is no published data that I am aware of which proves that a resistor having once drifted will continue to do so. To be meaningful, such data would have to be compiled by measuring a large sampling of resistors, each individual one measured at intervals over a long period of time, possibly many years. Even then it would be impossible to know whether the resistors drifted due to their service in a radio, or whether they would have drifted on their own while sitting on a shelf, unless control samples were also maintained. Not to mention the possible effects of temperature and/or humidity. It is possible that quality manufacturers such as AB did indeed maintain such testing data compiled over a long period of time, but if so it would have been closely-guarded proprietary information, as no good manufacturer would have wanted to admit publicly that its resistors had any tendency to drift whatsoever. So it is unlikely we will ever have any such data, leaving us with only anecdotal evidence or guesswork.

To take this a step further, assume we have a 20% resistor in a radio which measures 15% above marked value. Depending upon its application, many of us would probably leave this one alone. But in doing so we should realize that it could possibly have already drifted as much as 35% from what it measured when it left the factory. On the other hand, it might not have drifted at all and is still at or near its original value. No way to know which. If we get too hung up on this it would lead us to replace almost every 20% resistor we encounter, and some fellows do exactly that, although I am not one of them.

_________________
Poston


Top
 Profile  
 
Post New Topic Post Reply  [ 13 posts ]  Moderator: Chuck Schwark

All times are UTC [ DST ]


Who is online

Users browsing this forum: No registered users and 2 guests



Search for:
Jump to:  
cron






























-->


Privacy Policy :: Powered by phpBB