Help with extracting data entries from a Cartesian Product table.

[azreal64]

Hello everyone,

I was wondering if I could get some help with an issue I've been stuck on for a while.

Basically, I'm trying to a create a spreadsheet in Excel 2007 that will extract data based on a series of inputs from a Cartesian Product table and interpolate between the points. I can't figure out though how to extract the correct data entries.

My spreadsheet works like this: I have a fixed array (16x4) with columns W, X, Y, and Z. The Z column is the important one since that's what going to be interpolated later. The user is going to input numbers for W, X, and Y and the spreadsheet needs to extract the correct data set from the array.

So for example, if the user inputs 225/31/13, the spreadsheet will find the closest entry that's less than the stated inputs, which would be 200/30/10/7.

This can be done using formulas or VBA, doesn't matter. The ultimate goal is to expand this process to 7 other cases, but that will come later.

I've attached a screenshot of my spreadsheet.
http://i.imgur.com/tvfuAmQ.jpg

Any help is appreciated. Thanks.

 

[oldbrewer]

jargon to me and others I suspect - what is a cartesian product, what are the rules for interpolation - are the 15 rows under the user input row fixed a bit like a lookup table ?

 

[Rick Rothstein]

So for example, if the user inputs 225/31/13, the spreadsheet will find the closest entry that's less than the stated inputs, which would be 200/30/10/7.

Why isn't that 7 an 8?

 

[azreal64]

jargon to me and others I suspect - what is a cartesian product, what are the rules for interpolation - are the 15 rows under the user input row fixed a bit like a lookup table ?

Apologies. A Cartesian product (from what I've read) is a table representing all possible combinations of a set of numbers. The interpolation work has yet to be done, but I think I can do it fairly easily. And in the final product, the "Inputs" section and "Interpolation Data" sections will be on a separate page, but for now I figured it's simpler this way.

Why isn't that 7 an 8?

Try and think of it this way. The W/X/Y data are all inputs, the Z data is an output which will come from a computer simulation program, and the spreadsheet's job is only to match the input data, not the output so that the Z data can be interpolated.

So for example, you can see that the W column contains two different inputs, 200 and 250. But what if you wanted to know what Z was with W/X/Y = 205/30/10? What would Z be then?

Does that help clarify at all?

 

[MarcelBeug]

Possibly you are looking for the following.

This array formula in J12 will find the last row where W,X and Y values in column A,B and C are less than or equal to the inputs in G3, H3 and I3, confirm with CTRL-SHIFT-ENTER, not just ENTER:

=INDEX($D$3:$D$18;MAX(IF((($A$3:$A$18)<=$G$3)*(($B$3:$B$18)<=$H$3)*(($C$3:$C$18)<=$I$3),ROW($D$3:$D$18)-ROW($D$3)+1)))

Alternative, if X and Y values will always be less than 1000, array formula confirm with CTRL-SHIFT-ENTER, not just ENTER:

=INDEX($D$3:$D$18,MATCH(1000000*$G$3+1000*$H$3+$I$3,1000000*$A$3:$A$18+1000*$B$3:$B$18+$C$3:$C$18))

To find the W,X and Y values corresponding to the Z value found, enter in G12 and copy to the right to H12 and I12:

=INDEX(A$3:A$18;MATCH($J$12,$D$3:$D$18))

Otherwise I agree with Rick and would expect 8 in the example.
I guess there is some mistake with duplicate W,X and Y combinations in the data table in the example?

Would this be what you are looking for?

 

[azreal64]

Yes, I just noticed the mistake that I have a few duplicates and that it should be 8, not 7 in my example. Also, MarcelBeug, your 2nd formula works, but the 1st and 3rd ones give me errors.

 

[MarcelBeug]

Apologies, my original formulas are in Dutch and with a ";" as delimiter instead of "," and I see I didn't adjust them all.

Corrected formulas:

=INDEX($D$3:$D$18[COLOR="#FF0000"][B],[/B][/COLOR]MAX(IF((($A$3:$A$18)<=$G$3)*(($B$3:$B$18)<=$H$3)*(($C$3:$C$18)<=$I$3),ROW($D$3:$D$18)-ROW($D$3)+1)))

=INDEX(A$3:A$18[COLOR="#FF0000"][B],[/B][/COLOR]MATCH($J$12,$D$3:$D$18))