Array calculation with individually looked up values

[ziegeo]

Hello everybody,

I am new to this forum and after reading a lot about array formulas I seem to be stuck and hope that I can benefit from the forum's collective wisdom. By now I understand how to perform basic calculations with an array, but I am wondering if can create an array consisting of values that have been individually looked up in a different table.

I need to analyse survey results, in which participants evaluated each other on various topics on a scale from -3 to +3. However, the +3 extreme on that scale corresponds either to a positive or a negative attribute. If it does correspond to a negative attribute, I need to multiply the score with -1.

In my attached worksheet I have a data table and a lookup table. My helper column D checks if the attribute in column C belongs to the list of positive or negative attributes and results in either 1 or -1. Column E multiplies the original score in column C with the correction factor in column D. Finally, C15 calculates a conditional average from the corrected scores in column E.

My question is whether it is possible to avoid my helper column D and calculate the value in C15 directly from the original scores in column C. I guess I am looking for a "virtual array" that corresponds to my helper column D, but I am not sure if this is even possible.

Hoping that somebody will be able to help!

example3.xlsx

[JohnTopley]

try

=SUMPRODUCT(($A$3:$A$12=$B$15)*(ABS($C$3:$C$12)))/COUNTIF($A$3:A$12,B$15)

[ziegeo]

Hi JohnTropley,

thanks for your quick reply. In your solution you used the ABS() function to make all negative values positive. Unfortunately it is not that simple, as the scores can in fact be negative. I have updated my example files with some new values to make this more clear. I believe that all values need to be looked up individually. Any ideas how to do that without using the helper column?

[ziegeo]

Just to be clear: In case you believe that this is not possible, kindly let me know. If it is not possible to avoid using that helper column, I can stop looking for a solution.

[JohnTopley]

=SUMPRODUCT(($A$3:$A$12=$B$15)*($C$3:$C$12)*($D$3:$D$12))/COUNTIF($A$3:A$12,B$15)

Simply multiply C 8 D = helper column ???

[ziegeo]

Ok, I think I figured something out by myself. The following formula seems to be close to what I am looking for:
=SUMPRODUCT(--($A$3:$A$12=$B$15),IF($B$3:$B$12=INDEX($H$3:$H$7,MATCH($A$3:$A$12,$G$3:$G$7,0)),1,-1),$C$3:$C$12)/COUNTIF($A$3:$A$12,$B$15)

What I don't understand: If entered in a single cell, the formula returns a #VALUE error. However, if I enter it as an array across 10 rows, each row gives me the correct result.

Why is that? I thought that SUMPRODUCT would get me around using arrays.

Please find an updated example file attached. example4.xlsx

[ziegeo]

Thanks, but it is column D that I want to avoid. Meanwhile I came up with an approach that might work (see above), but something is still not quite right. Do you have any suggestion how to improve that formula?

[JohnTopley]

Enter it (SUMPRODUCT) with CTRL+SHIFT+ENTER (it is an array formula)

[ziegeo]

But it shouldn't. As far as I can see, my (SUMPRODUCT) multiplies three arrays of exactly the same size (1x10), which are all numerical. Why doesn't it return a single number?

[JohnTopley]

If you enter as I said you will get a single digit answer. The MATCH function is comparing ARRAYS (rather tan a single value against an array) so it becomes an array formula.

See attached.

[ziegeo]

Thanks for all your help so far, JohnTropley!

Unfortunately simply entering it with CTRL+SHIFT+ENTER does not seem to work correctly. If you select "Clarity" as a value in B15 in the sheet that you uploaded, you will see that C21 does not calculate the result correctly while C23:C32 do. Do have an idea why that is?

[JohnTopley]

My last attempt!!!

=SUMPRODUCT(--($A$3:$A$12=$B$15),(IF(ISNA(MATCH($B$3:$B$12,$H$3:$H$7,0)),-1,1)),$C$3:$C$12)/COUNTIF($A$3:$A$12,$B$15)

Entered with CtrlL+SHift+Enter

[ziegeo]

Thank you - this seems to work!

While this would run into problems, if any value from column I was listed in column H, I don't think this will happen in my example.

Also, I noticed that when defining a named range for =IF(ISNA(MATCH($B$3:$B$12,$H$3:$H$7,0)),-1,1), and using that range in the (SUMPRODUCT) formula, it can be entered correctly without Ctrl+Shift+Enter.

Thanks again for your help!

[JohnTopley]

Please mark as SOLVED