Excel iteration ranges?

[Aemornion]

Hello,

I am having a bit of a conundrum.

I need excel to calculate the number of capacitors in series and parallel to equal a certain capacitance, as well as be greater than some other criteria.

Right now i have some circular references between two simple equations. one telling me number in parallel,and one telling me number in series. Each are based off each other, and based off the value of the capacitors, and the desired over all capacitance.


Looks like this
Series=("individual capacitance")*("Parallel")/("Desired capacitance")
Parallel=("Desired Capacitance")*("Series")/("Individual Capacitance")



And here is my problem. It is too good at what it does. Yes, it runs threw the iterations and calculates exactly how many i need in series and parallel. But i don't need it to be that exact. as in an effort to match the total capacitance parameter, it requires way more capacitors than I can afford.

So here is my question, How would I go about telling excel, to do the iterations, but to stop doing them once the answer was within say +-10% of the actual value.
or to do all the iterations, but find the lowest combination of series and parallel that still sit the initial conditions within a the tolerence?

I need this to be cell based, because I want to run the same thing for hundreds of different capacitor types, and then a separate thing tells me which capacitor is the most cost effective for my given set of parameters.


Any ideas, or even hits of where i can look for an answer?

Many thanks

[Dacs]

It's possible to control the iteration by carefully setting up the formulas. I've done this technique with my spreadsheets on a numerous basis.

Just to give you the gist of the method:

It's important to keep in mind that (as I observe so far) excel does its calculation cell by cell from the topmost row (Row 1) going all the way to the right (Column A to Z) and then go down 1 row (Row 2) and so forth. This will dictate how you place your formulas to make the iteration work.

So for an example (I'll tackle on Newton-Raphson Method, since I think you'd also be familiar with it):

A1: Value of X
A2: Formula for F(X)
A3: Formula for dF/dX
A4: Formula for Newton-Raphson which is A1 - A2/A3

Cell A4 should give the value for that certain iteration and normally you'd introduce this value back to the loop by placing the formula in A1 as =A4 to create the circular reference.

When you run this (and assuming convergence), it will give the value of X with the tolerance dictated by whatever value you place on Excel under settings.

But in your case, you want to control the convergence upto a certain extent (say within 10%), in this case, you need to place a conditional statement to "break" the circular reference. This will basically control your iteration.

Going back to the problem, I'd normally place this formula on these cells:
A1: =A5
A5: =if(abs(A1-A4)/abs(A1)>0.1,A4,A1)

To expound on the formula:
abs(A1-A4)/abs(A1)>0.1 : This is basically your criteria for convergence (within 10%)
A4 (if TRUE) : It means that we're still beyond tolerance so we need to keep up with the iteration
A1 (if FALSE) : It means that we're within tolerance so we need to break the loop by having a circular reference with the initial value (A1)

Basically, if we reach 10% of desired value, we took out the formula out of the loop and we essentially created a 2-cell loop (A1=A5 then A5=A1), in which the internal tolerance value of Excel will kick in (since there's no difference between A1 and A5).

Again, it's important that you place the formulas in this sequence (from the top going down) because Excel will treat the calculation starting from A1, then going to A2 until it reaches A5, then moving up again to A1 (for the next iteration). Doing otherwise will mess up the evaluation of the conditional statement, and may even cause failure to converge the program properly.

Hope this helps