Recursion formula?

[Rwatson]

I'm looking for a way to change variables based on themselves. I know there are recursion formuals that might do the trick but can't figure out how to do it.


I have the length, width and height of a box. The height is based of another formula and then the three dimensions are multiplied together to determine the box volume. That volume is then divided by 1000 to convert units. The converted measurements along with the volume of the items in the box are used in a formula to determine a rating. I'm looking to make the left most rating equal to 1 (the column labeled <1). In the past i used "solver" to find the best fit for the length and width but now im looking for a way for it to be determined automatically. If there is a formula that can do this that would be great, i'm trying to stay away from macros if i can.


In the row labeled "original" i have the box dimensions as they currently stand. In the row labeled reduced, i used solver to find the new dimensions that would give me a rating of one in cell I7. If you could come up with a formuala that would reproduce the results for the length and width for the "Reduced," row that would be great.


Thanks


https://docs.google.com/spreadsheet/ccc?key=0AgD4ZdWTKF8qdEZFamdBLXVxUVdGb295dW9jaHpoY2c&hl=en#gid=0

 

[Hui]

Rwatson


Your file isn't available as it isn't marked for Public access

 

[Rwatson]

sorry about that, that always seems to happen when using google docs for me. This is the updated URL that should have no protection on it.


Thanks again


https://docs.google.com/spreadsheet/ccc?key=0AgD4ZdWTKF8qdEZFamdBLXVxUVdGb295dW9jaHpoY2c

 

[NARAYANK991]

Hi Watson ,


I don't think recursion is required here , provided my assumptions are correct.


1. I assume that you always want a value of 1 in I7.


2. I assume that H7 is a constant which is manually entered.


3. I assume that the formula you have given in I7 can be used to calculate G7 if we take (1) above for granted i.e. if :

I7 = G7 / ( 2.7 x H7 ) ,


then if I7 = 1.00 , G7 = 2.7 x H7 , and since H7 is a manually entered constant , G7 can be calculated from this formula.


4. F7 = G7 x 1000.


5. Since E7 is the same as E1 , and it is a constant , then the product of L x W = F7 / E7.


6. Now , if you assume that L can take the value given in C5 , then the new value of W can be calculated by dividing the product obtained in (5) above by C5 ; alternatively , if you assume that W can take the value given in D5 , then the new value of L can be calculated by dividing the product obtained in (5) above by D5.


Narayan