Como manter um saldo de pagamentos em execução versus faturamento no Excel

Question

Eu tenho uma linha de células com 12 colunas mensais, G4 (janeiro) a R4 (dezembro), em que vou inserir o valor pago para um faturamento mensal. [Note que um comentário inicial discute vários anos em várias linhas. No entanto, isso não é mais o caso; é apenas um ano, na linha 4.]

Quando o faturamento tem um valor devido, é um valor padrão, cujo valor é armazenado na célula F4.

Se um mês não tiver um valor devido, deixo a célula G4: R4 em branco. Se um valor é devido, eu entro no valor pago (zero se nada), na célula associada.

Eu gostaria que a célula A4 mostrasse um saldo total do saldo devedor (valores devidos menos os valores pagos). Exemplo: A fatura mensal é de 100,00 (F4). Eu paguei 75 dólares em janeiro (G4), então o total que ainda devo é de 25.00 (A4). Eu paguei 50 dólares em fevereiro para a conta de fevereiro (H4), então agora devo 75 (A4).

Observe que nunca haverá um caso em que não haja valor faturado, mas eu pago para um saldo anterior; Se houver um saldo anterior, sempre haverá um faturamento para o valor padrão. Então, uma célula em branco no G4: R4 significa que nada foi faturado; um valor em G4: R4 significa que o valor padrão foi faturado e o valor inserido foi pago.

    

Answer 1

Em A4, basta colocar:

=$F4*COUNT($G4:$R4)-SUM($G4:$R4)

EDITAR:  depois que seu comentário removeu alguns sinais "$" para que a fórmula possa ser facilmente copiada nas 15 linhas necessárias.

    

Answer 2

Nos comentários que você deu, o intervalo de células para seus meses é G4: R4

Agora, para sua resposta:

A fórmula que você vai querer colocar no seu total em execução (A4) é:

=IF( SUM($G4:$R4) < $F$4*COUNTA($G4:$R4), $F$4*COUNTA($G4:$R4) - SUM($G4:$R4), 0)

Vamos dividir isso e analisar as partes:

COUNTA($G4:$R4)

COUNTA é a abreviação de "Count All". Ele conta o número de células não vazias dentro do intervalo G4: R4. Isso porque você disse que a fórmula deve ignorar espaços em branco. Além disso, acompanhe quantos meses "passaram".

F4*COUNTA($G4:$R4)

Este é o valor total devido. (Pagamento mensal) * (# de meses)

SUM($G4:$R4)

Esse é o valor total pago. Por padrão, isso ignora os espaços em branco.

Então, juntos, basicamente temos

IF( TotalPaid < TotalOwed ) display TotalOwed - TotalPaid

Else display 0

    

Answer 3

Nota: Esta resposta foi escrita antes do esclarecimento da pergunta e não reflete a redação atual. Eu estou deixando isso no lugar apenas porque os comentários documentam as razões para as revisões da questão.

Existem várias respostas que fazem a suposição de que você paga algo todo mês. E se você não fizer? Sem entrar em questões como multas ou multas por atraso, o valor faturado é devido a cada mês, independentemente do histórico de pagamento. Contar meses com pagamentos não garante uma resposta correta. Para fazer isso, você precisa de um método de especificar quantas contas foram feitas.

Pode haver peculiaridades em seu ciclo de faturamento e o período de referência pode ser em um ano anterior. Se o cálculo do número de ciclos de faturamento até a data não for simples, esse cálculo pode ser outra pergunta de superusuário. Para simplificar, neste exemplo, janeiro significa o período de faturamento 1 e você não está preocupado com o dia do mês em que o faturamento é devido.

Para este exemplo simples, o A4 pode conter:

=$F$4*MONTH(TODAY())-SUM(G4:R4)

Suponho que você esteja copiando A4 para A5 no próximo ano, quando estiver usando valores em G5: R5. Algumas notas:

• Se o valor de faturamento padrão puder mudar de ano para ano, não ancore F4 com cifrões; cada linha teria um valor de coluna F relevante.
• Se o valor de faturamento padrão puder ser alterado no meio do ano, você precisará de uma fórmula mais complicada.
• Esta fórmula pressupõe que tudo se equilibra em dezembro e nada é transferido para o ano seguinte. Se esse não for o caso, você deve adicionar o saldo de fim de ano em uma célula, um local lógico seria o início do próximo ano e incluí-lo na sua soma.