Fórmula genérica
=INT((amount-SUMPRODUCT(denoms,counts))/currentdenom)
Resumo
Para calcular as denominações monetárias exigidas, dado um valor específico, você pode construir uma tabela de cálculo de moeda conforme mostrado no exemplo. Esta solução usa as funções INT e SUMPRODUCT.
No exemplo mostrado, a fórmula em D5 é:
=INT(($B5-SUMPRODUCT($C$4:C$4,$C5:C5))/D$4)
Explicação
Para começar, a fórmula em C5 é:
=INT($B5/C$4)
Esta fórmula divide o valor na coluna B pela denominação em C4 (100) e descarta o restante usando a função INT. As fórmulas na coluna C são mais simples do que aquelas nas próximas colunas porque esta é a primeira denominação - não precisamos nos preocupar com as contagens anteriores.
Em seguida, em D5, primeiro descobrimos qual é o valor da denominação existente:
SUMPRODUCT($C$4:C$4,$C5:C5)
Aqui o SUMPRODUCT é configurado com dois arrays, ambos configurados cuidadosamente.
Array1 consiste em denominações da linha 4. Este intervalo é cuidadosamente construído para ser "expandido" quando copiado na tabela à direita. A primeira referência é absoluta ($ C $ 4) e a segunda referência é "mista" (C $ 4) - a linha está bloqueada, mas a coluna mudará, fazendo com que o intervalo se expanda.
Array2 consiste em contagens de denominação existentes da linha 5, com a mesma abordagem acima. O intervalo se expandirá conforme é copiado à direita.
O resultado desta operação SUMPRODUCT é o valor total das contagens de denominações existentes até agora na tabela. Isso é subtraído do valor original na coluna B e dividido pela denominação "atual" da linha 4. Como antes, usamos INT para remover qualquer resto.
Como as fórmulas na coluna C são copiadas na tabela, as contagens corretas para cada denominação são calculadas.
Verificando o resultado
Se você quiser verificar seus resultados, adicione uma coluna ao final da tabela com uma fórmula como esta:
=SUMPRODUCT(C$4:H$4,C5:H5)
Em cada linha, SUMPRODUCT multiplica todas as contagens por todas as denominações e retorna um resultado que deve corresponder aos valores originais na coluna B.
Função FLOOR
O exemplo nesta página usa INT, mas você também pode usar a função FLOOR para arredondar para baixo por denominador.
