Getting values using my iterative calculations

**ancog** · 07-24-2018, 03:35 PM

I have attached an excel file as well as a word document with some formulas. The excel file I have attached is a condensed version of the original and in order to get back to the original, on Sheet1, you need to select A3 to CZ2001 and use the fill feature to fill down.

Next you need to copy the first array formula, that has a summation, from the word document into Sheet1!BW2001. Then you need to use the fill feature to fill right from BW2001 to CB2001, in sheet1. Next you need to copy the second array formula, that has a summation, from the word document into Sheet1!CC2001. After that you need to use the fill feature to fill right from CC2001 to CH2001, in sheet1. Then you need to copy the third array formula, that has a summation, from the word document into Sheet1!CI2001. Next you need to use the fill feature to fill right from CI2001 to CN2001. Also make sure you have Iterative Calculations enabled as in my original excel document I have iterative calculations enabled and the question I am about to ask involves iterative calculations.

I tried selecting BW2000 to CN2001 and using the fill feature to fill up but I got an error message in the form of #NUM! in all the cells involved in the group of iterative calculations when I did that, so I undid that as it's impossible to get ride of an error message in a group of iterative calculations without redoing the entire group of iterative calculations. I want to select from BW2 to CN2001 and use the fill feature to fill up but I'm not sure how to get actual values as opposed to an error message doing that. How do get values when I fill the formulas in columns BW to CN from row 2001 up to row 2, and get values from the formulas involving iterative calculations?

**MrShorty** · 07-27-2018, 11:35 PM

so I undid that as it's impossible to get ride of an error message in a group of iterative calculations without redoing the entire group of iterative calculations.

Without trying to be too contrary, but I don't find it impossible to get rid of such an error message, but you do need to put some thought into what the formula should do if/when it encounters an error. For serious circular reference/iterative calculation examples, I always include some kind of "reset" for the loop if it should encounter an error -- usually in the form of an IFERROR() function.

My main example thread, including examples of how I used the IFERROR() function to reset when an error is encountered: https://www.excelforum.com/tips-and-...ind-roots.html

See how I nested the main circular reference function inside of an IFERROR() function (step 2 of post #3) to reset the loop to 0 if it should encounter an error in this example: https://www.excelforum.com/excel-for...ate-value.html

I apologize for being too lazy to dig deep into those massive formulas you are using for this, so I cannot say where a "reset" IFERROR() could/should be incorporated. At this point, I would study your formulas and the circular logic you are using and identify which values/computations will be convenient for providing a reset point in the loop. Then identify what would be a good "default value" to introduce into the calculation at that point. Then use an IFERROR() (or other decision construct) to identify the error and introduce a good default value when an error is encountered.