Here is a short presentation of how to create a user defined function in Google Sheets for the automated calculation of the friction factor $f_F$ from the Colebrok equation.
In comparison with the Micrsoft Excel option, the user defined function in Google Sheets can not contain loops for repetitive calculations. This means that a nested function needs to be prepared. This nested function will of course mimic the process presented in the post: Solving the Colebrook equation.
How to
Step #1
First, you will need the required or available data for the Colebrook equation: the Reynolds number, the pipe internal diameter and the pipe roughness. It is obvious that the function will get that data and produce the friction factor $f_F$.
Another way of putting it is providing the fluid velocity and the fluid properties, so that the Reynolds number can be estimated.
Step #2
Next, go to menu ribbon and click in Data and next in Named functions. This will pop up a small window.
 |
| Fig. 01 Access the menu to create a new function |
In this small window you will create your new function (as shown below).
 |
| Fig. 02 Window for user defined creation. |
Click in the option Add new function in order to start the process. You will see the following,
 |
| Fig. 03 User defined function step 2. |
Step #3
Let us now explain and fill the boxes to create our user defined function. In the box A, you will need to write the name of the function. In other words, invent a name. In our case, we choose: MYFF, as for my friction factor.
In box B, a short explanation is required. You put something like: This function estimates the friction factor from the Colebrook equation. No more is needed.
Box C requires more attention. You will need to declare the variables the function will work with:
- kinematic viscosity (nu),
- internal pipe diameter (id),
- fluid velocity (vel)
- pipe roughness (ep)
- and an initial value for the friction factor (oldff).
 |
| Fig. 04 Parameters to be used in MYFF. |
For each parameter you will need to hit enter so that another box appears to register another parameter. Take care of the names of the parameters since these are to be used in the upcoming formula below.
Step #4
In box D, you will introduce the forrmula. This formula was created by nesting 10 times the Colebrook equation. This looks as follows,
=1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(1/(2*LOG10(epsilon/3.72/id+2.51/(vel*id/nu)/sqrt(oldff)))^2)))^2)))^2)))^2)))^2)))^2)))^2)))^2)))^2)))^2)))^2)))^2For short, copy the above formula and paste it in box D. This is as follows,
 |
| Fig. 05 Formula. |
One this is done, hit the button Next.
Step #5
Another window should appear with the parameters and formula already processed into Google sheets. More empty boxes appear but the information asked is optional. You do not need to fill these boxes but if you want to give completeness, you can do it as it follows.
 |
| Fig. 06 Additional details on the parameters. |
After this, you are done. Hit enter and the user defined functions should be already created into Google sheets.
How to use MYFF
If MYFF was successfully created, you should be able to call it from any cell in the sheet. This is shown below,
 |
| Fig. 07 Calling MYFF from the sheet. |
Let us now use some data to check the function and see if it can calculate. This is shown below,
 |
| Fig. 08 Calculations with MYFF. |
In my case I usually forgot the order of the parameters and keep open the list of functions. Once all data is passed to MYFF, just hit enter and a numerical result comes up.
This is the end of the post. I hope you find it usefull.
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.
No comments:
Post a Comment