Calculate using 1 cell to left of found cell

[rickh1001]

I need to find a minimum value in a short list of numbers, then use that number, and the one to its left, in a complex scientific calculation. For example

J K
0.30 1.17
0.35 1.14
0.40 6.25
2.50 1.20


Assume these values are in cells J17:J20 and K17:K20.

I would find the minimum value of 3.0, then in a fairly complex calculation, use both 3.0 and its associated value 1.8.

I've tried the CELL INDEX and MATCH functions without much success. The calculation is moderately complex with sums and squared terms, so I just end up with a hideously long statement that always gives me an error.

I can get the cell address of the minimum in K18 to display in a cell as follows:

=(CELL("address",INDEX($K$17:$K$20,MATCH(MIN($K$17:$K$20),$K$17:$K$20,0))))

The equation I need to solve, using the minimum found in the K column, with its associated J value in the same row, would be = (k+1/k)^2 * (j/j-1)^2 where k and j are the values found in the cell addresses.

I know this is not too hard for you experienced users, but it is giving me problems. I have been trying to settle to just find the cell address of the minimum, which I can do now (cell K18), then display the value of the cell to its left, then use them in the final calculation. However, I can't seem to use just the displayed cell address of K18 in any calculation, so this divide and conquer approach hasn't worked.

I needed to get this done to complete a project and deadline, and it is holding me up. Sorry for my stupidity on this, but any help would be appreciated.

[AliGW]

Maybe this?

=INDEX(K$17:K$20,MATCH(MIN(J$17:J$20),J$17:J$20,0))

[kvsrinivasamurthy]

Final formula is

Please Login or Register  to view this content.

[rickh1001]

Thanks to both of you and taking the time. This is a great help.

The equation by kvsrinivasamurthy is close, but is not giving the correct answer. It must be an inversion or something. From the data in the original post, k=1.14 and j=0.35. On a calculator, here is what I get:

((1.14/(1.14-1))^2 = 66.31 and for the second term, ((0.35/(0.35+1))^2 = 14.88

Multiplied together, I get 986.5.

When I put the above formula into the spreadsheet and checked that it is referencing the correct cells, it is producing 0.752.

I'll keep checking, and thanks again so much for the help. On these long equations, I fall asleep halfway through checking the parentheses, so I am guessing one of them is misplaced.

[JeteMc]

Posts #1 and #4 have some differences that make it hard for me to give a solution.
Post #1 states that the final product will be of the form: (k+1/k)^2 * (j/j-1)^2
Post #4 seems to state that the final product will be of the form: (k/k-1)^2*(j/j+1)^2
Assuming that the formula in post #1 is correct I have placed the two parts of the equation in different cells on row 17.
The first part, in M17, reads:

Please Login or Register  to view this content.

The second part, in N17, reads:

Please Login or Register  to view this content.

The product is in O17.
I would suggest that you run the Evaluate Formula feature for M17 and then N17. M17 will evaluate to (2.14/1.14)^2 and N17 will evaluate to (0.35/(0.35-1))^2 both of which I believe are consistent with the information in the first post.
Let us know if you have any questions.

[rickh1001]

I messed up what I stated in the first post, and apologize.

In the first post, I needed to find the minimum of the k column, which is 1.14.

From that, I need to use the associated j column value of 0.35.

Then, the formula I need to calculate is as I showed in post 4,

((1.14/(1.14-1))^2 = 66.31 and for the second term, ((0.35/(0.35+1))^2 = 14.88

Multiplied together, I get 986.5.

JeteMc, I still can't seem to get this value. I apologize for being so dense, and for the inaccurate first post.

[JeteMc]

The first formula would be: =(MIN(K$17:K$20)/(MIN(K$17:K$20)-1))^2 which yields 66.306...
The second formula would be: =(INDEX(J$17:J$20,MATCH(MIN(K$17:K$20),K$17:K$20,0))/(INDEX(J$17:J$20,MATCH(MIN(K$17:K$20),K$17:K$20,0))+1))^2
As you'll see if you run the evaluate formula feature that does evaluate to ((0.35/(0.35+1))^2 however the final result is 0.067215364
This makes sense because the denominator (1.35) is larger than the numerator (0.35) so the square of the quotient will be less than one.
Let us know if you have any questions.

[kvsrinivasamurthy]

The second part is not

(J/J+1)^2

It is

(J+1/J)^2

To get your calculated value 14.88

As per that formula is

Please Login or Register  to view this content.

[rickh1001]

kvsrinivasamurthy,

Thanks so much! Your solution was correct, and the spreadsheet worked out perfectly.

[kvsrinivasamurthy]

Welcome to you.