Prev: multiple cells are often selected when I click in only one cell
Next: IF formula not working
From: Edu on 31 Mar 2010 14:33
Hi guys,

Here's a challenge I hope you can help me solve.

Say I have a table of values with X and Y headers and a 3 x 3 array
hoding parameters that are a function of X and Y, call these f (X, Y)
as below.

Y
0.1 0.2 0.3
1 10 15 20
X 2 15 20 30
3 20 30 40

Say I am given a value of Y = 0.2 and the outcome of the X and Y, f
(X, Y) of 20, and the formula needs to return the value of X.

From the table above the result I would want to extract is X = 2. The
formula needs to look down the second column (Y=0.2) until it finds f
(X,Y) = 20. Sounds easy but I'm struggling. I generally use tables
where X and Y are known and the value of f(X, Y) is required, but I am
finding this trickier.

Please assume the values are always exact matches. I may require
bilinear interpolation if the numbers are not exact - but I can do
that myself.

Any suggestions are greatly appreciated.

Thanks
Edu
From: Thanks on 31 Mar 2010 16:05
Provided that your (X,Y) values are constant with :

Column labels in B1:D1 and Row labels in A2:A4,
(X:Y) Values in B2:D3
your X Value in G1 and your (X,Y) in G2.

=SUMPRODUCT((B1:D1=G1)*(B2:D4=G2)*A2:A4)

The formula will return a 0 if the value is not found. You may want to add
data validation to restrict entries for G1 and G2


"Edu" wrote:

> Hi guys,
>
> Here's a challenge I hope you can help me solve.
>
> Say I have a table of values with X and Y headers and a 3 x 3 array
> hoding parameters that are a function of X and Y, call these f (X, Y)
> as below.
>
> Y
> 0.1 0.2 0.3
> 1 10 15 20
> X 2 15 20 30
> 3 20 30 40
>
> Say I am given a value of Y = 0.2 and the outcome of the X and Y, f
> (X, Y) of 20, and the formula needs to return the value of X.
>
> From the table above the result I would want to extract is X = 2. The
> formula needs to look down the second column (Y=0.2) until it finds f
> (X,Y) = 20. Sounds easy but I'm struggling. I generally use tables
> where X and Y are known and the value of f(X, Y) is required, but I am
> finding this trickier.
>
> Please assume the values are always exact matches. I may require
> bilinear interpolation if the numbers are not exact - but I can do
> that myself.
>
> Any suggestions are greatly appreciated.
>
> Thanks
> Edu
> .
>
 | 
Pages: 1
Prev: multiple cells are often selected when I click in only one cell
Next: IF formula not working