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!