Estimation of the friction factor $f_F$ using an Excel user defined function

 This post is complementary to an early one to show an iterative procedure to estimate the friction factor $f_F$ from the well known Colebrook equation.

Therefore, the reader is referred to the following post for further details:

Solving the Colebrook equation

A short comment on the procedure

If you have already read the prevous post on the solution procedure to the Cole brook equation it is straight to see that the iterative process fetis very well for a for loop.

Since the friction factor is used for several calculations where it is estimated more than once it is convenient to find a way to avoid having a large number of sheets (one for each calculation). This is true for calculation of flow rates in pipe networks.

One way of going around this technical issue is by using an available tool from Excel. This is called: user defined function. The user defined function needs to be programmed in Visual Basic.

Building the user defined function

For short, the user defined function shall be written from 3 inputs common to the Colebrook equation. These input data are,

• the pipe roughness $\epsilon$
• the internal pipe diameter $ID$
• the Reynolds number $N_{Re}$

and as mentioined innthe previous post, the Colebrook equation is to be used in the following form,

$f_F=\dfrac{0.25}{\log_{10}\left( \dfrac{\epsilon}{3.72ID}+\dfrac{2.51}{N_{Re}\sqrt{f_F}} \right)^2}$

Since the friction factor is to be estimated starting from a guess, our user defind function will need an already set value for the friction factor (just to start the calcultion).

Having given the most important details, the process of creation of this function in Excel is as follows,

• first, open an excel document where the data for $\epsilon$, $ID$ and $N_{Re}$ has already been placed or calculated
• second, press the keys ALT + F11 to open the Visual Basic Editor and in the Insert menu click on Module
• third, in the module window insert the following code for the user defined function

Function fF_Calc(eps, ipd, nre)

' fF calculator

'First use a gues for fF

fFdummy = 0.0001

For i = 1 To 30

 fFdummy = 0.25 / (Log(eps / 3.72 / ipd + 2.51 / nre / Sqr(fFdummy)) / Log(10)) ^ 2

Next i

 fF_Calc = fFdummy

End Function

User defined function for the loop calculation of the friction factor

• save the created function and close the Visual Basic Editor. That is it.

How to use the created function

As you can see, the function fF_Calc uses three input data (already mentioned before): $\epsilon$, $ID$ and $N_{Re}$. Back into the Excel sheet where the data is already placed try to use fF_Calc as a built-in function of Excel as follows,

=fF_Calc

Using the fF_Calc function. Notice that Excel already recognized the created function

You should find no problem. Excel should even complete the function name and the calculation of the friction factor $f_F$ should be straight.

Once, this function has been created, repeated calculations as for pipe networks, should be much more easy.

This is the end. I hope, you find this post very useful.

Any question? Write in the comments and I shall try to help.

Other stuff of interest

• LE01 - AC and DC voltage measurement and continuity test
• LE 02 - Start and stop push button installation 24V DC
• LE 03 - Turn on/off an 24V DC pilot light with a push button
• LE 04 - Latch contact with encapsulated relay for turning on/off an AC bulb light
• LE 05 - Emergency stop button installation
• About PID controllers
• Ways to control a process
• About pilot lights
• Solving the Colebrook equation

==========
Ildebrando.