Fórmula para totalizar os valores com base na assinatura de novos clientes e nos subscriptions existentes

Question

Sempre que um novo cliente se inscreve (indicado pelo valor 1 em "Novo cliente"), ele começa a pagar US $ 50 nos primeiros quatro meses e US $ 60 depois do quarto mês.

            Month1  Month2  Month3  Month4  Month5  Month6  Month7  Month8  Month9  Month10 Month11     
New Customer    1      0       0       0       1       0       1       0       1       0       0        
Active  Cust's  1      1       1       1       2       2       3       3       4       4       4        

Total       $50.00  $50.00  $50.00  $50.00  $110.00 $110.00 $160.00 $160.00 $220.00 $220.00 $230.00         

Por exemplo, no sexto mês, você tem um cliente pagando US $ 50 e outro US $ 60, então esse total deve ser US $ 110.

Preciso de ajuda para criar uma fórmula para calcular os valores em "Total"

    

Answer 1

A maneira mais clara de resolver um problema como este é o layout da planilha para refletir a lógica e as regras. Você tem uma regra para os clientes nos primeiros quatro meses e outra regra para depois disso. Então eu coloquei a planilha assim:

A linha 3 calcula o número atual de clientes que estão em seus primeiros quatro meses. As colunas B a D precisam de uma fórmula ligeiramente diferente neste exemplo, porque o exemplo ainda não tem quatro meses. Então a fórmula em B3 é:

=SUM($B2:B2)

Isso ancora o ponto inicial na coluna B e o intervalo se expandirá à medida que você o replica em C e D. A coluna E é o primeiro ponto com quatro meses, portanto a fórmula é:

=SUM(B2:E2)

Conforme você replica isso nas colunas restantes, os pontos inicial e final serão alterados para refletir apenas os clientes em seus primeiros quatro meses nesse ponto.

Não há dados neste exemplo para a linha 4 até o quinto mês, então a fórmula em F4 é:

 =SUM($B2:B2)

No mês 5, apenas os clientes que começaram no mês 1 são afetados. Nos meses seguintes, esses clientes ainda afetam o valor mensal e você adiciona clientes mais recentes.

Então, isso faz com que o cliente conte cada mês nas duas taxas. O total é de US $ 50 para os clientes da linha 3, mais US $ 60 para os clientes da linha 4. Portanto, a fórmula em B5 é:

=50*B3+60*B4

Replique isso na linha.