Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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 A2:D2, 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(D2:D100))) D1 should return what you're after, i.e. : ... AVERAGE(x1/y1,x2/y2,xn/yn,...) Adapt to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Cheech" wrote in message ups.com... 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 |
#3
![]() |
|||
|
|||
![]()
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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
![]() |
|||
|
|||
![]()
Beautiful...a bit complex, but very efficient.
Thanks, Josh |
#5
![]() |
|||
|
|||
![]()
Beautiful...a bit complex, but very efficient.
Thanks, Josh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Using The Average Function if a cell has NA | Excel Worksheet Functions | |||
How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
IF Statement with Average Function results in #Value! | Excel Discussion (Misc queries) | |||
Average function with If | Excel Worksheet Functions | |||
Using Average function when number is zero | Excel Worksheet Functions |