CODEWITHSUNDEEP
arraysexcelif-statementarray-formulasblank-line
WnGatRC456
WnGatRC456

Reputation: 337

Multi-condition array formula having issue ignoring blank cells

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

Upvotes: 1

Views: 158

Answers (2)

WnGatRC456
WnGatRC456

Reputation: 337

I like the accepted answer better since the array formula is shorter, but this also gives accurate results.

{==SUMPRODUCT(IF($A3:$A22<0.5,IF($A3:$A22>0.2,$B3:$B22,""),""),IF($A3:$A22<0.5,IF($A3:$A22>0.2,$C3:$C22,""),""))/SUM(IF($A3:$A22<0.5,IF($A3:$A22>0.2,$B3:$B22,""),""))}

Upvotes: 0

basic
basic

Reputation: 11968

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?

Upvotes: 1

Related Questions