Solving Implicit Equations in Microsoft Excel

[gates621]

Hi, I was wondering if there is any way to solve this equation for f. (This equation is the Colebrook Equation used to find the friction factor for turbulent flow in circular ducts)

1/(f^0.5)=-2.0Log((e/d)/3.7 + 2.51/Re*f^0.5)

where e, d, and Re can all be referenced from other cells on the same page.
Thank you for your time!

 

[ragknot]

if yo don't know VBA skip this post.
RR is the relational roughness, in meters.(the k divided.dh in the euation)
Re is the Reynolds number

The most quick and accurate way to solve the Colebrook-White equation in Excel is to call this VBA with those two variables. This solution will soon be posted on the USDA's webpage for the public... after it goes thru all the red tape.
Ragknot@gmail.com

PS: You can copy and paste this into an Excel module. Fix the "debug" to read the steps in the immediate window.

'= Darcy-Weisbach Friction Coefficient solution ===============
Function DWFC(RR As Double, RE As Double) As Double
Dim f, stp, sm, dweq1, dweq2, NumDerv As Double
On Error GoTo stopnow
sm = 0.0000000000001: stp = 10: f = 0
While stp > 0.001
dweq1 = 1
While dweq1 > 0
f = f + stp
dweq1 = DwE(RR, RE, f)
If dweq1 > 0 Then savef = f
'Debug.Print stp, f, dweq1
Wend
f = savef: stp = stp / 10
Wend
dweq1 = 1
While Abs(dweq1) > sm
dweq1 = DwE(RR, RE, f)
dweq2 = DwE(RR, RE, f + sm)
NumDerv = (dweq2 - dweq1) / sm 'Numerical derivative
f = f - dweq1 / NumDerv 'Newton's method
'Debug.Print dweq1 / NumDerv, f, dweq1
Wend
DWFC = f
Exit Function
stopnow: DWFC = 9999: Exit Function
End Function

 

[ragknot]

Colebrook-White equation, published in 1939. I have found the solution.
continued. one more part


Function DwE(RR, RE, f)
'this function computes the value of the equation, given specific rr,Re and f
On Error GoTo Halt
DwE = (1 / f ^ 0.5) + 2 * Log((2.51 / (RE * f ^ 0.5) + (RR) / 3.7)) / Log(10#)
Exit Function
Halt: End: DwE = 9999: Exit Function
End Function

 

[mirabeau]

ragnot,

this originated as a very ancient thread on a specialized equation which is much less in scope than suggested by the title.

does your solution approach have any application to more general implicit nonlinear equations?

you mention Goal Seek, but actually it was the Excel Solver mentioned as a useful method of solution. is there any problem with Solver (with which many participants in this forum are likely to be familiar) as a method of solution to the Colebrook equation and/or more general implicit nonlinear equations?


you state Yes, I have found a simple way to solve this equation... to absolute accuracy.
Is your "simpler way" indeed simpler to use than the more familiar Solver?

what do you mean by "absolute accuracy"? generally implicit nonlinear equations, if indeed they have real solutions at all, will have irrational solutions with a non-terminating number of decimal places. why is your accuracy any more absolute than others? do you mean that you can calculate solutions to a better approximation than alternatives?

 

[ragknot]

I was answering a specific question about the Colebrook-White equation.
And YES it is absolutely accurate. If you have a calculator that will compute 1000 digits, my solution will solve correctly to 1000 correct digits. And is a lot better than the Excel Solver, because Solver is made to search for a generic solution. My solution was specifically Colebook-White equation. Try this on
Solver, Given RR=.5 and Re=350000, what is f?

And Yes, the solution might work on other problems, but you would have to specifically understand the path, and replan the path before you could "track toward" the proper solution.

Propose a specific equation and I will think more about it.

If you can understand the path toward my solution you might answer your question.

ragnot,

this originated as a very ancient thread on a specialized equation which is much less in scope than suggested by the title.

does your solution approach have any application to more general implicit nonlinear equations?

you mention Goal Seek, but actually it was the Excel Solver mentioned as a useful method of solution. is there any problem with Solver (with which many participants in this forum are likely to be familiar) as a method of solution to the Colebrook equation and/or more general implicit nonlinear equations?


you state Yes, I have found a simple way to solve this equation... to absolute accuracy.
Is your "simpler way" indeed simpler to use than the more familiar Solver?

what do you mean by "absolute accuracy"? generally implicit nonlinear equations, if indeed they have real solutions at all, will have irrational solutions with a non-terminating number of decimal places. why is your accuracy any more absolute than others? do you mean that you can calculate solutions to a better approximation than alternatives?

 

[ragknot]

I tested Solver with the problem I made up. rr=.5 and Re=350000 and it came very close, and it took about two minutes (my DWFE solution is immediate).
Solver got the first 6 digits correctly.

My evaluation Solver is 99.99997242% which is better than most web sites with a "demo" solutions. There is one web site that is correct to 15 decimals mostly, but 10% of the time, it only gets 14 decimals correctly because the answer begins with 0.0.... and it loses one significant digit.

 

[mirabeau]

I was answering a specific question about the Colebrook-White equation.
And YES it is absolutely accurate. If you have a calculator that will compute 1000 digits, my solution will solve correctly to 1000 correct digits. And is a lot better than the Excel Solver, because Solver is made to search for a generic solution. My solution was specifically Colebook-White equation. Try this on
Solver, Given RR=.5 and Re=350000, what is f?

And Yes, the solution might work on other problems, but you would have to specifically understand the path, and replan the path before you could "track toward" the proper solution.

Propose a specific equation and I will think more about it.

If you can understand the path toward my solution you might answer your question.

I guess I'm uneasy with the claim of "absolute accuracy" for a nonlinear equation of this complexity. However, no matter. That can be seen as a terminological matter.

Other approaches, such as Newton-Raphson, might then also claim "absolute accuracy" in this respect, since one can iterate them to 1000 "correct digits" or as many as one's patience and/or computing resources will withstand.

Newton-Raphson is pretty widely known and seemingly could be adapted to the Colebrook-White equation, as it could to very many others, and is thus in one sense "generic" (a disadvantage??) or can be made specific if one wishes.

I must have regard to your work and your contribution, but I personally have never used the Colebrook-White equation although I have solved a variety of nonlinear equations from time to time. I did have a brief look at Navier-Stokes once when doing meteorology, but never got far into that sort of thing.

I do note however that for those that do have use for this equation, a Google search does throw up some claimed solutions, including at least one based on Excel spreadsheets ... (edit: as I now see you mention in your most recent post)

 

[ragknot]

"The normal Newton-Raphson method won't work on the Colebrook-White equation because the equation curve is changes in different areas." That was what I read somewhere, but I suppose who ever said that did not investigate it very much. If you review the "normally stated" equation it begins like this... 1/sqrt(f) = -2*Log(.......). I just change it to 0 = 1/sqrt(f) + 2*Log(....).

So the only change is to rewrite it to equal zero when the solution is found.

I don't think f should every be greater than one, but sometimes I have seen solutions up past 20, but I think some error was made in computing the rr and Re. The very simplest solution would be to evaluate the equation and see if the evaluation IS zero with your guess af f. If it is, then the solution is what ever you guessed was f.

If you read the VBA code, I began what I call "step forward, then back". I set f close to zero and step forward by a step of 10. If the equation evaluates to a negative value, step back to the value of f that gave a positive, and reduce the step size to 1/10th. and start forward again,
If you continue this very simple routine for about 100 steps (loops) you will get a correct f of about 15 decimals on average. But another insight is that if you get within three to four decimals for f, you can use the Newton-Raphson method because the you have already narrowed down the possible area. I just find the numeric derivative for each step to get to the smallest value to zero that Excel will handle. I then go back the the original equation and evaluate the left side and right side using my found f. Comparing the left side to the right side, I evaluated how close the f actually was, usually numerical derivative cuts the loop down to about 5 more loops, to get 16 decimal places in Excel's VBA

As you should then see, that if your calculator would calculate 1000 decimals,you could get accuracy of f up to 1000 decimals if the given rr, Re, were perfect.

 

[mirabeau]

If you read the VBA code, I began what I call "step forward, then back". I set f close to zero and step forward by a step of 10. If the equation evaluates to a negative value, step back to the value of f that gave a positive, and reduce the step size to 1/10th. and start forward again,
If you continue this very simple routine for about 100 steps (loops) you will get a correct f of about 15 decimals on average. were perfect.

That bit sounds like something of a grid search procedure.

I did try similar once as an alternative to Excel's Goal Seek. I think there's an early version of that attempt posted on Mr Excel somewhere, but I didn't really pursue it. In part because some of the main interest/application of nonlinear equations to me lie in whether they have complex number solutions or not, and if so what do such solutions imply for the behavior of the systems which the equations model?

Grid search may be possible but less straightforward for possible complex solutions.

In the event, I became more interested in David Hilbert's (German mathematician) views first given around the 1880's that many/most of the properties of systems described by nonlinear implicit equations can be better assessed by analysing the properties of the equations themselves and that explicit numerical solutions are so frequently unnecessary. I now also have a feeling that Alan Turing's, and subsequent, development of digital computers, and the increasing influence of the engineers may have modified development of thinking in this area.

So I've never used the Colebrook-White equation, and so haven't tried out your method of solution. Even if I did, it would really mean little to me. Maybe others on this forum ...

 

[ragknot]

Member "Anilkumar" posted some errors in his graphs. The graphs makes it seem like it would be very hard to find a solution. I suspect the graphs were made with Excel, an that might be the problem.

The Excel default graphing uses "curved" lines, which usually shows error when the plotted points are distant from others. An Excel plot of the Colebrook-White solution usually shows a "bump" just below the zero line, like in the plot in his post. If your plot shows a bump, use straight lines instead, and you will see why the curved plot is an error.

Two similar Colebrook-White equations show no "bump"
1/sqrt(f)*= 1.14 - 2 * Log(9.35 / (Re * sqrt(f)) + rr)
1/sqrt(f)*= -2 * Log(2.51 / (Re * sqrt(f)) + rr / 3.7)

For example, assume increasing f values and compute the equation...
=1/f^(1/2)+2*LOG(2.51/(Re*f^(1/2))+rr/3.7)
where rr = relative roughness and Re - Reynolds Number.

Use straight lines and where the line goes below zero, make the f values very close and the solution will become obvious.

contact me at Ragknot@gmail.com for more info.

 

[mirabeau]

hey Ragknot,

what is all this about anyway?

you have a great interest in the Colebrook-White equation. Sure it's good to have interests, but do you have a particular problem you want to discuss with the MrExcel forum?