I am trying to use an array formula to calculate a weighted average of a data in Excel set that contains blank cells but I'm confused why I am getting an error. I want to exclude two portion of the data depending the relative location to another array.
Here is my data
A B C
0.03
0.06
0.09
0.12
0.18 1.87E-06 0.236033
0.21 4.04E-05 0.249285
0.24 8.31E-05 0.268155
0.27 1.54E-04 0.268104
0.30 2.49E-04 0.274135
0.33 3.08E-04 0.301185
0.36 4.06E-04 0.311255
0.39 4.70E-04 0.329554
0.42 5.39E-04 0.332625
0.45 5.97E-04 0.339827
0.48 6.69E-04 0.347729
0.51 7.54E-04 0.359481
0.54 8.49E-04 0.371833
0.57 9.46E-04 0.379580
0.60 1.08E-03 0.386926
and here is my array formula.
{=SUMPRODUCT(IF(AND($A3:$A22<0.5,$A3:$A22>0.2),$B3:$B22,""),IF(AND($A3:$A22<0.5,$A3:$A22>0.2),$C3:$C22,""))/SUM(IF(AND($A3:$A22<0.5,$A3:$A22>0.2),$B3:$B22,""))}
I don't understand why this array formula is not working. If I replace the and in the if statement with one condition then this works fine, returning a value of 0.321592.
{=SUMPRODUCT(IF($A3:$A22<0.5,$B3:$B22,""),IF($A3:$A22<0.5,$C3:$C22,""))/SUM(IF($A3:$A22<0.5,$B3:$B22,""))}
I think it might be because I have some blank cells, but they are not in the column I am applying the logical statement to, so I'm confused. I'd like to get a solution so I can apply this if statement with and or some workaround in the array formula. Thanks
It's because AND can't return array, it returns single TRUE or FALSE only.
Try following array formula:
{=SUMPRODUCT(($A3:$A22<0.5)*($A3:$A22>0.2)*($B3:$B22),($A3:$A22<0.5)*($A3:$A22>0.2)*($C3:$C22))/SUM(($A3:$A22<0.5)*($A3:$A22>0.2)*($B3:$B22))}
Is result as needed?