I'm going to show you an advanced application

of a circular calculation or iterative solving.

Now, just by the very nature of iterative solving,

circular calculations in Excel,

they are life's solutions.

But I'm going to show you how we can implement

a circular calculation to solve a pretty advanced case study.

What I've got here is something known as The Colebrook equation.

The Colebrook equation relates the friction factor in Fluid Dynamics,

which is f here to the relative roughness.

The relative roughness is this ratio Epsilon over big D h,

and the Reynold's number which is Re.

This equation was specifically set up because it can be

solved nicely using iterative solving and back in the day,

several decades ago when computational power was not as advanced as it is now,

engineers would use this equation to solve for the friction factor.

It was basically just a correlation set up that is nice for iterative solving.

And the Colebrook equation can be used to set up

these diagrams known as moody diagrams or moody plots.

The math that I'm going to show you today can be used to create these moody diagrams,

which are used by a lot of engineers and

plumbers and other people who are studying fluid dynamics.

The Colebrook equation is set up,

so it can be solved using circular calculations one over root f,

we can replace that with just x.

So, I'm going to make this substitution that x equals 1 over root f,

because you notice that that's found on the left side,

and on the right side in this second term in the original equation.

So really, what we end up with is this x equals and f of x.

Whenever you can rearrange an equation to be x equal some function of itself.

You should think circular calculations,

and that's exactly what the Colebrook equation was set up to do.

Relative roughness is typically between 1 times 10 to the negative fifth,

and 1 times 10 to the negative 2.

The Reynold's numbers typically between 10,000 and 10 million,

at least that's what I'm going to assume in this analysis,

and the friction factor is generally between 0.008 and about 0.1.

So, I've set up my spreadsheet here.

I'm going to have a guess,

so we're going to guess x.

Now remember x is equal to 1 over the square root of f. So,

if I use a guess of f of about 0.01 and my

initial x is going to be equal to 1 divided by the square root of 0.01.

So, I'm just making an initial guess.

Now, the function of x, I'm just going to plug in this second equation.

So, I've plugged in this equation here in terms of my guess which is

x and the known

relative roughness which again relative roughness is Epsilon over D_sub_h,

and the Reynold's number,

so I've got this equation.

And right now, x does not equal f of x.

So we set up iterative solving to do that,

then I start the dog chasing its tail and I say.

x equals the outputs,

so the new input is equal to the output.

But they're all linked, and so you end up with this big circular calculation,

and I've already enabled circular calculations in

the file options formulas menu and we converge on a solution.

So this is x, this is not f. I'm going to use this equation over here to get

back out my f. So f is equal to 1 divide by x squared,

and when we do that we get a friction factor of about 0.02.

So, that means at a relative roughness of 0.001 and a Reynolds number of 1 million,

the friction factor is about 0.002.

Now, this is a live solution.

If I change the Reynolds number here to 1e7,

then this automatically updates,

it changed a little bit.

So let me go down to something like 1e4.

So, it changes the friction factor to 0.03.

And I can also change the relative roughness.

So maybe I wanted to change the relative roughness to 1e-2,

and it automatically updates the friction factor.

So, I want to show you how we could then set up a big table and

this table could be used to make that moody plot that I've showed earlier.

We're going to do a two way case study.

I've got Reynolds number here ranging from one to the fourth,

up to one to the seventh,

and I've got the relative roughness

between 1 times 10 to the negative fifth and 1 times 10 to the negative 2.

Now, we can set this up using a data table tool or a two way data table.

The upper left cell here has to be,

this is a reference for where on the spreadsheet,

I'm going to get the variable that's going to populate this table,

and that's the friction factor.

So that's equal to cell B5.

So, I just put a point to formula in there,

and then I'm going to highlight this entire region and I'm going to go into data,

what if analysis and data table tool.

And this has a role input vector that's the Reynold's number is the row input vector.

The Reynold's number goes into something known as the row input cell, that's cell B8.

We have a column input vector here,

which is the relative roughness.

The relative roughness goes into cell B7 of our case study that we've set up.

Now, when I press OK,

it's going to go through, for each combination of Reynolds number and relative roughness,

it's going to go through a bunch of rounds of iterative solving to calculate

the friction factor and it's going to put

the corresponding friction factor in the corresponding cell.

So, let's go ahead and click OK, and boom,

it goes through that very quickly and calculates

the friction factors at those different conditions.

Even though this is not Visual Basic per se,

it's an advance implementation of Excel using circular calculations,

and I think many of you will find this quite interesting.

Whenever you can set up your equations that you are trying to solve,

in terms of x equals f of x then you should

think circular calculations. Thanks for watching.