How to do colebrook equation in Excel

The Colebrook equation is used to calculate the Darcy friction factor for fluid flow in a pipe. It’s a complex equation that involves iterative calculations because it’s implicit in terms of the friction factor. 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)

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:

  1. Go to “File” > “Options” > “Formulas.”
  2. Enable “Enable iterative calculation.”
  3. 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):

See also  Creating Permutations in Excel

=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. Additionally, there are specialized software and online calculators available for this purpose.