Arrays - average corresponding values for each occurance

[katish]

Hi everyone! I hope someone can shed some light, because it's driving me crazy here. Been working on this for over a week now!


Suppose this situation:

[pre]

A  B  C  D  E
1 a        a  1
2 b        b  5
3 c        c  3
4 a
5 c

[/pre]
What I need is: get the corresponding values in E for each value of A, and then average it (or sum, or whatever). I thought about (entered as an array formula):

=AVERAGE(INDIRECT("E"&MATCH(A1:A5;D1:D3;0)))


But this doesn't work. It makes no sense to me, because evaluating with F9 shows INDIRECT being parsed right.


Is there any other way to achieve this? I tried other lookup functions but everything I tried failed, except for CHOOSE. But CHOOSE doesn't seem like an elegant choice of function to achieve this... Plus, my search array already has 112 values, and the limit is 256..


So, PLEASE, ANY help will be greatly appreciated!


Much love from Brazil!

 

[Hui]

Katish

Do you want to count the values in Column D when they are in column A

then in E1 put


=COUNTIF($A$1:$A$5,D1) will count them

and copy down

 

[katish]

Hui, thanks for your thought, but I'm not sure how to use the COUNTIF result to retrieve the values in column E?


Also, I cannot use the 'copy down' part, the whole point is to have a formula in a single cell, otherwise I would have gone with VLOOKUP.

 

[Hui]

Katish


From your example what should be in E1, E2 and E3 ?

How are those values related to D1:D3 and A1:A5 ?

 

[katish]

The post came out unligned..

A1:A5 - a b c a c

D1:D3 - a b c

E1:E3 - 1 5 3


This means: a = 1, b = 5, c = 3


So I want the resulting array that would be: 1 5 3 1 3 (corresponding values)

Then I can average these numbers. Is this clear at all? I could upload the actual spreadsheet somewhere...

 

[Hui]

Katish


I wrote a simple User Defined Function (UDF) which will do your job for you


You need to paste the following code into a Code Module

Alt F11 and click on a Module without the ='s

======


Function Summ(Arr_Lst As Range, Arr_Tbl As Range)

Summ = 0

For x = LBound(Arr_Lst()) To UBound(Arr_Lst())

For y = LBound(Arr_Tbl()) To UBound(Arr_Tbl())

If Arr_Lst(x) = Arr_Tbl(y, 1) Then Summ = Summ + Arr_Tbl(y, 2)

Next

Next

End Function


=====


To use the code and assuming the values are in

A1:A5 - a b c a c

D1:D3 - a b c

E1:E3 - 1 5 3


To get the Sum


Type in =Summ(A1:A5,D1:E3)


To get the average =Summ(A1:A5,D1:E3)/Counta(A1:A5)