http://chandoo.org/forums/topic/arrays-average-corresponding-values-for-each-occurance Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Sat, 18 May 2013 10:11:54 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/arrays-average-corresponding-values-for-each-occurance#post-1094 Mon, 01 Mar 2010 06:23:21 +0000 Hui 1094@http://chandoo.org/forums/ <p>Katish</p> <p>I wrote a simple User Defined Function (UDF) which will do your job for you</p> <p>You need to paste the following code into a Code Module<br /> Alt F11 and click on a Module without the ='s<br /> ======</p> <p>Function Summ(Arr_Lst As Range, Arr_Tbl As Range)<br /> Summ = 0<br /> For x = LBound(Arr_Lst()) To UBound(Arr_Lst())<br /> For y = LBound(Arr_Tbl()) To UBound(Arr_Tbl())<br /> If Arr_Lst(x) = Arr_Tbl(y, 1) Then Summ = Summ + Arr_Tbl(y, 2)<br /> Next<br /> Next<br /> End Function</p> <p>=====</p> <p>To use the code and assuming the values are in<br /> A1:A5 - a b c a c<br /> D1:D3 - a b c<br /> E1:E3 - 1 5 3</p> <p>To get the Sum</p> <p>Type in =Summ(A1:A5,D1:E3)</p> <p>To get the average =Summ(A1:A5,D1:E3)/Counta(A1:A5) </p> http://chandoo.org/forums/topic/arrays-average-corresponding-values-for-each-occurance#post-1084 Fri, 26 Feb 2010 19:08:29 +0000 katish 1084@http://chandoo.org/forums/ <p>The post came out unligned..<br /> A1:A5 - a b c a c<br /> D1:D3 - a b c<br /> E1:E3 - 1 5 3</p> <p>This means: a = 1, b = 5, c = 3</p> <p>So I want the resulting array that would be: 1 5 3 1 3 (corresponding values)<br /> Then I can average these numbers. Is this clear at all? I could upload the actual spreadsheet somewhere... </p> http://chandoo.org/forums/topic/arrays-average-corresponding-values-for-each-occurance#post-1081 Fri, 26 Feb 2010 14:24:41 +0000 Hui 1081@http://chandoo.org/forums/ <p>Katish</p> <p>From your example what should be in E1, E2 and E3 ?<br /> How are those values related to D1:D3 and A1:A5 ? </p> http://chandoo.org/forums/topic/arrays-average-corresponding-values-for-each-occurance#post-1080 Fri, 26 Feb 2010 13:10:57 +0000 katish 1080@http://chandoo.org/forums/ <p>Hui, thanks for your thought, but I'm not sure how to use the COUNTIF result to retrieve the values in column E? </p> <p>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. </p> http://chandoo.org/forums/topic/arrays-average-corresponding-values-for-each-occurance#post-1078 Fri, 26 Feb 2010 06:19:49 +0000 Hui 1078@http://chandoo.org/forums/ <p>Katish<br /> Do you want to count the values in Column D when they are in column A<br /> then in E1 put</p> <p>=COUNTIF($A$1:$A$5,D1) will count them<br /> and copy down </p> http://chandoo.org/forums/topic/arrays-average-corresponding-values-for-each-occurance#post-1075 Fri, 26 Feb 2010 01:58:40 +0000 katish 1075@http://chandoo.org/forums/ <p>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!</p> <p>Suppose this situation:<br /> <pre><code>A B C D E 1 a a 1 2 b b 5 3 c c 3 4 a 5 c</code></pre> <p>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):<br /> =AVERAGE(INDIRECT("E"&amp;MATCH(A1:A5;D1:D3;0)))</p> <p>But this doesn't work. It makes no sense to me, because evaluating with F9 shows INDIRECT being parsed right.</p> <p>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..</p> <p>So, PLEASE, ANY help will be greatly appreciated!</p> <p>Much love from Brazil! :) </p>