I used formulas that helped sort the data and give more information. The formulas were mostly conditional, I mainly only wanted data about a specific department. I created two formulas, MAXIF and MINIF. These work like the main MAX and MIN functions but I am able to give a condition, in this case the department name. These formulas are hard coded so they can only be used with this spreadsheet right now.
List of Formulas used:
- =UNIQUE, to make the department list
- =SORT, to make sure the departments are in alphabetical order
- =SUMIF, for summing the total for each department
- =SUM, for summing the total how much the city spends in salaries overall
- =AVERAGEIF, for finding the average salary in each department
- =AVERAGE, for finding the average salary in the city
- =MAXIF, to find the top salary in each department
- =MAX, to find the top salary in the city
- =MINIF, to find the lowest salary in each department
- =MIN, to find the lowest salary in the city
- =COUNTIF, to find the number of employees in each department
- =COUNT, to find the total number of employees
- =COUNTUNIQUE, to find the number of job titles in each department
- =FILTER, to filter the content so it only gets the specific department
Sorting and calculating data by department helps someone maybe in Fiance get an overall view at what the city spends on its workers. It can also see which departments are spending the most money or which ones use the least. It also lets someone see how many employees are each department.
Vicky Pearce's IT Amber Site 