formula with changing cell ranges

I need a flexible way that I can do simple calculations (sums, averages,
etc.) over a range of cells that changes throughout the file. I have numerous
datasets like:
X Y Z
1 1 10
1 2 5
1 3 20
2 1 3
2 2 5
2 3 55
2 4 4
2 5 0
3 1 6
4 1 5
4 2 28
4 3 24
4 4 4
4 5 62
4 6 6

What I'd like is a way for Excel to calculate, for instance, the average of
Z for the three X's with 1, the five X's with 2, the one X with 1, and the
six X's with 4. I'd like the formula to be something I could have in each
cell in a neighboring column (or several columns for multiple steps)-- such
that it gives me the average only once per set (X=1, X=2, etc.) and all other
cells remain empty. That way I can then re-sort the columns and end up with a
single averaged value for each X like:
X Avg
1 11.7
2 13.4
3 6
4 21.5

Thanks!

Grant -
This will work, array formula entered Ctrl+Shift+Enter:

=AVERAGE(IF(X1:X100=1,Z1:Z100,""))

Change the =1 to whatever number you wish to get the average of.

--
Regards,
db


"Grant" wrote:

> I need a flexible way that I can do simple calculations (sums, averages,
> etc.) over a range of cells that changes throughout the file. I have numerous
> datasets like:
> X Y Z
> 1 1 10
> 1 2 5
> 1 3 20
> 2 1 3
> 2 2 5
> 2 3 55
> 2 4 4
> 2 5 0
> 3 1 6
> 4 1 5
> 4 2 28
> 4 3 24
> 4 4 4
> 4 5 62
> 4 6 6
>
> What I'd like is a way for Excel to calculate, for instance, the average of
> Z for the three X's with 1, the five X's with 2, the one X with 1, and the
> six X's with 4. I'd like the formula to be something I could have in each
> cell in a neighboring column (or several columns for multiple steps)-- such
> that it gives me the average only once per set (X=1, X=2, etc.) and all other
> cells remain empty. That way I can then re-sort the columns and end up with a
> single averaged value for each X like:
> X Avg
> 1 11.7
> 2 13.4
> 3 6
> 4 21.5
>
> Thanks!