Average Function help

[Cheech]

I'm curious to whether or not this particular action that I'm trying
make happen is possible w/o the aide of macros.

The problem is that I'm trying to take the average of a particular
equation within an equation.

For example:
.. A B

1 X 2
2 Y 6
3 Z 8
4 X 1
5 Y 3
6 Z 7
7 X 3
8 Y 5
9 Z 9

I need to calculate in multiple large xls' the average of x1-n/y1-n.
not x1+x2+xn / y1+y2+yn. I need AVERAGE(x1/y1,x2/y2,xn/yn,...). I
can't seem to figure out how to write this as an excel equation.
Any and all help is greatly appreciated...Thanks.

Josh

 

[Max]

Perhaps one way ..

Experiment with this set-up

Suppose the data below
is in Sheet1 cols A and B, from row1 down:

X 2
Y 6
Z 8
X 1
Y 3
Z 7
X 3
Y 5
Z 9

Put in C1: =IF(A1="","",COUNTIF($A$1:A1,A1))
Copy down to say C100 to cover max data range expected

Col C will number/itemize the occurrences of the X's, Y's , Z's in col A,
i.e. help identify: X1, Y1, X2, Y2, etc

In Sheet2
-------------
List across in B1:C1 : X, Y

Put in A2: =ROWS($A$1:A1)

Put in B2:

=IF(ISNA(MATCH($A2&B$1,Sheet1!$C$1:$C$100&Sheet1!$A$1:$A$100,0)),"",INDEX(Sh
eet1!$B$1:$B$100,MATCH($A2&B$1,Sheet1!$C$1:$C$100&Sheet1!$A$1:$A$100,0)))

Array-enter the formula in B2, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy B2 across to C2

Put in D2: =IF(ISERROR(B2/C2),"",B2/C2)

Now select A22, fill down to D100

Cols B and C extracts the X1, Y1, X2, Y2, etc from Sheet1
Col D computes: X1/Y1, X2/Y2, ... etc

Now put in D1: =AVERAGE(OFFSET($D$2,,,COUNTA(D2100)))

D1 should return what you're after,
i.e. : ... AVERAGE(x1/y1,x2/y2,xn/yn,...)

Adapt to suit

 

[Max]

An add-on clarification to the line:

Cols B and C extracts the X1, Y1, X2, Y2, etc from Sheet1

The values of X1, Y1, X2, Y2, etc are extracted from *col B* in Sheet1

 

[Cheech]

Beautiful...a bit complex, but very efficient.

Thanks,

Josh

 

[Cheech]

Beautiful...a bit complex, but very efficient.

Thanks,

Josh

 

[Max]

You're welcome !
Thanks for the feedback ..