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