This attachment is for others test my solutions and test it's speed. On those last postings I showed how my solution works step by step.
In those spreadsheets you enter Right side of the selected equation with the Reynolds number and relative
roughness and a "guess" for the "1/sqrt(f)" variable. I suggested 3 for a guess so that the initial results would be the same as mine, just so you could see it you had the equation right. The next step was to copy that initial cell to a cell below and revise the "3" with a "point and click" to the result of the first cell. The next was to copy that revised second cell down about 20 more cells. The results of those cells converge to the value of 1/sqrt(F). Then last step was to divide 1 by the square of that repeating value of 1/sqrt(F), and then you will have the utmost accurate result for Excel, just because Excel can go beyond the 15 or 16 decimal place.
If you had to do this for many different pipes, that would take quite some time. But this manual method was just to demonstrate how my solution works. I did not explain the math, except to those that asked why this works. To explain it simply, understand that an accurate result will be 1/sqrt(F) = 1/sqft(F), But the right side of the equation has other variables and constants and even a Log function. Those different parts including the Log function are the path to the solution.
The guess we entered is just a small part To see what I mean by "small part", try this. Use 300 instead of the 3... one hundred times bigger. How much difference did that make in the result? If you were using Re=200,000 and Rr=0.4 the 3 gives 3.9 and the 300 gives 3.6. That means those other parts of the right side of the equation are pointing us toward the solution. Think of those parts as a funnel pointing toward 1/sqrt(f). Our guess might be off a lot, but the funnel is pointing us toward the correct solution of 1/sqrt(F). If you keep using the result as you next guess you will see the steps are getting smaller and smaller, until the change becomes zero.
Some people say this is not a solution. I don't argue, but I know it is just like the fraction 100/3. What is answer of that division? You might say that it is 33.33, but it is just like long division, One more step would be 33.333. That's closer, but how close do you need to be? Using Excel, you would get 33.333333...a maximum of fifteen 3's. Each step takes you closer and closer... Just like my "solution".
My solution is so simple any programmer could write the code to do the looping until the result becomes nothing, or so small that Excel can't go any further. But how time is needed? Test my code's speed.
Download this "Test 10000.xls" file and you can see how fast it can do 10,000 different solutions... I also added about a dozen other public approximations so you can compare their answers to mine. Also you can enter your own variables of Re and Rr and it will solve those. It should also work in Excel '97, but I don't have Excel 97 to make sure.
Attached Files

Test 10000.xls (947.5KB)
downloads: 1077
Serghide’s Solution.
A = 2 log_{10}[(RelRough / 3.7) + (12 / Reynolds#)]
B = 2 log_{10}[(RelRough / 3.7) + (2.51*A / Reynolds#)]
C = 2 log_{10}[(RelRough / 3.7) + (2.51*B / Reynolds#)]
f = (A(BA)^{2} / (C(2* + A))^{2}