Multiplicar diferentes factores según el valor de una celda en Excel

Question

Estoy intentando construir una fórmula que calcule lo siguiente:

=< 10,000 x 1.00

10,0001-12,500 x 1.25

12,501 - 15,000 x 1.50

Ejemplos:

Si el número es 8.520 (8.520 x 1,00) = 8.520

Si el número fuera 11.560 (10.000 x 1,00) + (1.560 x 1,25) = 11.950

Si el número es 13.500 (10.000 x 1,00) + (2.500 x 1,25) + (1.000 x 1,50) = 14.625

Answer 1

Esto se puede hacer con un if. La forma de construir un if anidado es hacer lo que sucede con diferentes valores. Suponiendo una columna de datos que comienza en A2, entonces

1. A2<10000. El resultado es a2 + 0 o A2
2. 10000 < A2 <12501 El resultado es a2 +(.25 * (A2-10000) )
3. 12500 < A2 <15000 El resultado es a2 +(.25* (a2-10000)) + (.25 *( A2-12500))

Para convertir esto a una fórmula única, simplemente use un if para cada caso. El flujo es =if(prueba,verdadero,falso) donde si la prueba es verdadera, se obtiene el valor verdadero; en caso contrario, se obtiene el valor falso. La primera solución utiliza 3 si, el resultado falso de la prueba es cero, el resultado verdadero es la respuesta

• =SI(A2<10001,A2,0)+SI(Y(A2>10000,A2<12501),A2+(0.25*(A2-10000))+SI(Y(A2>12500,A2<15001),A2+ (0,25*(A2-10000))+(0,25*(A2-12500))))

Esto se puede simplificar usando algo de álgebra para

• =A2+SI(A2>10000,(0.25*(A2-10000)),0)+SI(A2>12500,(0.25*(A2-12500)))

Cualquiera de los formularios se puede cambiar según cómo se desee tratar con números superiores a 15.000.

Answer 2

Bueno, fue un poco más complicado de lo que se pensaba al principio.

cree una tabla como la siguiente:

Luego use la siguiente fórmula matricial:

=SUM(IF(A2>$D$2:$D$4,IF(A2<$E$2:$E$4,A2-$D$2:$D$4,$E$2:$E$4-$D$2:$D$4)*$F$2:$F$4,0))

Al ser una fórmula matricial, se debe confirmar con Ctrl-Shift-Enter en lugar de Enter al salir del modo de edición. Si se hace correctamente, Excel implementará {}la fórmula.

---

Basándonos en la fórmula de @fixer y haciendo que la fórmula sea dinámica, creamos una tabla como esta:

Como puede ver, el Factor ahora es el aumento del factor anterior y no el factor real.

Entonces podemos usar esta fórmula:

=A2+SUMPRODUCT((A2>$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*MOD(A2,$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*$E$3:INDEX(E:E,MATCH(1E+99,D:D)))

La fórmula ahora es dinámica en el sentido de que a medida que la tabla crece o se reduce, también lo hará la referencia al conjunto de datos. Encuentra INDEX(D:D,MATCH(1E+99,D:D))la última celda de la columna y la establece como la extensión del conjunto de datos.