Using x&y matrix values to display a result in s/sheet

[hoinvip]

Hello,

Can anyone help me with this one please?

I have a matrix as follows:

On the X and Y axis', I have a grading: 1=very low, 2=low, 3=medium, 4=high, 5=very high.

The matrix itself gives a set of arbitrary results so:

Y 1 2 3 4 5
X
1 1 1 2 3 4
2 1 2 3 4 4
3 2 3 4 4 5
4 3 4 4 5 5
5 4 4 5 5 6

Now, what I want to do, is have a column for both X and Y values in another worksheet, with a number of categories assessed against them. I then want to calculate the result automatically by using the data held in this table to present a set of conditionally formatted results.

What I need to understand is HOW I can get the matrix values compared with the equivalent (X&Y) columns in the worksheet and then to set the value in that sheet based on the result from the matrix.

Any suggestions please?

Thanks in advance


HoinviP

http://www.global-net.co.uk

 

[xlbo]

HOW is the data laid out in the "other" sheet ?

HOW is the data related to your "XY Matrix" ?

As a starter for 10, I would suggest looking at the INDEX 7 MATCH formulae...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[hoinvip]

Geoff,

Thanks for the swift reply.

The other sheet is a "traditional" table... i.e.

id, x axis value, y axis value, resulting value
-----------------------------------------------
01, 1 , 4 , 3
02, 2 , 3 , 3
03, 1 , 5 , 5

What I'd like to do is get the sheet to (automatically) check the matrix results based on X&Y and give me the answer to the resulting value column if I can.

The XY Matrix is purely a place to store the results as a "ready reckoner" (so to speak) and I just want to look-up the result.

Thanks!


HoinviP

http://www.global-net.co.uk

 

[xlbo]

You can use

=OFFSET(StartCell,MATCH($B2,XValues,0),MATCH(C2,YValues,0),1,1)

Where the xvalues are in cells A2:A6 and the range is named "XValues". The yvalues are in cells B1:F1 and the range is named "YValues". The cell to the top left of the matrix is named "StartCell"

You would enter the formula in place of where you have indicated the "3" result for "01"

Hope this makes sense

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[hoinvip]

Geoff,

This is immensely helpful thank you. I have read around the topic of OFFSETs now and understand it a bit better. However, I am still having a problem with getting it to actually work in my spreadsheet.

Perhaps I am coding it wrongly but I'm assuming I need to set the "xvalues" and "yvalues" fields as ranges in the sheet?

Can you advise please?

TIA,


HoinviP

http://www.global-net.co.uk

 

[xlbo]

Yes - you need to set up 3 range names:

XValues

This name would apply to the data in cells under the "X" in your original post ie runs vertically down the worksheet for 1 column, covering all the possible X Values

YValues

As per above but would run horizontally from after your "Y"

StartCell

This named range would refer to one cell only - the one in the top left of your data set.

As I imagine it, StartCell would be (for the sake of argument) cell A1

XValues would run from A2:A6 (as per your initial description of values)
YValues would run from B1:F1 (as per your example set of values)

Based on that layout, the formula should work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question