Como usar uma fórmula do Excel para comparar um valor calculado com uma tabela, considerando vários critérios

Question

Eu tenho o Excel 2016.

Eu tenho dois valores que saem de algumas fórmulas que eu quero comparar a uma tabela e saída ou "tamanho do tubo é ok" ou "tamanho do tubo é muito pequeno". Um dos valores que obtenho é o comprimento máximo (dev) desenvolvido e que deve ser arredondado para o próximo valor de tabela maior. Então, usando essa coluna na tabela, eu pego o número de unidades fixas que tenho e faço referência a isso tanto no tamanho do tubo quanto no comprimento máximo do dev. Se o número de unidades de fixação for maior que o máximo permitido para esse tamanho de tubo, dado o comprimento máximo permitido permitido, o tubo precisaria ser submetido a upsizing. se o número de unidades de fixação for menor que o permitido, o tamanho do tubo é aceitável.

minha tabela basicamente se parece com isso:

distro pipe 40 60 80 100 ... < = comprimento máximo do desenvolvedor (ft) em 1/2" 2.5 2 1.5 1.5 ... < = unidades máximas permitidas
3/4" 9.5 7.5 6.5 5.5 ...
1" 32 25 20 16.5 ... e 1-1/4" 32 32 27 21 ...
1-1/4" 32 32 32 32 ... e 1-1/4" 80 80 80 75 ... e 1-1/2" 87 87 87 87 ... e ... ....

Então, considerando meus outros cálculos, recebo um comprimento máximo de 49 pés para um cano de 3/4 "e uma contagem total de fixações de 56. Meu resultado deve sair" O tamanho do cano é muito pequeno "

Meu outro problema é que tenho várias instâncias do mesmo valor nas colunas de medidor e de tubo de distribuição. portanto, eu preciso de uma fórmula interativa para encontrar o valor que é maior do que o número de unidades fixas que eu obtenho.

Eu usei uma função de correspondência de índice para arredondar o comprimento máximo desenvolvido até o próximo valor de coluna mais alto, mas não consigo descobrir uma fórmula para referenciar os valores da unidade de fixação.

=INDEX('pipe sizing charts'!$C$2:$M$2,MATCH($H$59,'pipe sizing charts'!$C$2:$M$2,1)+(LOOKUP($H$59,'pipe sizing charts'!$C$2:$M$2)<>$H$59))

em que C2: CM são meus números de tabela de comprimento max dev e H59 é o comprimento máximo de desenvolvimento da seção que estou dimensionando

    

Answer 1

Eu acho que tenho isso.

Minhas suposições de sua pergunta:

• Sua fórmula INDEX encontra a coluna correta
• Você precisa descer essa coluna para encontrar o valor um nível acima do valor calculado
• Você já tem o valor que está procurando (no caso da sua pergunta: 56)
• Você precisa retornar os dados da coluna B (acho que essa é a referência da sua primeira coluna)

Então, se minhas suposições estiverem corretas, primeiro você precisa classificar os dados na tabela em ordem decrescente.

Você precisa retornar a letra da coluna que deseja pesquisar. Envolvendo sua fórmula INDEX em uma função SUBSTITUTO / ENDEREÇO deve funcionar

= SUBSTITUTO (ENDEREÇO (1, COLUNA (ÍNDICE ('gráficos de tamanho de tubos'! $ C $ 2: $ M $ 2, CORRESPONDÊNCIA ($ H $ 59, 'gráficos de tamanho de tubos'! $ C $ 2: $ M $ 2,1) + (LOOKUP ($ H $ 59, 'gráficos de tamanho de tubos'! $ C $ 2: $ M $ 2) < > $ H $ 59))), 4), "1", "")

Agora que você tem a letra da coluna, você pode usar INDIRECT para escrever seu INDEX / MATCH

= INDEX ('gráficos de tamanho de tubos'! B: B, MATCH ($ H $ 59, INDIRECT (xx & ":" & xx), - 1))

O xx na fórmula acima é a referência da célula em que você inseriu a fórmula SUBSTITUTE acima. A CORRESP trabalha neste caso, pois o tipo de correspondência "-1" procura o próximo número maior que o que você está procurando, mas os dados precisam ser classificados em ordem decrescente para que funcione.

O acima deve fazê-lo, com talvez alguns pequenos ajustes para ajustá-lo em sua planilha.

Deixe-me saber como você vai.