Iterative Calculations

[CalvinWong]

Hi all,

I have a set value in cell B1 that will be changed constantly.

Cell B7 has a formula written based on the values of cells B3, B4, and B5. The values in cell B3 and B5 do not change, meaning they are constant and will always be 4 and 10, respectively. Is it possible for Excel to automatically match the value in B7 with the value in B1, and give me the value of cell B4? In other words, can Excel change B7 to "500" and give the according height (B4)?

I am looking to do this without Goal seek and I believe an iterative calculation is the way to go, but I'm not exactly sure.

This is the formula inside of B7: =(PI()*B3*B3-B3*B3*ACOS((B3-B4)/B3)+(B3-B4)*SQRT(2*B3*B4-B4*B4))*B5

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Volume
[/TD]
[TD]500
[/TD]
[TD]Cubic Feet
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Radius
[/TD]
[TD]4
[/TD]
[TD]Feet
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Height of Air
[/TD]
[TD]4
[/TD]
[TD]Feet
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Length
[/TD]
[TD]10
[/TD]
[TD]Feet
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Volume
[/TD]
[TD]251.327412
[/TD]
[TD]Cubic Feet
[/TD]
[/TR]
</tbody>[/TABLE]


I have Excel 2010.

Thanks!

 

[ndsutherland]

Without being a math genius, I think the best method is Excel's Solver Add-in. After installing the add-in you can install the vba reference library and paste the following code into your worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.Calculation = xlCalculationAutomatic
    If IsError(Range("B7")) Then Range("B4") = 4
    If Target.Address <> "$B$1" Then Exit Sub
    solverreset
    SolverOk SetCell:="$B$7", MaxMinVal:=3, ValueOf:=Target.Value, ByChange:="$B$4", Engine _
        :=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$B$7", MaxMinVal:=3, ValueOf:=Target.Value, ByChange:="$B$4", Engine _
        :=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True
End Sub

You will have to run the code manually once first, but this method is far more accurate than goal seek

 

[CalvinWong]

Without being a math genius, I think the best method is Excel's Solver Add-in. After installing the add-in you can install the vba reference library and paste the following code into your worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.Calculation = xlCalculationAutomatic
    If IsError(Range("B7")) Then Range("B4") = 4
    If Target.Address <> "$B$1" Then Exit Sub
    solverreset
    SolverOk SetCell:="$B$7", MaxMinVal:=3, ValueOf:=Target.Value, ByChange:="$B$4", Engine _
        :=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$B$7", MaxMinVal:=3, ValueOf:=Target.Value, ByChange:="$B$4", Engine _
        :=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True
End Sub

You will have to run the code manually once first, but this method is far more accurate than goal seek

Is there a solution without using the Solver Add-in?

 

[ndsutherland]

I believe only if you can algebraically solve

B7=(PI()*B3*B3-B3*B3*ACOS((B3-B4)/B3)+(B3-B4)*SQRT(2*B3*B4-B4*B4))*B5

for B4, then you could enable iterration, but solving for B4 is beyond me.

 

[joeu2004]

Is it possible for Excel to automatically match the value in B7 with the value in B1, and give me the value of cell B4? [....] This is the formula inside of B7:
=(PI()*B3*B3-B3*B3*ACOS((B3-B4)/B3)+(B3-B4)*SQRT(2*B3*B4-B4*B4))*B5

Is there a solution without using the Solver Add-in?

In simple terms, you want to know the height of a shape, given its radius, length and volume.

It might be helpful to know what the shape is.

For example, the volume of a cylinder is V = h*pi*r^2. So h = V/(pi*r^2).

I am having trouble imaging a shape that has both height and length as well as a radius.

 

[joeu2004]

PS.....

I am having trouble imaging a shape that has both height and length as well as a radius.

Well, a cone has length (of side), height and radius. But the volume is simply V = h*pi*r^2/3.

 

[CalvinWong]

I believe only if you can algebraically solve

B7=(PI()*B3*B3-B3*B3*ACOS((B3-B4)/B3)+(B3-B4)*SQRT(2*B3*B4-B4*B4))*B5

for B4, then you could enable iterration, but solving for B4 is beyond me.

Yes, I agree. Thanks for your help!

 

[CalvinWong]

In simple terms, you want to know the height of a shape, given its radius, length and volume.

It might be helpful to know what the shape is.

For example, the volume of a cylinder is V = h*pi*r^2. So h = V/(pi*r^2).

I am having trouble imaging a shape that has both height and length as well as a radius.

Sorry, I should've stated in my original question the shape of the formula. It is a cylinder with flat ends on its side. There is liquid filling up inside the cylinder. The "height of air" is how much air is left between the liquid and top of the cylinder.

 

[ndsutherland]

What is the formula in B7? It's obviously not the equation for a cylinder.