If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
circular reference - iteration
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? -- rocknroll |
#2
|
|||
|
|||
circular reference - iteration
rock wrote:
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? |
#3
|
|||
|
|||
circular reference - iteration
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" wrote: rock wrote: 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? |
#4
|
|||
|
|||
circular reference - iteration
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 wrote: 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" wrote: Maybe you could share the formula with the group? rock wrote: 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? |
#5
|
|||
|
|||
circular reference - iteration
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(M40,M4/L4,0) =IF(W4=1,L4-V4,0) =IF(O40,O4/L4*M4*$D4/2,0) =IF(O40,O4*(M4/L4),0) =IF(L40,$D4*((L4-O4)/L4)*M4,0) =IF(L40,M4+((O4/L4)*(M4*$D4)/2),0) =IF(L40,SUM(S4:S24),0) =IF(L40,$E$10-T4,0) =IF(U40,(((U4*-1)*L4)/M4),0) =IF(AND(L40,V4L4),1,0) END -- rocknroll "Glenn" wrote: rock wrote: 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? |
#6
|
|||
|
|||
circular reference - iteration
file saved at www.savefile .com can be reached at this link.
http://www.savefile.com/files/2082911 -- rocknroll "Glenn" wrote: 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 wrote: 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" wrote: Maybe you could share the formula with the group? rock wrote: 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? |
Thread Tools | |
Display Modes | |
|
|