How to calculate "Velocity at actual flow" without doing for each cell
How to calculate "Velocity at actual flow" without doing for each cell
(OP)
Hi
I have spreadsheet for hydraulic analysis (see attached) and uses goal seek to calculate φ in sheet 2. However this is proving to be along process when number of MH increases. i need a way of incorporating how to calculating "Velocity at actual flow" from sheet 2 to sheet 1 in column AA without everytime cutting and pasting the answer from sheet 2 to sheet 1.
Regards
Tshepo
I have spreadsheet for hydraulic analysis (see attached) and uses goal seek to calculate φ in sheet 2. However this is proving to be along process when number of MH increases. i need a way of incorporating how to calculating "Velocity at actual flow" from sheet 2 to sheet 1 in column AA without everytime cutting and pasting the answer from sheet 2 to sheet 1.
Regards
Tshepo





RE: How to calculate "Velocity at actual flow" without doing for each cell
RE: How to calculate "Velocity at actual flow" without doing for each cell
An alternative would be to record one "loop" of the backsolve and copy/paste process with the macro recorder, then put a for/next loop around it to automate the process.
A third alternative would be to use the method shown here:
ht
with some rearrangement of your spreadsheet layout.
Whichever way you go, some coding will be required.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: How to calculate "Velocity at actual flow" without doing for each cell
Another benefit of this, it incorporates the not-so-well-known phenomenon that n-value is not constant with depth.
This article mentions some errors/limitation of Manning's equation.
www.fwrj.com/TechArticle07/1207%20FWRJ%20tech3.pdf
This commentary (can be Google/downloaded)on an article (which I haven't found) shows n variation as reported by different authors.
Discussion of "Explicit solutions of the Manning equation for partially filled circular pipes" Tommy S.W. Wong.
RE: How to calculate "Velocity at actual flow" without doing for each cell
Q = 1/n A^(5/3)/ P^(2/3) S^(1/2)
A = D^2 [Phi - sin(Phi)]/8
Phi = 2 P/D
Phi = 2 Cos-1(1-2d/D)
A trick I uses to provide more control and a more stable iteration is to add a "reset" feature such as an if statement that links to a cell with "y" or "n". If the cell is "y", iterations continue, if "n", then one of the unknowns is set to an assumed value that "resets" the process.
RE: How to calculate "Velocity at actual flow" without doing for each cell
RE: How to calculate "Velocity at actual flow" without doing for each cell
In what way?
I don't see anything in this problem that Excel an unsuitable tool to solve it.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: How to calculate "Velocity at actual flow" without doing for each cell
It's not that you can't do it, obviously. But, the other tools I mentioned are much easier to debug your calculations in, and for a third-party to check your work in.
RE: How to calculate "Velocity at actual flow" without doing for each cell
In what way are they?
For me, it is much easier to debug my calculations in Excel. That's because I have little knowledge of the other packages, but even if I did know them inside out I don't see any way in which they are inherently easier to de-bug. If anything, I'd say Excel was easier to de-bug.
As for checking, it should be done by an independent calculation anyway.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: How to calculate "Velocity at actual flow" without doing for each cell
I think that, for example, Matlab is easier to debug or check because you tend to use descriptive variable names rather than cell references. Matlab also gives the user great debugging information if something isn't coded properly, and generally points the user to the correct location in the code. The argument could also be made that Matlab is better at iterative analyses than Excel.
Also, once you have confidence in a routine, you can easily reference it as a subroutine to another calculation. Perhaps the flow velocity calculation referenced in the above post could be used for a larger flow-network analysis, for example. One could even compile a stand-alone program to perform said calculations if so desired.
MathCAD might be considered easier to debug because the equations are shown on the screen much in the way that you would write them on paper, or reference them in a textbook. The user can organize their calculations visually on the screen. I'm not a MathCAD expert, but I've been able to review calculations done by others in MathCAD without difficulty. This has proven handy, especially if I'm using someone else's calculation as a boundary condition to my own.
At the end of the day it's a matter of preference, but the tools are out there, and some of them are quite good. I don't mean to demean Excel, either. I think that it's probably the single best piece of software that Microsoft makes. I just find that, rather than writing macros to bend Excel into submission, it's easier to move to an analysis tool with different capabilities.
RE: How to calculate "Velocity at actual flow" without doing for each cell
I use all 3, depending on the specific problem.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: How to calculate "Velocity at actual flow" without doing for each cell
What kills me doing math in Excell are the parentheses. I was working on a sheet last week where there was a problem with the position of one of 19 closing parens. Finding that one took way too long. The same equatino in MathCad was quite readable and a a good check on the fact that the Excel equation had an error.
I try not to use a cresent wrench for a hammer or my tounge for a voltage tester. I feel the same way about software--I try to use the mix of programs that requires the least of my effort. I see way too many spreadsheets that should have been Access databases or MathCad worksheets. I regurlarly dump an Access query into Excell do a simple repetitive calc (like taking monthly production rate and developing cumulative production) and then stick the result back into Access. I find that the 5 minutes that turnaround takes saves me an hour of intermediate table hell. I nearly always start a new calculation in MathCad to debug the unit conversion and get an answer I trust before starting on Excel.
David
RE: How to calculate "Velocity at actual flow" without doing for each cell
RE: How to calculate "Velocity at actual flow" without doing for each cell
Sub Vel()
'
' Vel Macro
'
For i = 1 To 38
Sheets("Design Velocity Check").Cells(1, 2) = i
Range("C11").GoalSeek Goal:=0, ChangingCell:=Range("B9")
Sheets("IMQS-Upgraded").Cells(5 + i, 27) = Sheets("Design Velocity Check").Cells(9, 2)
Next i
End Sub
It might not do exactly what you want but you can tinker with it and get it right. You'll run into problems if your table isn't filled out completely.
RE: How to calculate "Velocity at actual flow" without doing for each cell
My spreadsheets tend to be a pain to produce because I need to allow the user to enter multiple sets of data and then use the same calculations time and again.
Because there may be different calculation options the cell equations tend to be quite involved with lots of IF statements.
It really is time consuming to set up the equations, fault find and then modify them with $ signs to extend the cells across and down the page.
It would be very nice to write out one set of equations and then simply allow the equation to function on any cells in the data entry columns and provide the answers.
If this is what Goal seek does, great...but will I gain anything:
JMW
www.ViscoAnalyser.com
RE: How to calculate "Velocity at actual flow" without doing for each cell