Fórmulas do Excel para dividir e combinar IF, AND, MIN

Question

Estou tendo alguns problemas com algumas fórmulas no Excel. Eu tenho três linhas de dados para três pessoas que estão dividindo os valores igualmente até que duas pessoas atendam a um número máximo. então todos os valores vão para uma única pessoa. Aqui está o critério

linha 1 tem um máximo em 800
linha 2 não tem um máximo de 2 linha 3 tem um máximo em 300

fórmula para linha 1 =MIN(800, SUM (D1:BF3)/3)
fórmula para linha 2 =IF(AND((BG3=300),(BG1=800)),(SUM(D1:BF3)-1100),(SUM(D1:BF3)/3))
fórmula para linha 3 = MIN(300, SUM (D1:BF3)/3)

aqui estão os dados brutos e os totais de cada linha usando essas fórmulas

Linha 1: R1 = 10, U1 = 50, AC1 = total de 20 em BG1 = 513 Linha 2: R2 = 10, AC2 = 20 no total em BG2 = 513
Linha 3: N3 = 100, R3 = 10, W3 = 1250, Z3 = 50, AC3 = total de 20 em BG3 = 300

o total real de todas as células é 1540. A linha 3 tem um total de 300, de modo que os totais da Linha 1 e 2 devem ser 1540-300 = 1240/2 = 620 para BG1 e 620 para BG2. Como você pode ver, BG1 e BG2 são curtos.

O que eu preciso é pegar todas as células para cada uma dessas pessoas e dividi-las por 3 delas, uma vez que a linha 3 atinge 300, então divida todas (linha 1, 2 e 3) por 2 e então os totais da Linha 1 e 2 continua a somar, uma vez que o total da linha 1 atinge 800, o total da linha 2 continuará a aumentar o total de todas as células (linha 1,2,3)

a fórmula para a linha 2 e 1 precisa de um OR em algum lugar?

Espero que isso não seja muito confuso. Se eu pudesse anexar a folha que pode torná-lo um pouco mais fácil de entender.

    

Answer 1

Sua fórmula para BG3 está bem. O BG1 precisa ser ajustado para o caso em que o BG3 é de 300:

=MIN(800,IF(BG3=300,(SUM(D1:BF3)-300)/2,SUM(D1:BF3)/3))

Semelhante ao BG2:

=IF(BG3=300,IF(BG1=800,SUM(D1:BF3)-1100,(SUM(D1:BF3)-300)/2),SUM(D1:BF3)/3)

    

Answer 2

Fórmulas simplificadas de acordo com sua lógica:

• sua fórmula da linha 3 está correta: = MIN(300, SUM (D1:BF3)/3)
• para a linha 1: =MIN(800, (SUM(D1:BF3)-BG3)/2)
  É metade do valor restante depois de subtrair o valor da linha 3, mas não mais que 800
• fórmula para linha 2 =SUM(D1:BF3)-BG1-BG3
  Em qualquer caso, é a soma total menos o sol se as outras duas linhas.