Aggregate data (using DAverage, ...)

[yves78]

Hi,

I have a "database" on which I would like to compute aggregate data. Here's a small example (| represent column separators).

Name | Function | Salary
John | Sales | 30,000
John | R&D | 40,000
Mike | Sales | 15,000
Bill | R&D | 20,000

For instance, for every function, the average salary. I first thought about writing a "criteria" area, with A1:C5 being the database area and G2:H2 corresponding to the "Function" column.

Name Function Salary Avg Salary
| Sales | | =DAverage($A$1:$C$5,"Salary",$G$2:H2)
| R&D | |

The formula works fine but, when I copy it downwards, it doesn't work anymore, because both "Sales" and "R&D" entries are considered as belonging to the criterion. Hence, it computes the average salary of both functions.

My idea to work around this would be to specify the criteria as a "split" cell area (you know, like when you select cells using CTRL+click), but I don't know how to specify such areas in formulae.

I apologize for my bad English and especially for using inappropriate terms, but I am French speaking and I completely ignore the proper translation of many Excel French terms ,

Thank you for your help,
yves78.

[yves78]

Reply to my own post: pivot tables solve exactly this problem.

yves78