Color Bands to Resistance Value Converter with HLOOKUP

Posted on by

VLOOKUP Week

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.

Color Bands

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).

Resistor

Figure 1. Resistor with 4 color bands

The color and its equivalent value for those four bands are shown below:

Resistor Color Bands

Figure 2. Color Bands

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:

Color Bands to Resistance Value Converter

Figure 3. Converter in Excel

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.

Data Validation

Figure 4. Picking Up The Color

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:

  1. Highlight cell B5
  2. Choose Data tab
  3. Click Data Validation > Settings > Validation Criteria > Allow > List.
  4. In Source, click the red arrow button on the left.
  5. Highlight cell B12 to B24. Press Enter.
  6. Make sure In-cell dropdown is checked. See Figure 5. Press Enter.
  7. Repeat step 1 to 6 for cell C5, D5 and E5.
Drop-Down List

Figure 5. Drop-Down List built from Data Validation menu

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:

=HLOOKUP(B5,$B$7:$M$9,2,0)

On G5:

=HLOOKUP(C5,$B$7:$M$9,2,0)

The third band, or multiplier, is actually the decimal powers of n, where n is the number assigned to a color. This way the converter could still use Value row on the reference table, but for a different purpose.

The formula on cell H5 would be:

=10^HLOOKUP(D5,$B$7:$M$9,2,0)

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:

=HLOOKUP(E5,$B$7:$M$9,3,0)

The resistance value (J5) can be calculated from the combination of first and second numerical value and multiplied by the multiplier.

=VALUE(F5&G5)*H5

The same result could also be achieved by multiplying the first numerical value with ten and adding the result to the second numerical value:

=(F5*10+G5)*H5

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)

8 thoughts on “Color Bands to Resistance Value Converter with HLOOKUP”

  1. Pingback: From ‘Beginning Excel’: “HLOOKUP to Find Resistor Values” « VLOOKUP WEEK

  2. Pingback: Introduction to Spreadsheet | Beginning Excel

    • I do admit that the theme is kind of “generic” :-) As of now, I’m still concentrating on digging out ideas, writing some new articles and spreading out the words about this site. But thanks for the suggestion!

      Reply ↓

    • Thank you. Well, I’m an electronic engineer and I do often bump into complicated calculations which can be solved quite easily with Excel. This article describes one of them. Actually, there are plenty more to cover. Just keep visiting! :-)

      Reply ↓