Forum Discussion
Using mode with several criteria
Hello everyone!
I'm currently treating some data before I send it to do some machine learning.
I have the following data:
| id | Ano | mês | data_inversa | dia_semana | horario | uf | br | km | municipio | causa_acidente | tipo_acidente | classificacao_acidente | fase_dia | sentido_via | condicao_metereologica | tipo_pista | tracado_via | uso_solo | ano | pessoas | mortos | feridos_leves | feridos_graves | ilesos | ignorados | feridos | veiculos |
And I created a new table to summarize this data for months instead of accidents, here are the columns:
BR | KM | Ano | Mês | Clima (Moda) | Tipo Pista | Acidentes | Veículos | Ilesos | Feridos Leves | Feridos Graves | Mortes | Causa (Moda) | Risco |
What I want to do is calculate the mode of condicao_metereologica for each combination of BR, KM, Ano and Mês.
But I can't wrap my head around on how I could do this. Any help is appreciated! Let me know if I made anything not as clear as it is in my head xD
1 Reply
You’re essentially trying to calculate a mode with multiple criteria, which Excel doesn’t offer as a single built-in function — but there are clean and reliable ways to do it in Excel 365.
Here is the simplest way without VBA and Office Script.
BEST PRACTICE (Excel 365): FILTER + MODE.MULT
Since you’re on Office 365, you already have all the tools you need.
Concept
For each row in your monthly summary table, you:
1. Filter the original accident table by:
- BR
- KM
- Ano
- Mês
2. Calculate the mode of condicao_metereologica from the filtered rows.
Example structure
Assume your raw data table is named tblAcidentes and has columns:
- BR, KM, Ano, mes, condicao_metereologica
And your summary table row contains:
- [@BR], [@KM], [@Ano], [@Mês]
Formula (most common case: one mode)
=LET(
dados, FILTER(
tblAcidentes[condicao_metereologica],
(tblAcidentes[BR]=[@BR]) *
(tblAcidentes[KM]=[@KM]) *
(tblAcidentes[Ano]=[@Ano]) *
(tblAcidentes[mes]=[@Mês])),
INDEX(MODE.MULT(dados),1))
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
