Reparto unidades marginal

En las últimas semanas me han llegado dos o tres cuestiones bastantes similares relacionadas con los repartos marginales de cantidades, es decir, en cómo conseguir repartir en tramos diferentes ciertas cantidades.

Supongamos tenemos un contrato de ventas con un cliente donde según el número de unidades totales acumuladas en el tiempo las primeras 600 tienen un precio.
Las siguientes 650 otro precio.
Las siguientes 350 otro y
todas las demás un último precio.

Es decir tenemos cuatro tramos como los siguientes:

Repito las condiciones asociadas a los cuatro tramos:
Tramo I: desde 0 hasta 600
Tramo II: desde 600 hasta 1250
Tramo III: desde 1250 hasta 1600
Tramo IV: desde 1600 hasta 2850
indican que por las 600 primeras unidades vendidas en cualquier momento aplica un precio, por las siguientes 650 unidades vendidas en cualquier momento otro precio, las siguientes 350 un tercer precio y por las demás unidades, desde la 1600 en adelante un cuarto precio.

El trabajo consiste en montar una fórmula que nos permita distribuir linealmente, de manera marginal, sobre las ventas acumuladas en base a las ventas del mes...
Algo confuso, pero si revisamos el ejemplo de la imagen se entenderá mejor.

Disponemos nuestras unidades vendidas en el rango E1:M1.
Mientras que en E2:M2 insertamos el acumulado:
=SUMA($E$1:E1)

En el rango E7:M7 sumamos las unidades repartidas en nuestros cuatro tramos para cada periodo:
=SUMA(E3:E6)

En las celdas en verde (rango E8:M8) añadimos un control para verificar que las unidades repartidas por tramos en el periodo coinciden con las unidades vendidas:
=SI(E1=E7;"ok";"fallo")

De forma similar, para nuestro control en el rango N3:N6 tendremos la suma de la horizontal, es decir, de las unidades repartidas a lo largo de los periodos por cada tramo.
=SUMA(E3:M3)

Y en las celdas en verde de su derecha (rango O3:O6) un condicional que verifique coincida con las unidades máximas de cada tramo:
=SI(D3=N3;"ok";"fallo")

Con los datos y controles distribuidos en nuestro modelo, podemos comenzar a repartir por periodos y tramos...

Para el Tramo I en el rango E3:M3 añadimos:
=SI(MIN(E$1;E$1-E$2+$C3;$D3)<0;0;MIN(E$1;E$1-E$2+$C3;$D3))

Observa como para el rango del Tramo I, las unidades repartidas suman exactamente 600... i.e., las unidades que corresponden a este tramo.

Para el Tramo II en el rango E4:M4 añadimos:
=SI(SI(E$2>$B4;MIN(E$1-E$3;E$1-E$2+$C4;$D4);0)<0;0;SI(E$2>$B4;MIN(E$1-E$3;E$1-E$2+$C4;$D4);0))

Vamos a por el Tramo III en el rango E5:M5:
=SI(SI(E$2>$B5;MIN(E$1-E$3-E$4;E$1-E$2+$C5;$D5);0)<0;0;SI(E$2>$B5;MIN(E$1-E$3-E$4;E$1-E$2+$C5;$D5);0))

Y por fin el último, Tramo IV en el rango E6:M6:
=SI(E$2>$B6;MIN(E$1-E$3-E$4-E5;E$1-E$2+$C6;$D6);0)

Podemos probar a cambiar las unidades de cada mes en E1:M1.... pero si verificamos los distintos controles por periodo o por tramo, comprobaremos que el reparto es correcto, cumpliendo con lo necesario.

Como apunte al ejemplo dado, si nos fijamos en el cuarto periodo, donde disponíamos de 750 unidades a repartir, vemos como:
-van 50 primeras al Tramo I, lo que completarían las 600 uds de ese tramo
-van 650 al TramoII
-y las 50 restantes hasta completar las 750 del periodo acaban en el tercer tramo.

Este es un buen ejercicio para practicar con las funciones condicionales y en qué forma podemos construir pruebas lógicas apoyándose en funciones habituales como MIN.