Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solving for a variable multiple times
Hey all,
I'm creating a worksheet that helps me determine water depth in a lagoon. I want to calculate the water depth from month to month for 12 years (144 Calculations of depth). The formula is Volume = X*Y*H +18*h^3+3*X*H^2 +3*Y*H^2 X=lagoon floor depth (user defined) Y=lagoon floor width (user defined) H= water depth The volume changes each month due to evaporation, seepage, and inflow. My spread sheet calculates the volume at the end of the month. The next step would be to solve for H using the formula above. I can use goal seek to solve for H starting at the first month and then doing the same for the following month, but I don't want to do this 144 times. I created a macro that automates the goal seek routine for each month, but how do I automate the macro to do this for every month . I need the macro to look at the next row down and repeat the goal seek routine until it hits the end of the worksheet. Any help is appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solving for a variable multiple times
Is small "h" a different variable than "H" ? (as in 18*h^3).
My spread sheet calculates the volume at the end of the month. Are you given "H" to begin with in order for the equation to calculate Volume? Or do you mean that you are given the Volume by some other means, and you wish to calculate 'H', the water depth? -- Dana DeLouis Win XP & Office 2003 "Pmpkin" wrote in message ... Hey all, I'm creating a worksheet that helps me determine water depth in a lagoon. I want to calculate the water depth from month to month for 12 years (144 Calculations of depth). The formula is Volume = X*Y*H +18*h^3+3*X*H^2 +3*Y*H^2 X=lagoon floor depth (user defined) Y=lagoon floor width (user defined) H= water depth The volume changes each month due to evaporation, seepage, and inflow. My spread sheet calculates the volume at the end of the month. The next step would be to solve for H using the formula above. I can use goal seek to solve for H starting at the first month and then doing the same for the following month, but I don't want to do this 144 times. I created a macro that automates the goal seek routine for each month, but how do I automate the macro to do this for every month . I need the macro to look at the next row down and repeat the goal seek routine until it hits the end of the worksheet. Any help is appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solving for a variable multiple times
Hows 'bout this?
count = 0 intNumRows = Worksheets("Sheet1").UsedRange.Rows.Count While counter < IntNumRows var = yourFormula count = count + 1 Cells(count,"D") = var Wend Pmpkin wrote in message ... Hey all, I'm creating a worksheet that helps me determine water depth in a lagoon. I want to calculate the water depth from month to month for 12 years (144 Calculations of depth). The formula is Volume = X*Y*H +18*h^3+3*X*H^2 +3*Y*H^2 X=lagoon floor depth (user defined) Y=lagoon floor width (user defined) H= water depth The volume changes each month due to evaporation, seepage, and inflow. My spread sheet calculates the volume at the end of the month. The next step would be to solve for H using the formula above. I can use goal seek to solve for H starting at the first month and then doing the same for the following month, but I don't want to do this 144 times. I created a macro that automates the goal seek routine for each month, but how do I automate the macro to do this for every month . I need the macro to look at the next row down and repeat the goal seek routine until it hits the end of the worksheet. Any help is appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solving for a variable multiple times
I may have it wrong because it's a cubic equation, but see if this custom
function might work instead of Goal Seek. I assume 'h' is the same as "H", and that you are given Volume, x,y, and wish to find H. Function WaterDepth(V, x, y) Dim t1, t2 t1=x^2+y^2-7*x*y t2=(162*V-t1*(x+y)+3*Sqr(3)*Sqr(972*V^2-12*t1*V*(x+y)-x^2*y^2*(t1+x*y)))^(1/3) WaterDepth=(t1+t2^2+3*x*y-t2*(x+y))/(18*t2) End Function HTH -- Dana DeLouis Win XP & Office 2003 "Dana DeLouis" wrote in message ... Is small "h" a different variable than "H" ? (as in 18*h^3). My spread sheet calculates the volume at the end of the month. Are you given "H" to begin with in order for the equation to calculate Volume? Or do you mean that you are given the Volume by some other means, and you wish to calculate 'H', the water depth? -- Dana DeLouis Win XP & Office 2003 "Pmpkin" wrote in message ... Hey all, I'm creating a worksheet that helps me determine water depth in a lagoon. I want to calculate the water depth from month to month for 12 years (144 Calculations of depth). The formula is Volume = X*Y*H +18*h^3+3*X*H^2 +3*Y*H^2 X=lagoon floor depth (user defined) Y=lagoon floor width (user defined) H= water depth The volume changes each month due to evaporation, seepage, and inflow. My spread sheet calculates the volume at the end of the month. The next step would be to solve for H using the formula above. I can use goal seek to solve for H starting at the first month and then doing the same for the following month, but I don't want to do this 144 times. I created a macro that automates the goal seek routine for each month, but how do I automate the macro to do this for every month . I need the macro to look at the next row down and repeat the goal seek routine until it hits the end of the worksheet. Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sum all values of a variable that's repeated multiple times | Excel Worksheet Functions | |||
Solving multiple equations | Excel Worksheet Functions | |||
Solving for multiple algebraic variables | Excel Worksheet Functions | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Multi-variable Equation Solving | Excel Programming |