Grouping and averaging

**BUSJEDWA** · 12-02-2008, 11:51 AM

I hope some clever soul can help ....

The organisation I wish to report on has many cost centres which each contain many people. These people are on many different grades, and each are on differing salaries (even those in the same grade).

One “reporting group” has many cost centres.

There are several reporting groups.

I need to report on average salary per grade / per reporting group.

I have attached some dummy data. The “rep group” tab displays the reporting groups and the cost centre mappings (ie reporting group England contains cost centres 1, 2, 3, 4, 5, 6 and 7). Note in reality the cost centres are not this simple, they are 6 digits and varying ranges.

In the “salaries” sheet each individual is listed along with their cost centre (in column c) and their grade (column d). Their salary is shown in E.

I hope this is clear, thanks for looking!!

**NBVC** · 12-02-2008, 12:12 PM

Perhaps re-attach document with showing some expected outcome.

**BUSJEDWA** · 12-02-2008, 12:22 PM

"outcome" tab shows format for desired outcome, column c would display the average salary in the category. Many thanks!!

**NBVC** · 12-02-2008, 12:40 PM

Try in C2 of Outcome:

Please Login or Register  to view this content.

Which must be confirmed with CTRL+SHIFT+ENTER not just ENTER...then copied down.

**NBVC** · 12-02-2008, 12:47 PM

Note:

I revised the Offset() reference cell in formula above... this should give now correct answer

**BUSJEDWA** · 12-02-2008, 12:53 PM

thanks, but it's not quite right, i'm taking a look at it now (if you drag it down on my example it doesn't work)

**NBVC** · 12-02-2008, 01:08 PM

See attached...

Is it wrong? If so, why?

**BUSJEDWA** · 12-02-2008, 01:13 PM

sorry, it;'s still no right for some reason - if i drag down the average salary for grades in England are all the same, which cannot be the case. Also, scotland and wales show 0