Para calcular o custo real vs teórico, primeiro você precisa calculá-los em relação ao número de kits:
Fórmulas:
- Custoteórico:
=A21*SUMPRODUCT($E$6:$E$16,$D$6:$D$16)
- Realsubtotal:
=SUMPRODUCT(ROUNDUP(A21*$E$6:$E$16/$C$6:$C$16,0),$B$6:$B$16)
-estaéumafórmuladematriz,entãovocêprecisaterminá-lacomCTRL+SHIFT+ENTER - Euincluítambémumacolunadecusto"real / teórica", que apenas divide o subtotal real pelo custo teórico, então dá a você também a quantidade de informação "extra" que você gasta acima do valor real dos kits
- NOTA: o intervalo de entrada é o mesmo que foi postado na pergunta
Gráfico:
- em vez de um único gráfico, sugiro usar gráfico dinâmico , isso permite também a filtragem de dados de entrada (zoom para um intervalo específico para ver melhor os custos)