Retorna um valor dado que um número está dentro de um determinado intervalo

Question

Estou procurando uma pequena ajuda no meu problema de saques programados. Eu atualmente resolvo isso com funções IF aninhadas, mas estou obtendo mais e mais intervalos e isso leva a mais e mais funções IF aninhadas. Além disso, estou executando a otimização e, em determinado momento, ela explode.

O problema:

Low        High        Amount to withdraw
600000     2000000     32000
300000     599999      20000
0          299999      8000

Assim, um usuário entraria no armazenamento atual, digamos 325000 e a planilha usaria 20000. Se um usuário enviasse 153555, a resposta seria de 8000

Como eu disse com apenas 3 catracas usando cláusulas if aninhadas não é impossível, mas pode haver 10 catracas e cláusulas if aninhadas não são viáveis. Eu gostaria de resolver isso com fórmulas do Excel e não com uma macro.

    

Answer 1

Configure suas informações de colchetes no seguinte formato ¹ :

Low       Amount to withdraw
0         8000
300000    20000
600000    32000
2000000   #N/A

em algum lugar em sua pasta de trabalho; digamos E2:F5 (com cabeçalhos na linha 1). Então use a função

=VLOOKUP(value, E$2:F$5, 2)

para pesquisar e recuperar o segundo valor (ou seja, valor a retirar) na última linha da tabela onde o valor de E (extremidade inferior do parêntese) é ≤ o valor de pesquisa; por exemplo:

Umafórmulaumpoucomaisrealistaseria

=IF(ISBLANK(A1),"", VLOOKUP(A1, E$2:F$5, 2))

, que exibirá um resultado em branco para uma entrada em branco (em vez de tratá-la como 0).

¹ Não temos as extremidades altas dos intervalos na tabela porque não precisamos; 299999 e 599999 são redundantes quando temos 300000 e 600000 . Nós incluímos 2000000 como a extremidade inferior de um suporte não documentado que varia de 2000000 a ∞ .

    

Answer 2

Eu usaria o index e match function.
^{Supondo que você pode ordenar que sua tabela suba primeiro. Ou então =MATCH() não funciona corretamente}

=IF(F2<=INDEX(A:C,MATCH(F2,A:A,1),2),INDEX(A:C,MATCH(F2,A:A,1),3),"invalid")

Isso também verifica o valor mais alto. Eu ajustei sua tabela na linha 3 para este cenário. Agora, 400000 não corresponde a um intervalo válido e mostra inválido

Divida em pequenas fórmulas em células separadas (a entrada do usuário está na célula F2)

[G9]  =MATCH(F2,A:A,1)             Find row in table
[G10] =INDEX(A:C,G9,2)             Find corresponding high value
[G11] =INDEX(A:C,G9,3)             Find corresponding amount to withdraw
[G12] =IF(F2<=G10,G11,"invalid")   Check if input is lower then upper boundary