Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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(U4<0,(((U4*-1)*L4)/M4),0) =IF(AND(L40,V4<L4),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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
circular reference | Excel Worksheet Functions | |||
Is this possible? (Circular reference?) | Excel Discussion (Misc queries) | |||
Circular reference help! | Excel Discussion (Misc queries) | |||
how to: circular reference | Excel Worksheet Functions | |||
how to: circular reference | Excel Worksheet Functions |