Cell Iterations???

[jkresch]

Could someone please explain cell iterations to me in a dumbed down version? Lol. I fixed a spreadsheet by using it today on a circular reference, but I don't know how it fixed the problem. Does anyone know what this is or how it works? Any info offered would be much appreciated. Thank you so much. Jeff

[Mallycat]

Normally if you have circular references, you have errors in your spreadsheet. If you are a novice, then I can say with some confidence that you have errors. However you may want to set up a sheet that deliberately has a circular reference. This is when you use iterations. If you do this, you will want to limit the number of times excel recalculates before stopping. Because technically a circular reference will iterate indefinitely.

eg. say you are over weight and want to lose some weight. You can create a formula to work our how much you will weight based on your maintenance calories, the number of calories you eat each day (assume constant exercise) and the number of days you maintain the diet. But of course your maintenance calories reduce each day as your weight reduces, so the formula iterates on itself. Weight is a function of maintenance calories and actual consumed calories, and maintenance calories is a function of weight. So the input in the formulas need to point to each other to work, and then the number of times it iterates would be the number of days on your diet. In this example, you would set the iteration to be the number of days you were going to diet, and that would tell you how much you would weigh in the end. If you did a recalculation, it would give you the answer x days into the future again. In this case, sooner or later the weight would level out because the consumed calories would equal the maintenance calories.

I consider myself a power Excel user and I have never need to use iterations

Matt

[DonkeyOte]

Matt has provided a nice synopsis... just as a very basic illustration let's say in A1 you enter a value and B1 is hold the balance such that as you change A1, B1 increases from it's present value to it's present value + A1

B1: =B1+A1

This is a circular reference as B1 is referring to itself and as Matt points out this is very rarely needed (and is Volatile - ie every time XL Calcs the Iteration is performed)... you do come across Iteration in Finance environment as in some cases iteration is used to say calculate a Cash Balance on a Balance Sheet (with Interest).


Iteration can in some instances be implemented as an alternative to VBA ... useful say for those running XL2008 on the Mac where there is no VBA support... suppose I want to enter a fixed datetime stamp in B1 when A1 has a value entered into it for the first time:

B1: =IF(AND(B1=0,A1<>""),NOW(),B1)

With iteration on, when I enter a value for the first time into A1 the current date & time is entered into B1 and will persist thereafter regardless of whether I change A1 again... it never refreshes... traditionally a VBA approach would be used for this but in some cases as outlined VBA is not viable so this can be a handy workaround.

However to reiterate Matt's point - you should generally be avoiding Circular References...

[martindwilson]

i disagree
running totals and time stamps are valid reasons for using iteration.
It's in the toolbox so why not use it?
so what if it slows down a sheet a bit.
most people don't even realize it's a problem but when the do ,then is the time to seek alternatives .
there are efficient and inefficient formulas but it doesn't really matter if it works for you.
its a bit like driving a car
you know how to do it
but you don't need to know combustion engine theory to do it.
Slow down. You will use 25% more fuel driving at 85mph than at the national speed limit of 70mph.
is good advice but boring if in a hurry
same as vba if you dont know it ,by the time youve found an example and found out what to do with it then made sure that people who recieve your file have macros enabled too you have wasted loads of time.
it all really depends on who is going to use any particular work book

[jkresch]

So correct me if I'm wrong here, but an iteration is a cell's (or worksheet's) ability to reference itself to recalculate itself until it makes sense, or to brings itself within a specific variance?

Just for some background information, the sheet I'm talking about is used to calculate revenue based on Average handle times. The circular reference popped up when we attempted to use a different cell's value for the goal handle time. There is no obvious circular reference, but I'm sure on one of the 15 tabs, info is put into this cell, and then the cell is using the same information that is entered into it thus creating the circular reference. I tried using the circular reference toolbar to track down the issue, and the arrows referenced another page (I know that by the little symbol that popped up), but I don't know how to click on that symbol to find which page it is. So if anyone could help with that please let me know.

Also, the timestamp theory made a lot more sense to me. I didn't really understand the weight one. Once a can see a formula, it's alot easier for me to process. Thank you all so much for your input though. If anyone has more info on how to track the reference please let me know. Thank you so much for your time and expertise. Jeff

[mewingkitty]

I think what he meant with the weight example is, lets say: (these are completely fictitious units of energy)
5% of body weight is used in energy units because of the force needed to move the body throughout the day, and a training regime.
5 units of energy are taken in each day.

at a weight of 150, you'd use 7.5, and therefore be losing weight, because your intake is only 5.

perform that calculation (as well as one figuring the weight lost) few times over (iterate) and you get to a weight of 125, using 6.25 per day, still losing weight.

at 100, you'll use 5 and take in 5. The iteration has completed, it will no longer change the value to recalculate it again. OR (and this is the reason they're included as an option) you could limit the iterations, in order to see where you'd be at after a certain number of days.

I suppose technically you'd be at 5.00...1, but for the sake of the example, we'll call it 5.

Glad I bumped into this thread, I personally never saw the use for them because once I started hitting walls with formulas I dove into VBA, but this is going to be a useful thread for anyone asking about this in the future.