Avatar of Pau Lo
Pau Lo
 asked on

min max and average stats based on salary data

Is there a way outside of pivot tables in excel to achieve the following.
I have 2 columns/fields worth of data (currently around 8000 records), column A is employee ID (a unique reference number for every employee on our payroll), and monthly wage, in  currency data format. Each employee ID will have between 2 and 12 rows in the data. It is not currently sorted on employee ID, but can easily be applied if that helps. What I want to do, is for all rows relating to an employee ID, produce some stats on their corresponding wage, to produce lowest value, highest value, and average value.
How would be best to go about this, possibly outside of Excel pivot tables if possible (never been comfortable with them!). I can import the data into MSACCESS as well and use an SQL to get the required results, it is currently in Excel. I don’t have a preference on Access or Excel, but knowing a method to produce this in either application, or both would be great as its some regular statistical analysis I need to do.

Results should ideally be returned in a single row for each:

Employee ID - Lowest Monthly Salary - Average Monthly Salary - Largest Monthly Salary

Microsoft OfficeMicrosoft ExcelMicrosoft Access

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
SOLUTION
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rob Henson

If you have a way of creating a list of the unique IDs in the list then a table of data would be easy to create with formulas:

MINIFS
MAXIFS
AVERAGEIFS

As a comparison for data integrity it may be worth including a COUNTIFS, for example if you have an ID that has multiple entries of the same value it will show MAX MIN and Average of the same value. If you have one entry, it will do the same.

Even if you don't like Pivot Table, this is exactly what they are designed for.
Pau Lo

ASKER
I can use the advanced filter and 'unique records only' to get a list of unique ID's on which to create a table, and copy the unique employee ID's on a separate worksheet, then remove the filter from the worksheet with the corresponding salary data.

Could you provide an example formula once that prep work has been done (unique ID's on separate sheet), to make use of the functions you have recommended as I've not used them previously.
ASKER CERTIFIED SOLUTION
Rob Henson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rob Henson

Just noticed I dragged the wrong field into the Pivot Table for the Average column. Easily changed if you change your mind on Pivot Table.

I didn't use to be comfortable with Pivot Tables either because I didn't understand them. I have learnt a lot about them and now use them quite often. Some advantages of Pivot Table that I can think of (sounds like I'm a Pivot Table salesman :-) ), particularly if linked to a Table rather than standard range:
- List of IDs will automatically update rather than having to do the Advanced Filter to get the list,
- Values are static until pivot is refreshed whereas formulas will recalculate with each change,
- If your data also has other fields, eg dates, you can apply a filter so that the Summary only shows the filtered data with a simple selection whereas adding an additional filter criteria to the formulas would be less simple.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy