circular reference - iteration

[rock]

I am building a forestry growth and harvest model that forecasts out twenty
years ahead. I need to be able to change assumptions such as beginning
volume per acre and annual harvest volume. I have a circular reference and
have turned on iteration to allow recalculation. However, I get "Divides by
Zero Error" (#DIV/0!) beginning a year ten when I change an assumption.
Changing the number of maximum iterations and maximum change does not appear
to ahve any impact. If I recopy the formula over the error message, #DIV/0!,
when the cells recalculate with a value. What options do I have to change
assumptions without errors?

 

[Glenn]

I am building a forestry growth and harvest model that forecasts out twenty
years ahead. I need to be able to change assumptions such as beginning
volume per acre and annual harvest volume. I have a circular reference and
have turned on iteration to allow recalculation. However, I get "Divides by
Zero Error" (#DIV/0!) beginning a year ten when I change an assumption.
Changing the number of maximum iterations and maximum change does not appear
to ahve any impact. If I recopy the formula over the error message, #DIV/0!,
when the cells recalculate with a value. What options do I have to change
assumptions without errors?

Maybe you could share the formula with the group?

 

[rock]

Is there a way to share a stripped down version of the file?

I'll try to get the formulas copied to this message forum

rocknroll

 

[Glenn]

You could post your workbook to a site like www.savefile.com, however there is
no guarantee that anyone will actually look at it. If you do post a workbook,
keep it as small as possible.

 

[rock]

Formulas copied below. To restore to a blank worksheet, copy from BEGIN B:1
to END and paste to cell B:1 in a blank worksheet.


BEGIN B:1 HEADER Growth Rate %/yr. BEG INV Acres BEG INV MMBF BEG INV
MBF/Ac. HARVEST Acres HARVEST Growth HARVEST MMBF GROWTH MMBF BEG INV
Acres BEG INV MMBF BEG INV MBF/Ac. HARVEST Acres HARVEST Growth HARVEST
MMBF GROWTH MMBF AC MMBF TOT HVST TO BAL BAL AC INDICATE
COLUMN D E F G H I J K L M N O P Q R S T U V W
YEAR
1 0.0407 3.55999994277954 131.089760124418 36.8229669189453 0 0 0 5.33535323706382
YEAR
2 0.0407 =E3-H3 =F3+K3-(H3*G3) =IF(M4>0,M4/L4,0) =IF(W4=1,L4-V4,0) =IF(O4>0,O4/L4*M4*$D4/2,0) =IF(O4>0,O4*(M4/L4),0) =IF(L4>0,$D4*((L4-O4)/L4)*M4,0) =IF(L4>0,M4+((O4/L4)*(M4*$D4)/2),0) =IF(L4>0,SUM(S4:S24),0) =IF(L4>0,$E$10-T4,0) =IF(U4<0,(((U4*-1)*L4)/M4),0) =IF(AND(L4>0,V4<L4),1,0) END

 

[rock]

file saved at www.savefile .com can be reached at this link.

http://www.savefile.com/files/2082911