How to calculate "Velocity at actual flow" without doing for each cell

[justmole]

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

 

[gbam]

justmole - I did not look at your spreadsheet but I recommend that you code the velocity computation in Visual Basic. I have coded HEC22 into excel using visual basic to iterate for velocity etc... If you are looking for partial flow velocities you can either compute normal depth using Manning's equation or Q/A for full flow conditions. The spreadsheet slows down if I have large systems but if you trigger the calculate manually then you can enter your data.

 

[IDS]

I agree with gbam, especially if this spreadsheet is one that you will be using over a long period.

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:

http://newtonexcelbach.wordpress.com/2009/07/25/using-goal-seek-on-multiple-cells/

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/

 

[CarlB]

A method I used is to directly calculate d given Q, or vice versa,from a lookup table that has values for the relationship for d/D, V/Vf,Q/Qf (along with interpolation).

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.

http://www.fwrj.com/TechArticle07/1207 FWRJ tech3.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.

 

[Drew08]

An alternate to VBA would be to turn on "Enable iterative calculations", (i.e., allow circular references) in your spread sheet. Find the Excel options dialog box (It's the windows icon at the top left in 2007, I don't remember where it is in early versions), select "Formulas" and check "Enable iterative calculations". Then, set up a line in another sheet, or add columns in the existing table for Area (A), Wetted Perimeter (P), Phi and depth (d); flow (Q) and Diameter (D) I assume are known. There are four equation and four unknowns that can be solved iteratively with a circular reference.

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.

 

[flash3780]

Is Excel the best tool for the job here? It seems like Matlab/Octave/Scilab, MathCAD or other tools would work better.

 

[IDS]

Is Excel the best tool for the job here? It seems like Matlab/Octave/Scilab, MathCAD or other tools would work better.

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/

 

[flash3780]

In what way?

I don't see anything in this problem that Excel an unsuitable tool to solve it.

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.

 

[IDS]

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.

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/

 

[flash3780]

In what way are they?

I should have known better than to make that statement on the spreadsheets forum. More an opinion than a fact, I suppose.

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.

 

[IRstuff]

Excel tends to win simply by virtue of the fact that most engineers are provided with MS Office, which comes with Excel, so it's essentially free. Mathcad and Matlab, on the other hand, are additional expenses, and somewhat pricey, relative to MS Office.

I use all 3, depending on the specific problem.

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[zdas04]

I'm with IRstuff (except that I haven't found anything the MatLab can do that MathCad doesn't do at least as well). Even in Excel, I name any cell I'm going to reference more than three times. I find it a lot easier to read an equation with "MW_C1" than 'Sheet 1!$c$6".

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

 

[flash3780]

Mathcad and Matlab, on the other hand, are additional expenses, and somewhat pricey, relative to MS Office.

Not sure about MathCAD, but there are quite a few free Matlab clones out there that are quite good. I currently use Octave with the QtOctave front end since our company only has a few Matlab licenses. It works great, even for some pretty intense calculations. It is, however, missing some of the more advanced or specialized functions that are available in Matlab, though. Matlab also tends to run a bit faster, but for what I do that generally doesn't matter much - I rarely have calculations that take over 10 minutes to run.

 

[JPLundberg]

Try this...

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.

 

[jmw]

Pardon my interruption but in a general case:

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:

The Excel “goal seek” function is useful and powerful, but the procedure for using it on multiple cells is painfully slow.

JMW

http://www.ViscoAnalyser.com

 

[gbam]

jmw - that is the intent of visual basic. Write a subroutine and call it from your spreadsheet to compute rather than the tedious parens and long equations to build into the cells.