Fórmula do Excel IF / AND com MIN Calculating Due Dates

Question

Estou criando uma planilha para planos de pagamento. Estou tentando criar uma fórmula para calcular a data de vencimento com base no valor e na data de inscrição. Eu quero a fórmula para calcular para diferentes níveis do plano, que é onde eu estou correndo em problemas. Aqui está o critério:

(sem baixa renda)

Valor vencido: US $ 1- US $ 150, cronograma: seis semanas

Valor em dívida: US $ 151 a US $ 300, cronograma: 10 semanas

Valor vencido: US $ 301 a US $ 500, cronograma: 12 semanas

Valor devido: US $ 501 a 700, linha do tempo: 14 semanas

Valor devido $ 701 a $ 1000, cronograma: 18 semanas

(baixa renda)

< = $ 500 / $ 25 (parcela) = # de meses (max 18 meses)

=$501/$50(installment)= # of months (max 18 months)

Aqui está a fórmula que eu tenho que funciona apenas para a parte da prestação de $ 501 / $ 25 e a parte de baixa renda: = SE (AND (B2="Y"), MIN (D2 + 546, (((C2 / 25 ) * 30) + D2)), SE (E (C2 > 1, C2 < = 150), D2 + 6 * 7, IF (E (C2 > 151, C2 < = 300), D2 + 10 * 7 , IF (E (C2 > = 301, C2 < = 500), D2 + 12 * 7, SE (AND (C2 > = 501, C2 < = 700), D2 + 14 * 7, SE (E (C2 > 701), D2 + 18 * 7))))))

Eu não consigo conseguir os dois $ 501 / $ 50 para encaixar na fórmula. O 546 representa 18 meses em dias. Qualquer ajuda seria muito apreciada!

Planilha Critérios Critérios2

    

Answer 1

=IF(B2="Y",IF(C2<=500,MIN(D2+546,(((C2/25)*30)+D2)),MIN(D2+546,(((C2/50)*30)+D2))),IF(C2<=150,D2+6*7,IF(C2<=300,D2+8*7,IF(C2<=500,D2+12*7,IF(C2<=700,D2+14*7,D2+18*7)))))

Simplifiquei alguns termos e ajustei o cronograma de US $ 151 a US $ 300 para concordar com sua tabela ( 8*7 em vez de 10*7 ), mas a única coisa que realmente estava faltando era a parte IF(C2<=500,MIN(D2+546,(((C2/25)*30)+D2)))),MIN(D2+546,(((C2/50)*30)+D2))) , conforme necessário para verificar a quantia devida se a baixa renda.

Deixei os termos de multiplicação nisto, mas se você tiver uma planilha grande dessas fórmulas, recomendo substituí-las por valores reais, como substituir +8*7 por +42 . Até mesmo pequenos cálculos podem aumentar a lentidão de uma planilha.

No entanto, embora eu não seja especialista em finanças, o uso de 30 dias para igualar um mês parece impreciso. Você pode querer verificar o uso de EDATE .

    

Answer 2

@Rey Juna & @ Scott Craner, muito obrigado! Acabei usando:

= SE (B2="Y", SE (C2 < = 500, MIN (EDATE (D2,18), (((C2 / 25) * 30) + D2)), MIN (COMIDA (D2,18 ), (((C2 / 50) * 30) + D2))), IF (C2 < = 150, D2 + 42, IF (C2 < = 300, D2 + 70, IF (C2 < = 500, D2 + 84 , IF (C2 < = 700, D2 + 98, D2 + 126)))))

Obrigado por também percebe o meu erro na tabela, o img snip estava realmente incorreto, então eu atualizei isso. Não consegui usar o EDATE para calcular o mês com a divisão, mas consegui usá-lo como o limite de 18 meses. No meu caso 30 dias é bom, porque eu tenho um período de carência generoso para as contas devidas.

    

Answer 3

Podemos simplificar a fórmula e usar algumas outras funções para retornar a quantidade desejada para adicionar à data:

=D2+IF(B2="Y",MIN(546,(C2/((C2>500)*25+25))*30),LOOKUP(C2,{0,151,301,501,701},{6,10,12,14,18})*7)

Assim, se Y , o mínimo de 546 dias e os dias retornados por (C2/((C2>500)*25+25))*30)

(C2>500) retornará 1 se verdadeiro e 0 se falso, que então é multiplicado por 25 e adicionado a 25 para retornar 50 ou 25 ao denominador.

Se N , em seguida, ocorrerá a pesquisa, que, por ser relativa, retornará o número correto de semanas com base no local em que o valor está no primeiro array.