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!
Bookmarks