Solving the Colebrook-White equation in Excel without Solver/VB/macro 11

[AndersE]

Hi everyone

Just wanted to share a little tip for direct solving of implicit equations (like the Colebrook-White equation) in Excel without having to use Solver, goalseek, VB or macros.

Implicit equations will of course still have to be iterated in order to be solved, but this can easily be automated.

The trick is to allow Excel to iterate circular references.

Go to "Tools" -> "Options" -> "Calculation" tab and check the box "Iteration".
You might also want to change the maximum number iterations (the default is usually 10, I use 100). Note that if the "Iteration" box is not checked Excel will give an error message regarding circular references with this method.

Here is a simple examle for solving the equation x = ln(x + 2):

Let cell A1 be the left side of the above equation (in other words "x"). Enter the formula "=A2" into cell A1.
In cell A2, enter the right side of the equation: "=LN(A1 + 2)"
Excel will automatically iterate the above equation and arrive at a result where the values in cell A1 and A2 become the same.

(All equations are entered into excel without the "s)

The Colebrook-White equation, 1/sqrt(f) = -2*log(e/D/3.7 + 2.51/Re*1/sqrt(f)), is solved in the same way as the above example:

Enter the Reynolds number into cell A1
Enter the relative roughness e/D into cell A2
In cell A3, enter "=A4"
In cell A4, enter "=-2*LOG(A2/3.7 + 2.51/A1*A3)"
In cell A5, enter "=1/A3^2" for calculating the final result

I have not yet been able to solve the equation directly for the friction factor without going through the third step in cell A5.

The equations I have tried solves instantly with this method on my computer, much faster than goal seek or solver.

Hope you'll find this tip useful!

 

[AndersE]

Turns out it is actually possible to solve these equations directly by letting the equation refer to it's own cell. Guess I should have tried that first...

Colebrook-White equation can the be written as:

Enter the Reynolds number into cell A1
Enter the relative roughness e/D into cell A2
In cell A3, enter "=-2*LOG(A2/3.7 + 2.51/A1*A3)" for solving for 1/sqrt(f) or simply "=1/(-2*LOG(A2/3.7 + 2.51/A1*A3))^2" for solving for f directly.

 

[BigInch]

That's gotta be worth a star for something.

**********************
"Pumping accounts for 20% of the world’s energy used by electric motors and 25-50% of the total electrical energy usage in certain industrial facilities."-DOE statistic (Note: Make that 99% for pipeline companies)

http://virtualpipeline.spaces.live.com/

 

[katmar]

I like this approach. 30 or 40 years ago there was a drive to find explicit approximations to the Colebrook-White equation and then this died out as people learned to iterate on their programmable calculators and then on their PC's. But recently I have seen a resurgence of this effort and there have been some very elaborate equations proposed. I am sure that Anders' method would be faster, easier to program and more accurate than these new approximations.

I agree with BigInch - definitely worth a star.

In my opinion, the only real benefit that came out of all the work towards explicit equations was the method proposed by Churchill which generates a continuous curve for the friction factor from the laminar regime right through to fully turbulent. This is a huge advantage when programming and has become a "de facto" standard.

Katmar Software
Engineering & Risk Analysis Software

http://katmarsoftware.com

 

[BigInch]

Ya. And I'd even like it better, if I hadn't discovered that Churchill eq.

**********************
"Pumping accounts for 20% of the world’s energy used by electric motors and 25-50% of the total electrical energy usage in certain industrial facilities."-DOE statistic (Note: Make that 99% for pipeline companies)

http://virtualpipeline.spaces.live.com/

 

[AndersE]

Don't forget to set the "Maximum change" value (also under the "calculation" tab relatively low. Otherwise the accuracy will be too low to motivate the use of implicit equations. I use 1e-6 for the Colebrook-White equation.

Apparently Excel will repeat the iteration every time any cell value in the spreadsheet is changed, including cells that have nothing to do with the equation. So I guess this could slow down Excel if you have many references or complicated implicit formulas.

But I agree with BigInch: Churchill's equation is genius and is in most cases accurate enough for an engineer.

A drawback with the circular reference approach is that Excel by default has the "iteration" option turned off and the "max change" value set quite high, 0.001 or something. So if you are developing a calculation tool for your company everyone who uses the spreadsheet must change these Excel settings or the tool will not work.

 

[JohnBreen]

We need more of this sort of sharing.

 

[SNORGY]

For Colebrook-White, I simply set up a Newton-Rhapson scheme and spit the successive iteration results into a row or column of cells with each succeeding cell taking the value from the previous one. When the values of two consecutive cells are equal to within x%, I stop the iteration and use a VLOOKUP(max(...)) or HLOOKUP(max(...)) to return the last iteration result. I believe I return the absolute value in the iteration scheme under the premise that friction factor F will always be positive provided your seed value is to the left of the actual root you are looking for.

Crude, but no VBA programming required - and it works.

Regards,

SNORGY.

 

[prex]

AndersE, you could write a FAQ in this forum with your method (that BTW may be extended to far more complex problems, like solving differential equations in one or 2 variables).
Please also note that:
-the setting Iterations=On and the precision are retained in the worksheet, so it is not necessary to set them each time (however I don't know the exact rules of this behavior, the setting might be lost under certain circumstances)
-the method should be made more robust. In particular the iterative method in Excel doesn't work if you enter by mistake an invalid input. Try for instance Re=0: the result shows an error and there is no way of recovering from it. I adopt an [tt]IF(ISERROR();[/tt] test in the iterative formula to get out of this situation: note that there are various error testing functions, am not sure at the moment of which one is most correct.

prex

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes and launchers for fun rides

http://www.levitans.com

: Air bearing pads

 

[prex]

Oops! There's already a FAQ on that argument by quark. It is not based onto the Iteration feature, so it is not prone to the error above. However the use of the Iteration feature is more general, an alternate FAQ based onto it could be in order.

prex

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes and launchers for fun rides

http://www.levitans.com

: Air bearing pads

 

[eln10]

There is a small mistake in the above Excel equation.
The square root is missing from the right hand term (A3)and it should be divided not muliplied.

simply "=1/(-2*LOG(A2/3.7 + 2.51/A1*A3))^2" for solving for f directly

If you try to just add sqrt(A3), you get a division by zero error because the initial value of the cell is zero. One way to get around this is to add a tiny little number to A3 like this:

"=1/(-2*LOG(A2/3.7 + 2.51/(A1*SQRT(A3+1E-300))))^2"

This equation now agrees with Colebrook-White to many decimal places.

I've used this small number trick to set a cell's initial value above zero a number of times. Have not found a better way.

 

[SNORGY]

Although I suspect the "D" term is missing from what should be "3.7*D", I am going to try this.

Regards,

SNORGY.

 

[AndersE]

eln10:

You're right, the formula I supplied in the second post is of course incorrect since cell A3 is equal to f and not 1/sqrt(f).

Thanks for pointing that out, I should have checked the results better before posting (the result is often not too far off, which is why I missed it).

prex:

Yes, the method crashes when Re = 0 or the cell for Re is blank. The only way of recovering seems to be by editing the cell with the equation (for example adding a blank and then immediately erasing it).

One way of making the method more stable is by adding an IF function that checks the values of Re and e/D:

=IF(AND(A1>0;A2>0);1/(-2*LOG(A2/3.7 + 2.51/(A1*SQRT(A3+1E-300))))^2;10000)

The last value in the IF formula (i used 10000) has to be a positive number since the cell refers to itself. If I try a string like "ERROR" or a negative number the formula will not be able to recover. So I found the best solution was to use a number that is way to high to be a friction factor in order to avoid confusion.

SNORGY:
The formula should be correct, cell A2 is relative roughness e/D and not absolute roughness e.

 

[vzeos]

AndersE
I complement you on your ingenuity. The solution method that quark uses in his FAQ is a form of successive nesting or successive substitution and can yield extremely accurate results when sufficiently nested. The successive nesting can be done in one cell without much difficulty, as illustrated below.

Colebrook Equation Tf = -2.0*log((e/D)/3.7 + 2.51/Nre*Tf)

Tf = 1/[√]f = transmission factor (f= Darcy-Weisbach friction factor)
Ke = effective roughness
D = diameter
Nre = Reynolds number

Start by letting Tf on the right side be equal to (1), and get
Tf = -2.0*log((e/D)/3.7 + 2.51/Nre*(1))

now copy the right side of the equation and paste it over the (1), and get
Tf = -2.0*log((e/D)/3.7 + 2.51/Nre*(-2.0*log((e/D)/3.7 + 2.51/Nre*(1))))

you can repeat this procedure 5 or 6 times, and get
Tf = -2.0*log((e/D)/3.7 + 2.51/Nre*(-2.0*log((e/D)/3.7 + 2.51/Nre*(-2.0*log((e/D)/3.7 + 2.51/Nre*(-2.0*log((e/D)/3.7 + 2.51/Nre*(-2.0*log((e/D)/3.7 + 2.51/Nre*(-2.0*log((e/D)/3.7 + 2.51/Nre*(1))))))))))))

Every time you nest the equation, the error introduced by allowing Tf=(1) is diminished. After 5 or 6 nestings the error is negligible. This method is easily done in Excel using upto 7 or 8 nestings and is much more accurate and faster than iteration.

By the way, don't be concerned if this equation is unstable at Nre=0 because the Colebrook equation is not valid for flows in which the Nre < about 2000.

 

[SNORGY]

Thanks AndersE.

I guess I am just so conditioned to seeing the "D" there when I see it in GPSA (11th Ed. Eq. 17-11) and other places that I automatically assumed it was a mistake. I should have thought it through a bit more.

Regards,

SNORGY.

 

[AndersE]

By the way, don't be concerned if this equation is unstable at Nre=0 because the Colebrook equation is not valid for flows in which the Nre < about 2000.

No, but when you use a spreadsheet there is a good chance that you will sometimes erase the input values. If this happens, the blank cell is treated as being equal to zero and the calculation crashes.

I would like to have a method that is both more stable and also more general. For example:

1/(x^2 - sin(x)) = log(x + 3)

Does anyone have any idea how the above equation could be solved (in a relatively simple way) with the circular reference iteration method?

 

[vzeos]

No, but when you use a spreadsheet there is a good chance that you will sometimes erase the input values. If this happens, the blank cell is treated as being equal to zero and the calculation crashes.

I'm afraid you missed my point. When the Reynolds number is below the critical Reynolds number (about 2000) you should switch to the Pousille-Hagen equation for laminar flow. Then, when the Reynolds number is zero the Pousille-Hagen equation will also be zero and your spreadsheet will not crash.

I have attached a spreadsheet to illustrate how easily implicit transmission factor calculations can be done.

 

[quark]

A small clarification from me. I knew about the iteration built in excel but as I hard coded the formulae (because macros are Greek to me), I prefered the circular reference error to be available (to have a check on other formulae) to me all the time. Iteration supresses the circular reference error for the entire worksheet.

 

[AndersE]

vzeos:

OK, now I see what you mean. With the circular reference method one could for example use Hagen-Pousille equation when Re is below 2000, instead of just resulting in a friction factor equal to 10 000 like my formula in a previous post.

quark:

That is a good point. It's a bit strange that it is not possible to allow circular references just for certain cells. When allowing it for the whole worksheet you can easily end up with incorrect sums etc.

 

[prex]

AndersE, you can transform that equation in many ways to solve it by iteration. However if you transfer this discussion onto the general problem of iterative solutions, then this thread would become endless...
A problem with iterative methods is stability: depending on the initial tentative value you take for the unknown (and you have to take one, even when you use the bad method of a cell referring to itself, the tentative value being presumably 0 in that case), you can have a divergent iteration. Another problem is that any equation may have more than one solution, and you won't know in advance towards which one the method will converge.
As an example, taking your last equation (where BTW I don't know whether you meant a decimal or natural log), if you transform it into
[tt]x=EXP(1/(x^2-SIN(x)))-3[/tt]
then this one will almost inevitably converge to the solution [tt]x=-1.70853189168026[/tt](but of course it converges to the other one below if starting sufficiently close to it).
If you transform it into
[tt]x=SQRT(SIN(x)+1/LN(x+3))[/tt]
then starting from 0, 1 or even -1.7 it always converges to [tt]x=1.28309676971774[/tt].

prex

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes and launchers for fun rides

http://www.levitans.com

: Air bearing pads