Código de colores de una resistencia con excel. Directo e inverso

Para conocer el valor de una resistencia se utiliza un código de colores a cuatro bandas, la primera banda se reconoce porque es la más cercana al borde del cuerpo de la resistencia mientras que la cuarta banda (la tolerancia) está más separada respecto a las otras tres. 

• Los colores posibles de las dos primeras banda son 9. Cada uno corresponde a un número entre 0 (negro) y 9 (blanco) , siguiendo el orden de los colores del arco iris (negro, marrón, rojo, naranja, amarillo, verde, azul, gris y blanco).
• La primera banda nos indica el primer dígito del valor de resistencia. La segunda banda nos da el segundo dígito de dicho valor. Los dos dígitos de las primeras dos bandas nos dan un número que puede variar entre 0 y 99.
• La tercera banda es el multiplicador, es decir, un factor con el cual debemos multiplicar el número de las dos primeras bandas. Por ejemplo, si el valor de las primeras bandas es 47 y el multiplicador es 1000 (o 1K) el valor de resistencia será de 47.000 ohms (47K). En la tabla pueden ver todos los colores, las bandas y los valores correspondientes. En la parte alta del diseño podemos ver un ejemplo concreto.

Para resistencias codificadas con 4 bandas el más conocido se llama E12 y está compuesto, como su nombre lo indica, por una serie de 12 números que son: 10, 12, 15, 18, 22, 27, 33, 39, 47, 56, 68, 82 y que se repiten para cada década del multiplicador. En la figura podemos ver todos los valores estándar E12 posibles que son 108 (12 números x 9 multiplicadores posibles). Por ejemplo, una resistencia con las dos primeras bandas rojas tendrá un valor numérico de 22 pero en base a la tercera banda el valor final podrá ser de 0,22 ohms, 2,2 ohms, 22 ohms, 220 ohms, 2,2K, 22K, 220K, 2,2M o 22M.

Cuando empecé con la calculadora de resistencias en Excel ya sabía que no todas las posibles combinaciones de colores se correspondían con un valor comercial, pero consideré que, en un primer intento no iba a contar con ello, por lo que en el libro excel se pueden seleccionar combinaciones de colores que que no se fabrican. A la inversa, a partir de un cálculo matemático en el que encontramos un valor de resistencia, la hoja de cálculo solo da valores comerciales.

La hoja de cálculo permite saber el valor de una determinada resistencia a partir de sus colores (3 colores) y el conocer los colores que corresponden a un determinado valor de una resistencia teórica como suma de hasta tres valores comerciales, en ohmios, siempre y cuando el valor teórico sea entero y superior a 10 ohmios. Creo que haré una nueva versión contemplando esos detalles.

Funciones utilizadas:

• Indirecto(Cad):Convierte una cadena alfanumérica en una dirección excel.
• Indice(). Devuelve el contenido de una celda incluida en un rango.
• Extrae(texto;pos. inicial;N. Caract.). Devuelve una serie de caracteres de una cadena.
• Coincidir(valor;matriz;tipo busq.). Encuentra un valor en un rango de valores.
• Si.Error(valor;valor si error). Si se produce un error devuelve un valor determinado por el segundo parámetro.

Variables y rangos con nombre:

• CFr1:=Aux!$A$3:$A$11 Colores de la primera franja.
• CFr2:=Aux!$A$2:$A$11. Colores de la segunda franja.
• CFr3:=Aux!$C$2:$C$10. Colores de la tercera franja.
• VCom1:=Resistencia!$D$13. Valor comercial 1.
• VCom1:=Resistencia!$F$13. Valor comercial 2.
• VComer:=Aux!$K$2:$K$92. Valores comerciales.
• VForm:=Resistencia!$B$13. Valor teórico de la resistencia.

Formato condicional en las celdas B2 a B5 de la hoja Resistencia.

Valor de una resistencia según sus colores:

• El color de cada una de las bandas se selecciona mediante un desplegable. El color, en este caso, aparece como un literal. 
• Cada desplegable tiene una celda vinculada con el valor seleccionado. En este caso de C2 a C5.
• Los valores numéricos correspondientes a cada color de cada banda están en la hoja Aux.
• Para recuperar cada valor utilizo, en este caso, la función INDIRECTO. Con una sola fórmula calculo el valor total (=(INDIRECTO("Aux!B" & C2+2)*10+INDIRECTO("Aux!B" & C3+1))*INDIRECTO("Aux!d" & C4+1))
• Este cálculo también se puede hacer mediante la función INDICE. (=(INDICE(Aux!B3:B11;$C2)*10+INDICE(Aux!B2:B11;$C3))*INDICE(Aux!D2:D9;$C4). Los rangos a los que se refieren las funciones INDICE pueden sustituirse por un nombre de rango, que previamente se haya dado de alta con el administrador de nombres.
• Por último, hay  una pequeña comprobación de que un determinado valor esta en la tabla de valores comerciales. (Celdas H y J).

Formato condicional:

• Los formatos condicionales de las celdas con formato condicional, en este caso, dependen del valor de la celda asociada al desplegable. Así que  el formato condicional depende de una fórmula. Utilizo la formula =c2=1 para dar de fondo un color marrón. Es un poco pesado, hay que hacerlo de uno en uno.

Valores comerciales que nos aproximan a un valor calculado:

• Aquí utilizo la función COINCIDIR(VBuscado;Rango en donde se busca; tipo de búsqueda), en este caso tipo de búsqueda=1, con el que encuentra, en una lista ordenada, el último número que es menor o igual al valor buscado. Esta función nos devuelve la posición en la que está el valor buscado, no el valor encontrado. Con la función INDICE recupero el valor encontrado.  =INDICE(VComer;COINCIDIR(VForm;VComer;1)), utilizando además nombres en vez de rangos.
• Las otras dos resistencias, en serie, se calculan restando al valor inicial las resistencias encontradas y, con ese valor, repetir el proceso de búsqueda.

Segundo color dependiendo del primero