Como obter a mediana com base nos títulos das colunas?

Question

Digamos que temos:

EPS    Sentiment    Revenue  Sentiment     Formula1?     Formula2?

10     3%            20       5%           F1a           F2a
9      6%            30       4%           F1b           F2b

Tenho mais de 600 colunas e esta é uma coluna simplificada. Tenho mais de 150 colunas de "Sentimento".

Para F1a e F1b, desejo obter o percentil de 73% nos dados de sentimento na mesma linha, ou seja, se o cabeçalho da coluna for "Sentimento", inclua os dados na fórmula F1 para obter o percentil.

Para F2a e F2b, desejo obter a mediana dos dados de sentimento na mesma linha, ou seja, se o cabeçalho da coluna for "Sentimento", inclua os dados na fórmula F1 para obter o percentil.

Como tenho 2.000 linhas, gostaria de copiar F1a e F2a para baixo até F1z e F2z e mais.

Que fórmulas devo usar para F1 e F2?

Answer 1

Esta não é uma resposta muito elegante, mas dará os resultados que foram solicitados relativamente à "coluna simplificada". Ele assume que os dados reais estão organizados de tal forma que as colunas de “sentimento” são espaçadas em intervalos regulares. Serão necessárias "colunas auxiliares", felizmente seus dados estão usando apenas 600 das mais de 1.600 colunas disponíveis.

Para a resposta da "coluna simplificada":

• 1) coloque a fórmula =INDEX($A2:$D2,(COLUMN()-7)*2+2)na célula G2
• 2) puxe a alça de preenchimento para copiar a fórmula para a célula H2
• 3) com ambas as células ainda selecionadas, clique duas vezes na alça de preenchimento para enviar a fórmula para baixo
• 4) coloque a fórmula =PERCENTILE.INC(G2:H2,0.73)na célula E2 (
  usei a versão .INC, porém pode ser necessário usar a versão .EXC
  dependendo de como os dados estão sendo interpretados)

• 5) clique duas vezes na alça de preenchimento para enviar a fórmula para baixo

• 6) coloque a fórmula =MEDIAN(G2:H2)na célula F2

• 7) clique duas vezes na alça de preenchimento para enviar a fórmula para baixo