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.

Ticker	Fund Name	Growth	Income
IVV	iShares S&P 500 Index Fund	80%	50%
BSV	Vanguard Short-Term Bond ETF	0%	10%
IBB	iShares Nasdaq Biotechnology Index	0%	0%
BND	Vanguard Total Bond Market	10%	20%
VNQ	Vanguard REIT ETF	10%	20%
		100%	100%

<colgroup><col width="91" style="width:68pt"> <col width="179" style="width:134pt"> <col width="64" style="width:48pt" span="2"> </colgroup><tbody>
</tbody>

 

[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.

	Jane Doe
Portfolio Size:	$150,000	Target	Actual %	Actual $
Growth	80%	$120,000	81%	$96,757
Income	20%	$30,000	74%	$22,243
	100%	$150,000	79%	$119,000
Ticker	Target %	Target $	Actual
IVV	74%	$111,000	$98,000
BSV	2%	$3,000	$3,000
IBB	0%	$0	$5,000
BND	12%	$18,000	$0
VNQ	12%	$18,000	$18,000
CASH			$26,000
TOTAL:	100%	$150,000	$150,000
		target $=actual $:	OK

<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>
</tbody>

 

[captainxcel]

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