# Solving Implicit Equations in Microsoft Excel

This is a discussion on Solving Implicit Equations in Microsoft Excel within the Excel Questions forums, part of the Question Forums category; Hi, I was wondering if there is any way to solve this equation for f. (This equation is the Colebrook ...

1. 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.

2. Looks like the best bet is to use the solver. How about rearranging the equation to:

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

The solver can then solve for f (a variable input cell) that will drive the formula result to 0.

HtH

3. ## Re: Solving Implicit Equations in Microsoft Excel

Make use of circular reference this can be done witin the worksheet itself.

SOLVING COLEBROOK EQUATION
Viswanatha et. Al
Dept of Fluid Mechanics, IIT Madras

1 SIMPLIFIED BERNAULLI'S EQUATION FOR AIR FLOW THROUGH DUCTS

Head loss between position 1 and 2 of a duct system can be expressed by using extended Bernoulli's equation as
h loss = [n12/2g + p1/rg + z1] -[n22/2g + p2/rg + z2]

p Fluid Static Pressure : N / square meter
r Fluid Density : Kg per cubic meter
g Acceleration due to gravity : meter per second square
n Average velocity : meter/second
h Loss in "head" : meter
z Elevation with reference to a fixed reference. : meter
1 : Point 1
2 : Point 2

When analysis is based on uniform velocity, horizontal flow, magnitude of velocity and elevation at both positions is same and so cancels. Then the equation becomes

h loss = p1/rg - p2/rg

So a measurement of "static pressure" at two points can give the pressure drop due to friction and other reasons.

For engineering design we need an expression for the term h loss. The above equation is not a predictive equation. We need to construct a actual duct and measure the pressure loss experimentally. But what is required my engineers is a predictive equatio3.6.2

2 DARCY-WEISBACH EQUATION

In 1957, Henry Darcy came up with a similar but some what different equation "for old rough pipes" under turbulent flow conditions. Also he proposed a factor which depends on pipe roughness and diameter. Darcy never proposed factor f as proposed by Weisbach but the friction factor f is known after his name. Julius Weisbach (1806-1871), a native of Saxony proposed in 1845 the equation for hloss as we know now as "Darcy-Weisbach equation":
h loss = f (L/D) V 2/2g

L= length of duct. D is hydrolic diameter of the duct.
f = friction factor or friction coefficient. However, he did not provide adequate data for the variation in f with velocity.
The term V2/2g is known as "Velocity Head" where V = flow velocity and g = acceleration of gravity.

Darcy-Weisbach equation is the best known empirical formula for frictional pressure drop or energy loss. It is dimensionless and at the same time complex function of pipe roughness, hydraulic diameter, fluid viscosity, fluid density, and flow velocity. Also it is to be noted that these complexity of f is resulted from "boundary layer mechanics".

J.T Fanning combined Weisbach and Darcy equations but used hydraulic radius instead of diameter and published tables of "Fanning f". "Fanning f" is (1/4) Th of present day known "Darcy f".

There were many other forms of empirical equations like the Hazen-Williams equation which is of interest only for historians and now such equations are not considered of any universal use for friction factor calculation. The Darcy-Weisbach equation became popular once Moody diagram became available and engineers started its use only once electronic calculators became common. Currently Darcy-Weisbach equation is considered as standard because of its complete range of application and accuracy.

3 PRESSURE LOSS – FLOW RELATIONSHIP
In general, the function between pressure loss and flow can be represented by the following parabola:
Pressure loss = (Hydraulic constant) (Flow)2
where "Hydraulic constant" depends on duct shape, size, length, material, air density, and the sum of C-coefficients. All of these variables are presented in the previous equations above.

4 PRESSURE LOSS – DIAMETER RELATIONSHIP
As flow rate is inversely proportional to area of duct, and hence inversely related to D2,
a proportionality to (Flow)2 means inversely related to D4 . infact, from the Darcy equation below, we can see that Pressure loss is inversely proportional to D5.

5 FRICTION FACTOR FOR TURBULENT CONDITIONS IN AIR DUCT:

A MOODY DIAGRAM
The condition for turbulent flow is when Reynolds Number is above that of transition range. For almost all of industrial duct systems, the velocity need be sufficiently high to carry along dust/fumes/vapor/gas that common condition that exist is turbulent flow condition, i.e. Re > 4000.

Lewis F. Moody (Professor, hydraulic engineer, Princeton University) in ASME Transactions of November 1944 published Moody's work (and Moody Diagram), in his article entitled "Friction Factors for Pipe Flow". It is a plot of Fanning friction factor against Reynolds number. It is a graphical solution for Colebrook equation. This has become basis for many of the calculations on friction loss in pipes, ductwork and flues. Moody references the work of C. F. Colebrook and C. M. White in developing Moody Diagram. Crane TP410 or any hydraulics textbook gives Moody Diagram. Recently it has come to know that Moody diagram is only +/-15% accurate. Glenn O. Brown (professor, Oklahoma State University, OK) recently in his paper put on Internet wondered that why for more than 58 years the Modi diagram was used unmodified.

B COLEBROOK EQUATION FOR SOLVING f
There are many formulas for calculating friction factor for turbulent conditions. Colebrook (1938) developed the best-known formula, which requires iterations, in 1938.
1/ f 0.5 = -2 log10 [(e/D)/3.7 + 2.51/(Re. f 0.5)]

e is relative roughness

This is a "understood though not directly expressed" equation otherwise known as implicit equation. Solving such equation requires that you assume a value for f to start the iteration. The assumed values converge in few (less than 100) iterations. Please see belowfor how it convergence. Besides this, there exist several other implicit forms of Colebrook equation and several explicit forms. It is to be remembered that it is the boundary layer theory of Ludwig Prandtl (1875 to 1953) and students gave way to the development of Colebrook and White equation.

Iterations are normally done by using computer programs using software like Fortran but recently some simple methods based on spreadsheets are available and becoming popular[1] . Many attempts by engineers using VBA and mcros are not perfect, as they need either a deep knowledge of Visual Basic or have to use tools like Goal Seek available in Excel, which makes multiple solving within a single sheet time consuming and non-automatic.
C RELATIVE ROUGHNESS

The term e in the above term is relative roughness which is found by experiments (fitting the pressure-drop data in to Colebrook equation) or in comparison to the roughness parameters of various material pipes reported in literature. Also it is to be remembered that relative roughness is a parameter of importance only for turbulent flow and this is the condition in all industrial exhaust ducts.

Roughness suitable for substituting in to Colebrook equation for commercial pipes is presented in various literatures. Following table gives the typical values that can be used to solve Colebrook equation. It is emphasized here that no major error will be introduced by a slight wrong judgment.

Quality of Example Normal Range in mm
Roughness

Drawn Pipe Drawn Glass Tube 0.0001
Smooth Fresh and smooth Aluminum, Brass, or Copper tube 0.0015
Medium Smooth SS pipe, Molded FRP Pipes looking like mirror finish inside.
Also similar extruded PP, PE etc pipes. 0.005 0.015

Average Galvanized steel 0.02 0.15
Medium Rough New cast iron, well planed wood 0.2 0.8
Rough Concrete, Wood, Rusted Steel, Flexible PVC 1 5
D COLEBROOK SOLVER

A worksheet was set up (see reference below) to solve Colebrook equation without use of any macros or VBS . Nor this worksheet use solver or goalseek functions in Excel. (see reference 1).
There are not restrictions on how many times you can use that in the worksheet. It is capable of solving 100 cases of Colebrook Equations.

The convergence pattern of Colebrook equation is clear from following graphs.

The first solution is more than f and second solution less than f. As iteration is repeated, this fluctuation continues and converges to a closest value of f. The precision of resulted f is determined by numberof iterations.

The graph shows that a 8-digit precision is attained within just 12 iterations.

The worksheet does 100 iterations, which is more than sufficient for 15-digit accuracy.

The features of Colebrook Solver can perform:

1 100s of cases of solutions done simultaneously.
2 Each solution is done by 100 iterations giving accurate solution.
3 Can be easily incorporated with other worksheets in Excel.
4 Do not require manual operations like that required for back solver of VB based user defined functions.

E Parameters Effecting Colebrook Friction Factor f
How friction factor depends on various parameters are of interest for designers and scientists alike. One may note that more important than friction factor for designer is pressure drop, which will be analyzed in the next sub-section.

FRICTION FACTOR AND DIAMETER OF DUCT: For same velocity, friction factor decreases with increase in diameter. The decrease is steeper initially and then levels out.

FRICTION FACTOR AND VELOCITY OF FLOW: As velocity increases, friction factor decreases. This decrease is more pronounced in smooth pipe than in rough pipe. In either case friction factor levels out beyond very high velocities (2000 fpm).

FRICTION FACXTOR AND ROUGHNESS OF PIPE: As a rule, rough pipe will have higher friction factor than a smooth pipe. At low velocity (50 fpm) and higher pipe diameters (50"), the difference between rough and smooth pipe friction factors are as small as 0.9%. At higher velocity (2000 fpm) and lover diameters (1"), which is the other extreme, the difference is found to be about 25%.

FRICTION FACTOR AND TEMPERATURE OF FLUID: It is noted that friction factor shows a mild increase with temperature.

FRICTION FACTOR AND REYNOLDS NUMBER: As Reynolds number increases, friction factor decreases. The decrease is steeper initially and at higher Re, the slope of the curve decreases and tends to flatten. For example, for a 1" duct and 200 fpm, an increase of air temperature from 40 degree to 100 degree Cel caused friction factor to increase by 8.2 %. At 2000 fpm, the increase was only 5%. Similarly for a 48" pipe and 200 fpm flow, the increase in temperature from 40 to 100 caused a increase in friction factor by 9%. At 2000 fpm the increase was only 4%.
F DEPENDENCE OF PRESSURE DROP ON VARIOUS FACTORS
Pressure drop is the most important aspect that a designer considers during duct design. So it is important to analyse and see what are the parameters effecting pressure drop. This is because it is the velocity, which contributes momentum to particles in the path of the moving air..

VELOCITY AND PRESSURE DROP (Medium Smooth Pipe): Velocity of air is the basic requirement that a designer have to achieve ultimately in the duct system to guarantee the performance of a duct. As a rule, pressure drop (head loss in inches / 100 feet) of pipe increases with increase in velocity. There exist a "power relation" between pressure drop and velocity. h a Vx . An extreme case of medium smooth pipe of diameter 2" gave a pressure drop of 48" of water per 100 feet of pipe at V = 2500 fpm where as a 24" pipe gave rise to a pressure drop of 2.6".

DENSITY OF FLUID AND PRESSURE DROP (Medium Smooth Pipe): When density increases, Reynolds' number increase. A 0.1 increase in density (in g/cc) increased pressure loss per 100ft of air by 5.5% for a 1" duct. A similar change for a 48" duct caused 7% increase. Similarly for a air velocity of 2000 fpm, the increase in resistance due to increase of 0.1 in density causes 7.1% increase in resistance for a 1" duct. For a 48" duct it is 7.5%

VISCOSITY OF FLUID AND PRESSURE DROP (Medium Smooth Pipe): Doubling the viscosity of air increases resistance offered by air by 15% for a 2" diameter and 50 cfm case. For a flow speed of 2000 cfm, the same duct exerts 13% higher resistance on doubling the viscosity. For large diameter pipe, the effect is less pronounced. i.e. for 48" diameter and 2000 cfm, the increase in resistance is 10%.

ROUGHNESS AND PRESSURE DROP: For a 1" duct at 50 cfm, a 2% increase in pressure drop is observed from smooth to rough condition. For a flow rate of 2000 cfm, the increase from smooth to rough condition can be as high as 27%. For a 1" duct at 2000cfm this increase is 1%. For a 48" duct at 2000cfm this increase is 14%

6 REFERENCES

1) "SOLVING IMPLICIT EQUATIONS INSIDE YOUR WORKSHEET" by ANILKUMAR M, DR E SREENIVASAN, DR K RAGHUNATHAN : www.frpdesigns.com/iterations.htm

2) DARCY FRICTION FACTOR: http://en.wikipedia.org/wiki/Darcy_friction_factor_formulae

4. ## Re: Solving Implicit Equations in Microsoft Excel

Yes, I have found a simple way to solve this equation... to absolute accuracy.
It beats many websites that have good solutions. I will share this excel worksheet. I work for the NRCS department of the USDA.

I just joined MrExcel, and I don't know this website details.
If you know a lot about Excel, you will be amazed about my easy solution to this complex equation.

Ragknot

Originally Posted by 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.

5. ## Re: Solving Implicit Equations in Microsoft Excel

I don't now what ragnot's solution is, but it can be solved in straightforward fashion using Solver.

6. ## Re: Solving Implicit Equations in Microsoft Excel

Originally Posted by shg
I don't now what ragnot's solution is, but it can be solved in straightforward fashion using Solver.
Yes, my solution is straight forward. But it is an "out of the box" solution,
The solution is not complicated, with my insight. Message me back, and I will be around for a bit. It just passed mid night here and I have to go to work tomorrow. If you have spend much time looking for a solution, you will smile at my insight.

7. ## Re: Solving Implicit Equations in Microsoft Excel

My simple insight.

The equation is (sometime the variables swapped... don't worry with that.

1 / λ1/2*= -2 Log [ 2.51 / (Re λ1/2)* +* (k / dh) / 3.7 ]** …. Find λ

λ Lets call this f, because it is the friction coffecient
Re Lets call this re, it is called the Reynolds Number
k This is the pipe's roughness number.
dh This is the hydraulic diameter, Lets call it d
rr This is not in the equation, but rr is the relative roughness, k/dh

First let's re-write the equation to work in normal excel text.
Remember small f is λ and rr is k/dh.

1/sqrt(f)+2*Log(2.51/(Re*sqrt(f))+rr/3.7)…

now when this equation equals zero, we will have the answer for f.

Assume values for Re and rr, then in a pair of columns begin with f = 0.01 and compute the equation above, use f going up small to about 5.

Then make a x,y graph for f and the equation value. Where the plot crosses the zero line... that would be the value of f for that Re and rr values.

I will use these
rr= 0.08382038
Re= 37884
and 0.02, 0.03, 0.04, 0.07, 0.10, 0.15, 0.22, 0.35, 0.50, and 0.77

if you get -2.147208311 for the f value of 0.77 you have it right so far.

You will see from the graph, the answer for f is about 0.1

You may see the solution from now, but if you get this far, let me know.

It is not necessary to make a plot, but I am trying to explain how to get the answer of f - 0.092870863, let me know you follow me so far.

8. ## Re: Solving Implicit Equations in Microsoft Excel

The first equation did not post absolutely accurate, but the second one did

= 1/sqrt(f)+2*Log(2.51/(Re*sqrt(f))+rr/3.7)

copy and paste this and then substitute both f's and the rr by pointing to those values.

9. ## Re: Solving Implicit Equations in Microsoft Excel

Goal Seek does get close, but only as close as 3 to 5 (maybe more) decimals.

My solution will go to 15 or 16 decimals. (16 if the first two digits are 0.0......) 15 significant digits. My solution goes to whatever the computer program will let it.

10. ## Re: Solving Implicit Equations in Microsoft Excel

corrections
and for f use 0.02, 0.03, 0.04, 0.07, 0.10, 0.15, 0.22, 0.35, 0.50, and 0.77

Originally Posted by ragknot
My simple insight.

The equation is (sometime the variables swapped... don't worry with that.)

1 / λ^1/2 = -2 Log [ 2.51 / (Re λ^1/2)* +* (k / dh) / 3.7 ]

λ Lets call this f, because it is the friction coffecient
Re Lets call this re, it is called the Reynolds Number
k This is the pipe's roughness number.
dh This is the hydraulic diameter, Lets call it d
rr This is not in the equation, but rr is the relative roughness, k/dh

First let's re-write the equation to work in normal excel text.
Remember small f is λ and rr is k/dh.

1/sqrt(f)+2*Log(2.51/(Re*sqrt(f))+rr/3.7)…

now when this equation equals zero, we will have the answer for f.

Assume values for Re and rr, then in a pair of columns begin with f = 0.01 and compute the equation above, use f going up small to about 5.

Then make a x,y graph for f and the equation value. Where the plot crosses the zero line... that would be the value of f for that Re and rr values.

I will use these
rr= 0.08382038
Re= 37884
and for f use 0.02, 0.03, 0.04, 0.07, 0.10, 0.15, 0.22, 0.35, 0.50, and 0.77

if you get -2.147208311 for the f value of 0.77 you have it right so far.

You will see from the graph, the answer for f is about 0.1

You may see the solution from now, but if you get this far, let me know.

It is not necessary to make a plot, but I am trying to explain how to get the answer of f - 0.092870863, let me know you follow me so far.

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•