Fórmula genérica
=commission*VLOOKUP(amount,split_table,column,TRUE)
Resumo
Para calcular uma comissão, e dividir a Comissão entre Agente e Corretor de acordo com um cronograma pré-estabelecido, você pode usar a função VLOOKUP. No exemplo mostrado, a fórmula em D5 é:
=C5*VLOOKUP(B5,split_table,2,TRUE)
onde split_table é o intervalo nomeado G7: I11
Explicação
Esta fórmula assume que todas as comissões são de 3%, conforme definido na célula H4, o intervalo nomeado comm_rate .
O valor da comissão é dividido entre o agente e o corretor, começando com uma divisão 50/50 e mudando em intervalos fixos, conforme mostrado no intervalo nomeado split_table (G7: I11).
O valor da comissão é calculado na coluna C com esta fórmula:
=B5*comm_rate // total commission
Os valores do agente e do corretor nas colunas D e E são calculados assim:
=C5*VLOOKUP(B5,split_table,2,TRUE) // agent =C5*VLOOKUP(B5,split_table,3,TRUE) // broker
Observe que essas fórmulas são idênticas, exceto para o índice da coluna. A fórmula para o agente retorna um valor da coluna 2 da tabela, e a fórmula para o corretor retorna um valor da coluna 3 da tabela. Em ambas as fórmulas, VLOOKUP é definido para uma correspondência aproximada explicitamente, fornecendo o quarto argumento opcional como TRUE.
Ao fazer uma correspondência aproximada, VLOOKUP assume que a tabela está classificada em ordem crescente. Se VLOOKUP encontrar uma correspondência exata, ele retornará um valor da linha correspondente. Se VLOOKUP encontrar um valor maior que o valor de pesquisa, ele retornará um valor da linha anterior.
