How to do colebrook equation in Excel
The Colebrook equation is used to calculate the Darcy friction factor for fluid flow in a pipe. The Darcy friction factor is a dimensionless quantity that measures the resistance to flow caused by the pipe wall roughness and the fluid viscosity. It’s an important parameter for designing and analyzing pipe systems, such as water supply, oil and gas transportation, or HVAC.
The Colebrook equation is a complex equation that involves iterative calculations because it’s implicit in terms of the friction factor. That means you can’t solve for the friction factor directly, but you have to use a trial-and-error method until you find a value that satisfies the equation. While you can use Excel to perform these calculations, it may require some effort to set up.
Here’s how you might implement the Colebrook equation in Excel:
Step 1: Set Up Your Excel Worksheet
In an Excel worksheet, create a table with columns for:
- Reynolds Number (Re)
- Relative Roughness (ε/D)
- Initial Guess for Friction Factor (f)
- Iterations (for tracking)
The Reynolds number is a dimensionless quantity that measures the ratio of inertial forces to viscous forces in a fluid flow. It depends on the fluid velocity, density, viscosity, and pipe diameter. The relative roughness is the ratio of the pipe wall roughness to the pipe diameter. It reflects how smooth or rough the pipe surface is.
Step 2: Define Your Inputs
Enter the known values into the appropriate cells. This includes the Reynolds number (Re) and the relative roughness (ε/D).
Step 3: Perform Iterative Calculations
You’ll need to set up an iterative calculation process because the Colebrook equation requires an iterative solver. Excel provides a built-in tool for this. Here’s a simplified outline of the steps:
- Go to “File” > “Options” > “Formulas”.
- Enable “Iterative calculation”.
- Set the maximum number of iterations (e.g., 100) and a small maximum change value (e.g., 0.0001).
In a cell (e.g., F2), you can start with an initial guess for the friction factor (f), often around 0.02.
In another cell (e.g., G2), use the Colebrook equation to calculate the friction factor. The formula might look like this (assuming cell A2 has Re and cell B2 has ε/D):
=IF(ISNUMBER(F2), 1 / (2 * LOG10(B2 / 3.7 + 2.51 / (Re * SQRT(F2)))), “”)
In the cell below (e.g., F3), you can use the following formula to update the friction factor based on the previous value and the calculated value in cell G2:
=IF(ISNUMBER(G2), (F2 + G2) / 2, “”)
Copy the formula in cell F3 down as many rows as necessary for convergence.
Step 4: Review the Result
After the iterations converge (the friction factor in column F stops changing significantly), you’ll have your Darcy friction factor.
Please note that the above approach is a simplified demonstration. The Colebrook equation can require more complex programming in Excel, especially for more accurate results and handling different scenarios.
Leave a Reply