Contagem do Excel em vários critérios

Question

Este é um desafio divertido para o Excel (eu). Eu tenho uma planilha com as seguintes colunas:

Customer   Date    lat.  long.

Agora, há 10.000 deles no conjunto de dados inicial. O que eu gostaria é de uma fórmula que me permita:

1. Encontre todos os outros registros com menos de seis meses no futuro.
2. Calcule a distância entre este cliente e cada um dos outros registros de # 1.
3. Conte o número que está dentro de seis meses e dentro de uma milha.
4. Repita para cada cliente no conjunto de dados.

Estou ciente de que existem maneiras de calcular a distância, mas o principal é descobrir como fazer os seis meses no futuro E a distância sem precalcular todas as distâncias em uma grade de 10K x 10K.

Agora, se eu estivesse trabalhando em um idioma, simplesmente faria um loop e verificaria cada item e o deixaria rodar. No entanto, o desafio é prototipar isso E provar para um amigo que isso é possível no Excel.

Para adicionar notas adicionais com base no comentário:

A questão principal para mim não é o cálculo da distância, mas sim obter a contagem de cada um dos resultados e depois percorrer toda a lista. Então, por exemplo:

FIRST     12/01/1990   20  30
SECOND    04/02/1991   40  50
THIRD     05/16/1991   10  20
FOURTH    07/22/1991   50  60

Agora, suponha que tudo que eu quero seja: uma contagem dos próximos seis meses e qualquer x y (últimos dois dígitos) que, quando adicionados, seriam menores que 51 do xy atual adicionado da linha atual.

Para um pequeno número de entradas, eu poderia simplesmente criar outra coluna para o primeiro calcular e fazer uma contagem. O que eu não tenho certeza é como configurar isso leva apenas os próximos seis meses e, em seguida, só executa um countif sobre o cálculo xy em uma única célula. Parece-me, eu quero um resultado de matriz das datas são perto o suficiente e, em seguida, fazer uma distância calc em cada membro da matriz e, em seguida, contar todos aqueles que surgem positivo. Mas implementá-lo no Excel é um desafio.

Alguma sugestão ou outras indicações?

obrigado, -d

    

Answer 1

Você pode usar SUMPRODUCT :

• conta clientes nos próximos 6 meses:
  =SUMPRODUCT(([date]-[@date]<=183)*([date]-[@date]>0))
• conte os clientes a menos de 20 unidades usando uma fórmula muito simples (como em uma superfície simples):
  =SUMPRODUCT((SQRT(([@[lat.]]-[lat.])^2+([@[long.]]-[long.])^2)<20)*1)
• combinando os dois critérios em uma fórmula:
  =SUMPRODUCT(([date]-[@date]<=183)*([date]-[@date]>0)*(SQRT(([@[lat.]]-[lat.])^2+([@[long.]]-[long.])^2)<20))