Problem getting correct conditional Weighted Avg. using nested and/or conditions in SUMPRODUCT

C

CJ

I want to get a weighted average for rows that have "X" in column M OR
rows that have both a "Y" in column A and a value greater than 95 in
column D. Column E contains values in the range of 4.625 to 15.75 and
column B contains values in the range of 10,000 to 612,500.

=SUMPRODUCT(((M2:M3693 = "X")+((A2:A3693 =
"Y")*(D2:D3693>95)))*(E2:E3693),B2:B3693)

The results are close to the expected value but definitely off.

8.453 - result of sumproduct formula w/no conditions; sorted rows that
met the criteria above and referenced just those rows in the formula

8.333 - result of sumproduct formula w/conditions as shown above.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding the weighted average of text that have been given values 1
OR in SUMPRODUCT formula 4
Sum Totals/Different Conditions 3
Sumproduct, but errors in column 6
Conditional formatting using nested "Or(and ..." 7
Nested IF 2
Gather Data from Multiple Sheets 1
weighted avg. of groups in table 2

Top