Link to home
Start Free TrialLog in
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

SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of Pau Lo
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.