In this post, a Python add-in was used to create a function to calculate the friction factor $f_F$ in Excel. Using Python in Excel allows us to solve more complex numerical problems. For the purposes of this post, the Anaconda add-in was installed.
 |
| Fig. 01 Installing the add-in for Python. |
Follow the steps presented in Fig. 01 to install the add-in for Python. Once, this has been installed, we may proceed to write our function for solving the Colebrook equation so that the friction factor $f_F$ can be known.
1 Solution to the Colebrook equation
The solution for $f_F$ in the Colebrook equation is based on the iterative way usually employed in engineering.
$\dfrac{1}{\sqrt{f_F}}=-2\log_{10}\left[ \dfrac{\epsilon}{3.7\, ID}+\dfrac{2.51}{N_{Re}\, \sqrt{f_F}} \right]$ Eq. (01)
where all variables are known already. As you may recall, Eq. (01) is solved for $f_F$ by iterating the following expression,
$f_F=\dfrac{0.25}{\left( \log_{10}\left[ \dfrac{\epsilon}{3.7\, ID}+\dfrac{2.51}{N_{Re}\, \sqrt{f_F}} \right] \right)^2}$ Eq. (02)
Equation (02) is suitable for a for loop in Python. For short, a simple Python program to solve Eq. (01) using the form in Eq. (02) can be as follows,
import mathidi = 4.09E-2
NRe = 34808.51
fFg = 0.1
for i in range(1, 20):
fF = 1/(-2*math.log(ep/3.72/idi+2.51/NRe/math.sqrt(fFg),10))**2
fFg = fFprint(fFg)
Notice, that in the code above the Reynolds number $N_{Re}$ is known a priori, so that it should be previously calculated. Test the code above and see how it works.
2 Using Python in Excel
However, from the point of view of pre-existing calculations in Excel and the data already estimated some other place in the spreadsheet, you would desire your Python code takes the information from a given spreadsheet cell. So, how do we do that?
If the data is in a cell, you need to use the REF command in your code. For the code above, it would look like,
import mathep=REF("B4")
idi=REF("B5")
NRe=REF("B8")
fFg = 0.1
for i in range(1, 20):
fF = 1/(-2*math.log(ep/3.72/idi+2.51/NRe/math.sqrt(fFg),10))**2
fFg = fF
fFg
In this case, the data is stored in cells B4, B5 and B8. In order to make this code work you need to paste this code in the Anaconda console. First, place the cursor in the cell where you want the output to be placed and then hit in the button + Create code cell.
 |
| Fig. 02 Creating a code cell. |
Anaconda will ask to confirm that the cell you are wishing to place your code, and the output of course. In our case, the cell in question is B9. After this is done, an empty console to introduce the code should appear. Paste the code above there. It should look as in Fig. 03.
 |
| Fig. 03 Code for calculation of $f_F$ and output. |
Once you have paste your code hit the green triangle button in the up right corner above the console. This button executes the code and if everything is right the corresponding $f_F$ should appear in B9.
Notice, that in order to Python do its job the corresponding data in cells:B4, B5 and B8, must be already there. Otherwise, an error will pop up.
As final comments, Python can be used for the solution of more complex mathematical problems from data in Excel making it more powerful. However, you need to learn how to code Python. Follow the link below to download an Excel file showing how to perform the presented calculation,
Many more features are available from Anaconda but these are left to the reader to discover. I hope you could find this post useful.
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.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
- Estimation of the friction factor $f_F$ using an Google Sheets user defined function
- Estimation of the friction factor $f_F$ using an Excel user defined function
==========
Ildebrando.
No comments:
Post a Comment