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]

New Simplified, Accurate, method for Colebrook

A New Simplified, Accurate, method for Colebrook equations
by (ragknot) Harrell Geron, NRCS Civil Engineer

This is the most simple and accurate solution for the Darcy Friction Factor with Excel. It works with the popular Colebrook-White equations. After learning this method, you can design your own procedures.

Here are four Colebrook-White equations written to work in Excel.


1/sqrt(f)=-2*Log(Rr/3.7+2.51/Re*1/sqrt(f))
1/sqrt(f)=1.14-2*Log(Rr+9.35/Re*1/sqrt(f))
1/sqrt(f)=1.14+2*Log(1/Rr)-2*Log(1+(9.3/(Re*Rr)*1/sqrt(f)))
1/sqrt(f)=1.74-2*Log(2*Rr+18.7/Re*1/sqrt(f))


Steps----------------

If you don't know values for Re and Rr, use these ...
Re=200000 and Rr=0.04... If you don't do Excel, just read last paragraph

1. Select one of these equations and paste it into an Excel workbook,
2. Edit the equation, remove the left side up to "="
3. Replace the Re with the Reynolds Number, Replace Rr with the relative roughness.
4. Replace the "1/sqrt(f)" near right end with the number 3.
5. The result of this will provide an initial value for 1/sqrt(f).
6. Copy that Excel cell to a cell just beneath that initial value.
7. Edit that 2nd cell. Replace that "3" with a point and click to 1st cell.
8. Copy that second cell down to about 20 rows. The result will begin repeating the same value.
9. That repeating number is the value of 1/sqrt(f), it usually is solved by step 7 on average.
10. Write one more equation that is 1 divided by the square of that repeating value.

The last step will give the Darcy f factor with at least 15 decimals of accuracy.
To check it, use it in the right side of the equation with your computed f inside the "1/sqrt(f).

That result will be the same as your repeating value that was 1/sqrt(f) if it does not equal it, then you made an error some where,
An average of 7 steps will achieve an accuracy of 15 digits (the maximum digit accuracy for Excel).

 

[ragknot]

My Simple Colebrook-White Equation method has now been confirmed by several piping companies. One of the main goals to to provide free and easy solutions to the public. You can easily solve any of the popular Colebrook equations, and after you see how easy it works, you will be able to share this with others.

Here's one of my postings for public use.

http://www.cheresources.com/invisio...fied-accurate-method-for-colebrook-equations/

 

[ragknot]

I saw the equation ...
1/(f^0.5)=-2.0Log((e/d)/3.7 + 2.51/Re*f^0.5)
and I liked it because I know the Easy and True solution to this.
It was made in the 1930, and since then, lots of approximation were
made, but since I know many where not very connect. I have shown
over 1000 engineers the Easy and True solution.

First I have shows folks the "1/(f^0.5)" is on both sides, so rename it as X.
The right side has "2.51/Re*f^0.5" which can be turned to "2.51/Re*X"
So the equation will be ... "X=-2*Log((e/d)/3.7+2.5/Re*X)"

But also the Log() will reduce number a lot to be accurate. So you can make about 20 or much less loop.
So in Excel worksheet type a guess number. You may convert the number, later and see the solution is still right.
Then below the guess number type =-2*Log(e/d+2.51/Re*X). But also inter the numbers for e, d and Re,
but for the X, just point to guess number. Then copy the equation down to about 20 more cells. And the X
will change until it gets right. The below the last cell type =1/X/X , but for the X just point to the cell above.

There is an easier VBA module in Excel.










.

 

[ragknot]

Her is a VBA Excel form. Just in Excel page =Easy(e,d,Re), and it will give you the f in the equation ...

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

Function Easy(e As Double, d As Double, Re As Double) As Double
Dim A As Double, B As Double, C As Double, X As Double, F As Double, L As Integer
C = 3: A = 2.51 / Re: B = (e / d) / 3.7
While X <> C
X = -2 * Log10(B + A * C)
C = -2 * Log10(B + A * X)
L = L + 1: If L > 20 Then X = C '..., Excel can not compute more digits.
Wend
F = 1 / X / X
Easy = F
End Function
Static Function Log10(X)
Log10 = Log(X) / Log(10#)
End Function

 

[ragknot]

This equation is not right. It is... 1/(f^0.5)=-2*Log((e/d)/3.7 + 2.51/Re*f^0.5)

The error is this... 2.51/Re*f^0.5 ... It show be 2.51/(Re*f^0.5) ... the main thing
is that the Re and the f^0.5 are both under the 2.51. But I have for a better way to solve
the f. This is a better way to use it... 2.51 / Re * 1/(f^0.5) then you will know the
1/(f^0.5) is on both sides. To solve the equation, Easy and True is to change the 1/(f^0.5)
to X.

So the equation will be X=-2*(Log((e/d)/3.7 + 2.51/Re * X). The easy way to do
in Excel is to type a guess X number, then below the guess number enter the new equation...
=-2*(Log((e/d)/3.7 + 2.51/Re * X) , but for the X, just point to the guess X. But use the
numbers for the e and d and Re. An then format that equation to 16 decimals.
Then just copy that equation down to about 20 more cells.

An each X will be using the cell above it. An you will see the equation will change for
some cells, but maybe at 5 or 10 cells the solution will stop changing for the 16 digits.
Then at the bottom cell just type =1/X/X, but then for the X's just point to the cell above
it. Then the 1/X/X with be the f. Then to test it, use the f number in the both sides of
the 1/(f^0.5) and the =-2*(Log((e/d)/3.7 + 2.51/(Re*f^0.5)). The solution of both sides
will be the last X that you found. You can change the "guess X" and the last X and the f
will be the same.

If you use some of the approximation for the f some of the approximations might be
close to the right f you figured.

 

[ragknot]

This equation is not right. It is... 1/(f^0.5)=-2*Log((e/d)/3.7 + 2.51/Re*f^0.5)

The error is this... 2.51/Re*f^0.5 ... It must be 2.51/(Re*f^0.5) ... the main thing
is that the (Re*f^0.5) are both under the 2.51. But I have for a better way to solve
the f. This is a better way to inter it... 2.51/Re * 1/(f^0.5) then you will know the
1/(f^0.5) is on both sides. To solve the equation, is a Easy and True is to change the
1/(f^0.5) to X.

So the equation will be X=-2*(Log((e/d)/3.7 + 2.51/Re * X). The easy way to do it
in Excel is to type a guess X number, then below the guess number enter the new equation...
=-2*(Log((e/d)/3.7 + 2.51/Re * X) , but for the X, just point to the guess X. But use the
numbers for the e and d and Re. (or us Rr as the (e/d) ). An then format that equation
to 16 decimals. Then just copy that equation down to about 20 more cells.

An each X will be using the one cell above it. An you will see the equation will change for
some cells, but maybe at 5 or 10 cells the solution will stop changing for the 16 digits.
Then at the below the bottom cell just type =1/X/X, but then for the X's just point to the
cell above it.

Then the 1/X/X with be the f. Then to test it, use the f number in the both sides of
the 1/(f^0.5) and the -2*(Log((e/d)/3.7 + 2.51/(Re*f^0.5)). The solution of both sides
will be the last X that you found. You can change the "guess X" and the last X and the f
will be the same.

If you use some of the approximation for the f some of the approximations might be
close to the right f you figured. But it is not a approximation, it is an Easy and True solution.

To see a web site I helped to solve this see... keisan.casio.com/exec/system/1380521258

 

[ragknot]

This equation is not right. It is... 1/(f^0.5)=-2*Log((e/d)/3.7 + 2.51/Re*f^0.5)

The error is this... 2.51/Re*f^0.5 ... It show be 2.51/(Re*f^0.5) ... the main thing
is that the Re and the f^0.5 are both under the 2.51. But I have for a better way to solve
the f. This is a better way to use it... 2.51 / Re * 1/(f^0.5) then you will know the
1/(f^0.5) is on both sides. To solve the equation, Easy and True is to change the 1/(f^0.5)
to X.

So the equation will be X=-2*(Log((e/d)/3.7 + 2.51/Re * X). The easy way to do
in Excel is to type a guess X number, then below the guess number enter the new equation...
=-2*(Log((e/d)/3.7 + 2.51/Re * X) , but for the X, just point to the guess X. But use the
numbers for the e and d and Re. An then format that equation to 16 decimals.
Then just copy that equation down to about 20 more cells.

An each X will be using the cell above it. An you will see the equation will change for
some cells, but maybe at 5 or 10 cells the solution will stop changing for the 16 digits.
Then at the bottom cell just type =1/X/X, but then for the X's just point to the cell above
it. Then the 1/X/X with be the f. Then to test it, use the f number in the both sides of
the 1/(f^0.5) and the =-2*(Log((e/d)/3.7 + 2.51/(Re*f^0.5)). The solution of both sides
will be the last X that you found. You can change the "guess X" and the last X and the f
will be the same.

If you use some of the approximation for the f some of the approximations might be
close to the right f you figured.

 

[SpillerBD]

My Simple Colebrook-White Equation method has now been confirmed by several piping companies. One of the main goals to to provide free and easy solutions to the public. You can easily solve any of the popular Colebrook equations, and after you see how easy it works, you will be able to share this with others.

Here's one of my postings for public use.

A New Simplified, Accurate, Method For Colebrook Equations - Cheresources.com Community

Public to registered users of that site...

 

[SpillerBD]

Ragnots solution is iterative calculation in VBA. Iterations can be done on a worksheet without VBA, and are performed much faster than goal seek.
However, when using iterative calculations in the worksheet circular reference errors will be turned off and that can allow errors elsewhere. (Just turn of iterative calculation to check for those errors.)

 

[SpillerBD]

[RANGE=cls:xl2bb-100][XR][XH=cs:3]Excel 2012[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][/XR][XR][XH]1[/XH][XD=h:r]Epsilon=[/XD][XD=h:r]0.015[/XD][/XR][XR][XH]2[/XH][XD=h:r]Pdia=[/XD][XD=h:r]1[/XD][/XR][XR][XH]3[/XH][XD=h:r]Reynolds=[/XD][XD=h:r]200000[/XD][/XR][XR][XH]4[/XH][XD=h:r]=-2*LOG((Epsilon/PDia)/3.7 +
2.51/Reynolds*RightSide)[/XD][XD=h:r]=-2*(LOG((Epsilon/PDia)/3.7 +
2.51/Reynolds*LeftSide))[/XD][/XR][XR][XH]5[/XH][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]6[/XH][XD=h:r]f =[/XD][XD=h:r]=1/RightSide/RightSide[/XD][/XR][XR][XH=cs:3][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

Named cells should be self-explanatory. I couldn't find "Enable iterative calculation" to add to the QAT So the recorded code may be good for the PERSONAL.XLSB

Sub IterationTurnOn()
    Application.Iteration = True
End Sub

Until iteration is turned on you will have the circular reference error.

[IMG

Excel 2012
	A	B
1	Epsilon=	0.015
2	Pdia=	1
3	Reynolds=	200000
4	4.771484898	4.771484898
5
6	f =	0.043923090770254
Sheet1

[/IMG]