How to Calculate Average Salary by Job Title in Each City Using Excel

Whether you're an HR analyst, a business owner, or a researcher, you might need to analyze salary data based on job titles within specific cities. This article will guide you through two methods to calculate the average salary for each job title in each city using Microsoft Excel:

Organizing Your Data

Ensure your data is structured in three columns: City, Job Title, and Salary. For example:

City Job Title Salary New York Software Engineer 100000 New York Data Scientist 120000 Los Angeles Software Engineer 110000 New York Software Engineer 90000

Method 1: Using AVERAGEIFS

Excel's AVERAGEIFS function is a powerful tool for calculating averages based on multiple criteria. Follow these steps:

Step 1: Organize Your Data

Ensure your data is in three columns: City, Job Title, and Salary.

Step 2: Create a List of Unique Cities and Job Titles

You can use the UNIQUE function in Excel 365 or manually create a list. For example:

Unique Cities: New York, Los Angeles

Unique Job Titles: Software Engineer, Data Scientist

Step 3: Use the AVERAGEIFS Function

Suppose your unique list of cities is in column E and job titles in column F. In cell G2, you can enter:

AVERAGEIFS(C:C, A:A, E2, B:B, F2)

- C:C is the Salary column.

- A:A is the City column.

- B:B is the Job Title column.

- E2 is the cell with the city you are analyzing.

- F2 is the cell with the job title you are analyzing.

Step 4: Drag the Formula Down

Drag the fill handle down to calculate averages for all combinations of cities and job titles.

Method 2: Using a Pivot Table

Pivot Tables provide a more dynamic and interactive way to analyze your data. Follow these steps:

Step 1: Select Your Data

Highlight your data range including headers.

Step 2: Insert a Pivot Table

Go to the Insert tab. Click on PivotTable. Choose to place the Pivot Table in a new worksheet or the existing one.

Step 3: Set Up the Pivot Table

Drag City to the Rows area. Drag Job Title to the Rows area below City. Drag Salary to the Values area. Ensure that the Value Field Settings for Salary are set to Average. You can change this by clicking on the dropdown in the Values area and selecting Analyze the Results.

The Pivot Table will display the average salary for each job title in each city.

Summary

AVERAGEIFS is great for a more manual approach where you have specific cells for cities and job titles.

Pivot Tables are more dynamic and allow for easy adjustments and a clearer summary of your data.

Choose the method that best fits your needs! If you have any further questions, feel free to ask.