Avoiding a DIV/0 Error in a 3 range SUMPRODUCT

[captainxcel]

Hi everyone,
Trying to avoid a div/0 on a sumproduct that contains 3 ranges. The range doe_target below occasionally contains zero values.

=SUMPRODUCT((doe_actual/doe_target),growth_weights)

Thanks!

 

[CodeNinja]

You could use a simple array formula to get your desired result...
{=sum(IF(doe_target=0,0,doe_actual/doe_target*growth_weights))}

Note: this formula must be committed by using CTRL+SHIFT+ENTER you will see the curly braces around the formula if committed correctly.

Good luck,

CN.

 

[Roxxien]

Hello,

it will be useful to know what you want as an answer instead of div/0, i will write this formula as if you want to have "problem" as result.

=IFERROR(SUMPRODUCT((doe_actual/doe_target),growth_weights),"problem")

 

[captainxcel]

Thank you both. The array formula solved the issue, but I will also play around with the latter. What I was trying to accomplish was calculating the percentage invested a portfolio was while allowing for the portfolio to change such that the target for a particular security could be at zero as with "IBB" below.

[TABLE="width: 398"]
<colgroup><col width="91" style="width:68pt"> <col width="179" style="width:134pt"> <col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="class: xl21, width: 91"]Ticker[/TD]
[TD="class: xl21, width: 179"]Fund Name[/TD]
[TD="class: xl21, width: 64"]Growth[/TD]
[TD="class: xl21, width: 64"]Income[/TD]
[/TR]
[TR]
[TD="class: xl19"]IVV[/TD]
[TD="class: xl19"]iShares S&P 500 Index Fund[/TD]
[TD="class: xl23"]80%[/TD]
[TD="class: xl23"]50%[/TD]
[/TR]
[TR]
[TD="class: xl19"]BSV[/TD]
[TD="class: xl18"]Vanguard Short-Term Bond ETF[/TD]
[TD="class: xl23"]0%[/TD]
[TD="class: xl23"]10%[/TD]
[/TR]
[TR]
[TD="class: xl19"]IBB[/TD]
[TD="class: xl18"]iShares Nasdaq Biotechnology Index[/TD]
[TD="class: xl23"]0%[/TD]
[TD="class: xl23"]0%[/TD]
[/TR]
[TR]
[TD="class: xl19"]BND[/TD]
[TD="class: xl18"]Vanguard Total Bond Market[/TD]
[TD="class: xl23"]10%[/TD]
[TD="class: xl23"]20%[/TD]
[/TR]
[TR]
[TD="class: xl21"]VNQ[/TD]
[TD="class: xl22"]Vanguard REIT ETF[/TD]
[TD="class: xl24"]10%[/TD]
[TD="class: xl24"]20%[/TD]
[/TR]
[TR]
[TD="class: xl19"][/TD]
[TD="class: xl19"][/TD]
[TD="class: xl25"]100%[/TD]
[TD="class: xl25"]100%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Snakehips]

Or if the Div/0 element is to contribute 0 to the sum then....

=SUMPRODUCT(IFERROR((doe_actual/doe_target),0),growth_weights)

And confirm with Ctrl + Shift + Enter

Hope that helps.

 

[captainxcel]

Thanks Tony. The first suggestion from CodeNinja produced the results in blue, which I think work for me. I am trying to get a sense of the percent invested a client is. In the below paste, all yellow cells are input cells. In this particular case, for client 'Jane Doe' we have taken the target weight to IBB to zero and upped the target weight to IVV. The client is in the process of selling down IBB taking it's weight to "0" and purchasing IVV. Additionally, the client has yet to purchase any BND.

[TABLE="width: 562"]
<colgroup><col width="256" style="width:192pt"> <col width="68" style="width:51pt"> <col width="102" style="width:77pt"> <col width="68" style="width:51pt" span="2"> </colgroup><tbody>[TR]
[TD="class: xl19, width: 256"][/TD]
[TD="class: xl30, width: 68"]Jane Doe[/TD]
[TD="class: xl25, width: 102"][/TD]
[TD="class: xl25, width: 68"][/TD]
[TD="class: xl30, width: 68"][/TD]
[/TR]
[TR]
[TD="class: xl19"]Portfolio Size:[/TD]
[TD="class: xl31"]$150,000 [/TD]
[TD="class: xl20"]Target[/TD]
[TD="class: xl20"]Actual %[/TD]
[TD="class: xl51"]Actual $[/TD]
[/TR]
[TR]
[TD="class: xl19"]Growth[/TD]
[TD="class: xl40"]80%[/TD]
[TD="class: xl25"]$120,000[/TD]
[TD="class: xl118"]81%[/TD]
[TD="class: xl30"]$96,757[/TD]
[/TR]
[TR]
[TD="class: xl19"]Income[/TD]
[TD="class: xl31"]20%[/TD]
[TD="class: xl20"]$30,000[/TD]
[TD="class: xl119"]74%[/TD]
[TD="class: xl51"]$22,243[/TD]
[/TR]
[TR]
[TD="class: xl19"][/TD]
[TD="class: xl25"]100%[/TD]
[TD="class: xl25"]$150,000[/TD]
[TD="class: xl23"]79%[/TD]
[TD="class: xl30"]$119,000[/TD]
[/TR]
[TR]
[TD="class: xl19"][/TD]
[TD="class: xl19"][/TD]
[TD="class: xl19"][/TD]
[TD="class: xl19"][/TD]
[TD="class: xl32"][/TD]
[/TR]
[TR]
[TD="class: xl21"]Ticker[/TD]
[TD="class: xl20"]Target %[/TD]
[TD="class: xl20"]Target $[/TD]
[TD="class: xl20"]Actual[/TD]
[TD="class: xl30"][/TD]
[/TR]
[TR]
[TD="class: xl34"]IVV[/TD]
[TD="class: xl23"]74%[/TD]
[TD="class: xl32, align: right"]$111,000 [/TD]
[TD="class: xl36, align: right"]$98,000[/TD]
[TD="class: xl53"][/TD]
[/TR]
[TR]
[TD="class: xl34"]BSV[/TD]
[TD="class: xl23"]2%[/TD]
[TD="class: xl32, align: right"]$3,000 [/TD]
[TD="class: xl36, align: right"]$3,000[/TD]
[TD="class: xl53"][/TD]
[/TR]
[TR]
[TD="class: xl34"]IBB[/TD]
[TD="class: xl23"]0%[/TD]
[TD="class: xl32, align: right"]$0 [/TD]
[TD="class: xl36, align: right"]$5,000[/TD]
[TD="class: xl53"][/TD]
[/TR]
[TR]
[TD="class: xl34"]BND[/TD]
[TD="class: xl23"]12%[/TD]
[TD="class: xl32, align: right"]$18,000 [/TD]
[TD="class: xl36, align: right"]$0[/TD]
[TD="class: xl53"][/TD]
[/TR]
[TR]
[TD="class: xl34"]VNQ[/TD]
[TD="class: xl23"]12%[/TD]
[TD="class: xl32, align: right"]$18,000 [/TD]
[TD="class: xl36, align: right"]$18,000[/TD]
[TD="class: xl53"][/TD]
[/TR]
[TR]
[TD="class: xl35"]CASH[/TD]
[TD="class: xl39"][/TD]
[TD="class: xl39"][/TD]
[TD="class: xl37, align: right"]$26,000[/TD]
[TD="class: xl53"][/TD]
[/TR]
[TR]
[TD="class: xl34"]TOTAL:[/TD]
[TD="class: xl25"]100%[/TD]
[TD="class: xl19, align: right"]$150,000 [/TD]
[TD="class: xl19, align: right"]$150,000[/TD]
[TD="class: xl53"][/TD]
[/TR]
[TR]
[TD="class: xl19"][/TD]
[TD="class: xl19"][/TD]
[TD="class: xl19"]target $=actual $:[/TD]
[TD="class: xl123"]OK[/TD]
[TD="class: xl32"][/TD]
[/TR]
</tbody>[/TABLE]

 

[captainxcel]

No formattingL Blue cells are the ones under Actual % (84% and 74%)