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.

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.

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.

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.