picking from a matrix

  • Thread starter Thread starter M.Siler
  • Start date Start date
M

M.Siler

I have a small matrix -

1 2 3 4 5
A 5 2 7 1 8
B 1 4 8 2 3
C 7 2 5 1 9


I have used Data Validation List to give me a pop-down list in two cells A1
& B1. A1 would have 1,2,3,4,5 and B1 would have A,B,C If in C1 I have 3
How would I write the formula to multiply C1 (3) with then number in the
matrix that corresponded from A1 & B1. For Example: if I selected 2 from
the list in A1 and C from the list in B1 the math should be 2 * 3 = 6 there
3 is from C1.
 
Hi there,

I can't see where "B1" should be included in the formula, but even though
you're selecting A1 from a validation list, you can still just do a simple
multiplication such as =c1*a1. The only thing is that until the user
selects a value from the list in A1 this formula will equate to zero because
A1 is effectively empty, but once they select from the dropdown the formula
will perform the calculation and reutnr the value, in your example A1 =2, so
it would return 6.

HTH,
Katherine
 
Let me try to explain better with a different example - forget my first
example

A1 = B1 = 1 C1 = 2 D1 = 3
A2 = 1 B2 = .1 C2 = .4 D2 = .7
A3 = 2 B3 = .2 C3 = .5 D3 = .8
A4 = 3 B4 = .3 C4 = .6 D4 = .9

This is the matrix. In A6 I have a Validation list with a range of
=$A$2:$A$4 (The Y axis of the matrix) In F1 I have a Validation list with a
range of =$B$1:$D$1 (The X axis of the matrix)

If F6 I would like result of what is select from the the two lists.

Example 1: If in A6 I selected 2 from the list and in F1 I selected 3 from
the list I would like F6 to display .8

Example 2: If in A6 I selected 1 from the list and in F1 I selected 2 from
the list I would like F6 to display .4

Hope the this explains it a little better.
 
.... try this: put in F6
=INDEX(B2:D4;A6;F1)

--
regards/pozdrav!
Berislav

ROT13 e-mail address
*******************************
 
Glad to work! Instead of "," we use ";" !!!

--
regards/pozdrav!
Berislav

ROT13 e-mail address
*******************************
 
Ok I understand about the comma - semicolon thing now ;-)

I ran into a problem when I tried this in the "real-world" applicatioin.

=INDEX(B2:D4,A6,F1) Worked fine with the following matrix

A1 = B1 = 1 C1 = 2 D1 = 3
A2 = 1 B2 = .1 C2 = .4 D2 = .7
A3 = 2 B3 = .2 C3 = .5 D3 = .8
A4 = 3 B4 = .3 C4 = .6 D4 = .9

But my application doesn't have the row & col with 1, 2, 3 It's something
more like this:

A1 = B1 = 6 C1 = 8 D1 = 11
A2 = 24 B2 = .1 C2 = .4 D2 = .7
A3 = 36 B3 = .2 C3 = .5 D3 = .8
A4 = 40 B4 = .3 C4 = .6 D4 = .9

Now when I use =INDEX(B2:D4,A6,F1) and A6 = 40 & F1 = 8 the function falls
apart because it is looking for a value in 8,40 which the matrix isn't that
big. How can I get A6 = 40 to translate to 3 as it is the 3rd in the list
and the 3rd row of the matrix and F1 = 8 to translate into 2 as it is the
2nd in the columns?
 
.... one way:
=INDEX(B2:D4;MATCH(A6;A2:A4;0);MATCH(F1;B1:D1;0))

--
regards/pozdrav!
Berislav

***************************************
ROT13 - email address (e-mail address removed)
 
Yep! Don't ask me why! :-)

--
pozdrav!
Berislav

***************************************
ROT13 - email address (e-mail address removed)
 
Back
Top