¿Cómo creo una fórmula en Excel para esta estructura de tarifas?

Question

Tengo una celda que tiene un valor A1 = 3,500,000.

Quiero calcular una tarifa sobre esos $3,5 millones que siguen esta fórmula:

7% sobre el primer millón, más. 5% sobre el segundo millón, más. 3% sobre el tercer millón, más. 2% después de eso.

Lo ideal sería que todo esto sucediera en 1 celda, es decir, 1 fórmula en 1 celda en lugar de tener una tabla completa o una hoja que haga esto.

¡Gracias!

Answer 1

Ciertamente es posible resolver esto con una sola fórmula en una sola celda, pero si desea que sus fórmulas sean fáciles de seguir y cambiar, una tabla sería más limpia.

--

Solución de celda única

Para una solución de celda única, si el valor está en A1, esta fórmula calculará sus tarifas escalonadas:

= A1 * 0,07 + MÁX(A1-1000000,0) * -0,02 + MÁX(A1-2000000,0) * -0,02 + MÁX(A1-3000000,0) * -0,01

Multiplica el valor total por 0,07 y luego, para cada nivel, resta la diferencia en la tarifa entre el nuevo nivel y el anterior (hay numerosas formas de hacer esto; solo he proporcionado 1 ejemplo).

--

Solución de mesa simple

Para una solución de tabla simple, puede hacer lo siguiente:

Las fórmulas para calcular el valor en cada nivel.:

E2: =MÍN(C2,$A$1)

E3: =MIN(C3,$A$1)-SUM($E$2:E2)

E4: =MIN(C4,$A$1)-SUM($E$2:E3)

E5: =MÁX ($A$1-C4,0)

La fórmula para calcular la tarifa total.:

D6: =SUMAPRODUCTO(D2:D5,E2:E5)

Esta configuración es dinámica; puedes cambiar los umbrales o las tarifas y las fórmulas seguirán funcionando.

---

^{Si es posible que necesite niveles adicionales en el futuro, se recomienda una tabla XML. Si solo va a utilizar 4 niveles, pero los umbrales y tarifas para esos niveles pueden cambiar, la sencilla tabla anterior será suficiente.}

Answer 2

Se puede utilizar una tabla de búsqueda adecuadamente estructurada para esto (así como para problemas muy similares de cálculo de impuestos).

De tu mesa de comisiones

• Columna 1 = el monto base de las ventas
• Columna 2 = el porcentaje en cada nivel
• Columna 3 = el monto de la comisión ganada antes de ese nivel

En la siguiente tabla:
G1: 0
G2: =(E3-E2)*F2+G2
y complete tanto como sea necesario

Luego con datos enA1

=(A1-VLOOKUP(A1,commissionTbl,1))*VLOOKUP(A1,commissionTbl,2)+VLOOKUP(A1,commissionTbl,3)

Tenga en cuenta que si su estructura de comisiones cambia, es sencillo editar la tabla, mucho más sencillo que editar fórmulas.

Answer 3

Me gustaría mostrar 3 posibilidades.

1. Calcule por separado el valor del Nivel 1, luego el 2, el nivel 3 y el siguiente para el nivel 4, y finalmente obtenga el Total.

Para esto verifique la fórmula en las celdas:

E2: =IF(D2<=1000000, D2*0.07, 1000000*0.07)

F2: =IF(D2>1000000, IF(D2<=2000000, (D21-1000000)*0.05, 1000000*0.05), 0)

G2: =IF(D2>2000000, IF(D2<=3000000, (D2-2000000)*0.03, 1000000*0.03), 0)

H2: =IF(D2>3000000, (D2-3000000)*0.02, 0)

Y el final está enI2: =SUM(E2:H2)

---

2. Otra es la solución de fórmula única en I5.

Celda I5:

=IF(D2<=1000000, D2*0.07, IF(D2<=2000000, 1000000*0.07 + (D2-1000000)*0.05, IF(D2<=3000000, 1000000*0.07 + 1000000*0.05 + (D2-2000000)*0.03, 1000000*0.07 + 1000000*0.05 + 1000000*0.03 + (D2-3000000)*0.02)))

---

3. Y la última es la Fórmula I7para la versión superior de Excel donde se han utilizado las funciones LET.

Celda I7:

=LET( amount, D2,
        tier1, MIN(amount, 1000000),
        tier2, MAX(0, MIN(amount, 2000000) - 1000000),
        tier3, MAX(0, MIN(amount, 3000000) - 2000000),
        tier4, MAX(0, amount - 3000000),
        tier1*0.07 + tier2*0.05 + tier3*0.03 + tier4*0.02)

NÓTESE BIEN Ajuste las referencias de celda según sea necesario.