Advanced Analytics - Dynamic Array Formulas in Excel 365 (HR Case Study) - Rahim Zulfiqar Ali

[@ExcelBasement]

Q1. Filter all Employees who are Reporting to Manager Rahim? =VSTACK(HR_Data[#Headers],FILTER(HR_Data,HR_Data[Manager]=I4)) Q2. Filter all Employees who are Reporting to Manager Rahim having Salary >= 80,000 & Male? =VSTACK(HR_Data[#Headers],FILTER(HR_Data,(HR_Data[Manager]=I49)*(HR_Data[Salary]>=80000)*(HR_Data[Gender]="Male"))) Q.3 Salaries of People Reporting to Rahim ? Descriptive Analytics =FILTER(HR_Data[Salary],HR_Data[Manager]=B74) Q4. Filter all Employees who are Reporting to Manager Rahim (ID, Names, Gender, Age, Salary)? =VSTACK(CHOOSECOLS(HR_Data[#Headers],{1,2,4,5,7}),FILTER(CHOOSECOLS(HR_Data,{1,2,4,5,7}),HR_Data[Manager]="Rahim")) Q.5 Summarize Department wise based on Age Filter? =SORT(UNIQUE(FILTER(HR_Data[Department],HR_Data[Age]>=C163))) =COUNTIFS(HR_Data[Department],E166#,HR_Data[Age],">="&C163) =SORT(E166#:F166#,2,-1) Q.6 Find Names and No of Employees Reporting to Manager? =TEXTJOIN(", ",TRUE,FILTER(HR_Data[Full Name],HR_Data[Manager]=B188)) =COUNTA(--TEXTSPLIT(C188,", "))