'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate Attendance Percentage by Department in Excel
Question details
The user needs to calculate the attendance percentage for different departments based on TRUE/FALSE attendance records using Excel's dynamic array formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing employee attendance records to find the percentage of present employees per department.
- Observed behavior
- The user wants to group attendance data by department dynamically and output the attendance rate formatted as a percentage.
Ensure your dataset has a column for 'Department' and a column for 'Attendance' marked as TRUE (present) or FALSE (absent). Dynamic array formulas require a version of Excel that supports them, such as Microsoft 365, Excel 2021, or WPS Office.
Using UNIQUE and COUNTIFS Dynamic Arrays
Extract a unique list of departments and calculate the ratio of present employees to total employees in each department using spilling formulas.
The UNIQUE function automatically pulls a list of distinct departments from your data range.
By appending the '#' symbol to the cell reference of the spilled UNIQUE array, the COUNTIFS function can calculate attendance metrics for all departments simultaneously without needing to drag the formula down.
Select an empty cell (e.g., A25) and enter `=UNIQUE(B2:B21)`, assuming column B contains your department data. Press Enter to spill the unique department list.
In the adjacent cell (e.g., B25), enter the formula `=COUNTIFS(B2:B21,A25#,D2:D21,TRUE)/COUNTIFS(B2:B21,A25#)`. Here, D2:D21 is the attendance column containing TRUE or FALSE values. The `A25#` references the spilled array from the first step.
Select the newly populated decimal results in column B. Go to the Home tab and click the '%' icon in the Number group to format the values as percentages.
Calculate Dynamic Arrays Easily with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array formulas like UNIQUE and COUNTIFS, allowing you to build complex attendance reports with ease. It is a highly compatible and lightweight alternative for your daily data analysis tasks.
- 1. Open Your Data in WPS: Launch WPS Spreadsheet and open your attendance tracking workbook.
- 2. Apply Dynamic Arrays: Type `=UNIQUE(B2:B21)` to list the unique departments, and use the `=COUNTIFS(...)` formula to divide the present count by the total count.
- 3. Format the Results: Highlight the formula results and click the Percent Style button on the Home tab to beautifully display your attendance percentages.
Frequently Asked Questions
Why is my dynamic array formula returning a #SPILL! error?
A #SPILL! error occurs when the formula needs space to display the output array, but one or more cells in the required range are not empty. Clear the blocking cells to allow the formula to spill correctly.
Can I calculate attendance percentages without dynamic arrays?
Yes. You can use a PivotTable. Select your data range, go to Insert > PivotTable, place 'Department' in Rows, and use calculated fields or value field settings to show the percentage of TRUE values.
How do I count 'Present' or 'Absent' instead of TRUE/FALSE?
If your attendance column uses text strings like 'Present', simply modify the COUNTIFS condition from TRUE to 'Present', like this: `=COUNTIFS(B2:B21, A25#, D2:D21, "Present")`.
Does WPS Office support the UNIQUE function?
Yes, recent versions of WPS Office fully support dynamic array functions, including UNIQUE, SEQUENCE, and SORT, ensuring complete compatibility with modern Excel formulas.
