This blog entry is submitted to VLOOKUP Week
Converting color bands to the equivalent resistance value is a good example of how table lookup function can be employed. Anyone can pick a separate color for every band through a drop-down list (built from data validation facility) and Excel matches color selected to the reference table, counts and displays the resistance value. As a visual aid of the band and the corresponding color, a table containing both information should be used.
Resistor is one of the fundamental components in electronics. The resistance value can be obtained by interpreting the color bands circling around its body. For a general purpose, fixed resistor, there are usually four color bands. First and second band denote the equivalent numerical values. The third band is the decimal multiplier. Finally, the last one is the tolerance (in percentage).
The color and its equivalent value for those four bands are shown below:
Note that this is a simplified table for practical use.
As for the resistor shown on Figure 1 above, the first and second band are brown and violet, respectively. Looking up to the figure 2, this would bring a value of 17 (one for brown; seven for violet). The third band is yellow, so the multiplier is set to: x104.
The resistance value is 17 x 104 = 170000 = 170 KΩ.
The fourth band is gold, so the tolerance is 5%. The actual resistance value can then go from 5% below 170 KΩ to 5% above it, i.e. 161.5 KΩ to 178.5 KΩ.
Conversion in Excel
The converter in Excel is composed of three separate tables as shown below:
To use this converter, anyone had just to pick the color on cell B5 through E5 (by a drop-down list) and the converter will count and display the resistance value and allowable range on cell J5 and K5, respectively.
The cell B5 through E5 reads the data on the third table (the one in the bottom) via data validation facility. Drop-down list is built through this procedure:
- Highlight cell B5
- Choose Data tab
- Click Data Validation > Settings > Validation Criteria > Allow > List.
- In Source, click the red arrow button on the left.
- Highlight cell B12 to B24. Press Enter.
- Make sure In-cell dropdown is checked. See Figure 5. Press Enter.
- Repeat step 1 to 6 for cell C5, D5 and E5.
The most important part about how this converter works lies on the table lookup function. The HLOOKUP function. The equivalent numerical values for first band (F5) and second band (G5) is delivered by matching the color picked (B5 and C5, respectively) on color bands with the Value row on the reference table (the one in the middle).
On F5, the formula would be:
The formula on cell H5 would be:
The percentage of tolerance for fourth band (I5) can be obtained by simply referencing the color picked on cell E5 with Tolerance row on the reference table, like the one below:
The resistance value (J5) can be calculated from the combination of first and second numerical value and multiplied by the multiplier.
The same result could also be achieved by multiplying the first numerical value with ten and adding the result to the second numerical value:
Finally, the allowable range use the percentage of tolerance to lower and raise the resistance value. This would be:
=J5*(100%-I5)&” – “&J5*(100%+I5)
The workbook can be downloaded below.
(Note: Microsoft Excel Web Application didn’t support data validation feature. Download the file and open it with Microsoft Excel instead)