FILTER, LET, TRANSPOSE and Other Excel Functions: Mode Calculation for each Quartile. EMT 1771

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/ETM1771-Ch03-ESA.xlsm
Learn how calculate the Mode in the First Quartile of Data. Then see a formula to calculate the Mode for each quartile of data. Learn about the functions: FILTER, QUARTILE.INC, MODE.MULT, TRANSPOSE and LET.
Learn how to use the LET function when your formula has repeating formula elements.
1. (00:00) Introduction
2. (00:22) QUARTILE.INC function to spill min, max and quartile 1, quartile 2 and quartile 3 data.
3. (01:04) Use FILTER function to filter to get just quartile 1 data.
4. (02:14) Use MODE.MULT to calculate the mode.
5. (02:43) Use LET function to define variables for repeated formula elements and make formula more efficient.
6. (04:25) Learn how to run an AND logical test inside the FILTER function to capture the specified quartile data based on an upper and lower limit.
7. (06:10) FILTER with AND Logical test inside MODE.MULT
8. (06:26) Use TRANSPOSE function to spill multiple modes horizontally
9. (06:59) Closing and Video Links

ExcelIsFunFri, January 14, 2022 4:17am