Forum Discussion
is it possible to calculate a weighted average (SUMPRODUCT) in a way that it ignores 0 and #DIV/0!?
Hello fellow Excel users,
I need to calculate a weighted average (SUMPRODUCT) but since one of the criteria has 0 value it shows the error #DIV/0! - the formula is not working. Is it possible to make the formula ignore both the 0 and #DIV/0! in the calculation?
| Test | |||
| (C) Quantity | (D) Risk | (E) Reward | |
| Criteria 1 | 79 | 0,27% | 15,82% |
| Criteria 2 | 11 | 2,51% | 14,75% |
| Criteria 3 | 0 | #DIV/0! | #DIV/0! |
| Weighted Average | #DIV/0! | #DIV/0! | |
| Correct result | 0,54828% | 15,68851% |
Weighted Average (risk - column) has the following formula =SUMPRODUCT(C4:C6;D4:D6)/SUM(C4:C6)
I tried the two following formulas based on the research I did on the internet, but it also didn't work out:
=SUMPRODUCT(--(C4:C6>"0");--(D4:D6<>"#DIV/0!")/SOMASE(C4:C6;C4:C6>"0";C4:C6))
=SUMPRODUCT(--(C4:C6>"0");--(D4:D6<>"#DIV/0!")/SUM(C4:C6))
How can we fix it? Thanks for taking your time helping me.
1 Reply
That could be
=SUMPRODUCT( IFERROR(C4:C6, 0 ), IFERROR(D4:D6, 0 ) ) / SUM(C4:C6)Use as array formula (Ctrl+Shift+Enter) if you are not on Excel 365 or 2021
