Scenario is, you have a long list of employee numbers (Col B for example) and against each employee number is a payment figure (Col C) and a Department (Col D).
What we want to do is count the number of employee's who have an average pay that falls into various brackets and lie within one of two departments (Dept1, Dept2)
e.g. How many people have an average pay between 7 and 10, who work in Dept1 - (repeat for Dept2)
All very easy so far ... but ... some of the Employees appear more than once in the list and we need to average their pay first, before we count.
What we really really don't want to do, is add in helper columns, additional sheets etc. etc.
We're hoping for a single formula, that can almost group the records by Employee to calc the average and then count based on that result.
I appreciate that it's weirdly specific not to have helper columns, or other sheets that calculate the average first, but it is an unfortunate requirement.
As an example, if Employe 123456 who works in Dept1 appears 3 times, with the pay of 8, 9 and 10 - the avg. pay for that Emp is 9 - so we would count them as being 1 Employee with a pay of 9, working in Dept1.
Hope that makes sense
What we want to do is count the number of employee's who have an average pay that falls into various brackets and lie within one of two departments (Dept1, Dept2)
e.g. How many people have an average pay between 7 and 10, who work in Dept1 - (repeat for Dept2)
All very easy so far ... but ... some of the Employees appear more than once in the list and we need to average their pay first, before we count.
What we really really don't want to do, is add in helper columns, additional sheets etc. etc.
We're hoping for a single formula, that can almost group the records by Employee to calc the average and then count based on that result.
I appreciate that it's weirdly specific not to have helper columns, or other sheets that calculate the average first, but it is an unfortunate requirement.
As an example, if Employe 123456 who works in Dept1 appears 3 times, with the pay of 8, 9 and 10 - the avg. pay for that Emp is 9 - so we would count them as being 1 Employee with a pay of 9, working in Dept1.
Hope that makes sense
