M
Mal
I hope someone can help me on this one.
I have a column of numbers in A1-A6.
I have three columns (B
) of combinations of the words "Greater", "Equal",
"Less"
I have a table (A1018) four columns wide and any rows long which include
"Greater", "Equal", "Less" plus a value
I want to match the words in B11 with the table A:C and multiply the
corresponding values in column A and column D.
So match b1:d1 with Table A11:C11, multiply 5*1.9 and produce the result 9.5
in E.
A B C D E
1 5 greater greater equal (Result 9.5 (5*1.9))
2 3 equal greater greater (Result 4.8 (3*1.6))
3 6 less greater less (Result 6.6 (6*1.1))
4 9 less greater equal (Result 10.8 (9*1.2))
5 7 equal greater less (Result 9.8 (7*1.4))
6 2 greater greater less (Result 3.4 (2*1.7))
There can be any combinations of these words in random order.
Table A1018
A B C D
10 Greater Greater Greater 2
11 Greater Greater Equal 1.9
12 Greater Greater Less 1.7
13 Equal Greater Greater 1.6
14 Equal Greater Equal 1.5
15 Equal Greater Less 1.4
16 Less Greater Greater 1.3
17 Less Greater Equal 1.2
18 Less Greater Less 1.1
There are other combinations but you can see the columns are in order.
I hope this all makes sense.
Thanks,
Mal
I have a column of numbers in A1-A6.
I have three columns (B
) of combinations of the words "Greater", "Equal","Less"
I have a table (A10
18) four columns wide and any rows long which include"Greater", "Equal", "Less" plus a value
I want to match the words in B1
1 with the table A:C and multiply thecorresponding values in column A and column D.
So match b1:d1 with Table A11:C11, multiply 5*1.9 and produce the result 9.5
in E.
A B C D E
1 5 greater greater equal (Result 9.5 (5*1.9))
2 3 equal greater greater (Result 4.8 (3*1.6))
3 6 less greater less (Result 6.6 (6*1.1))
4 9 less greater equal (Result 10.8 (9*1.2))
5 7 equal greater less (Result 9.8 (7*1.4))
6 2 greater greater less (Result 3.4 (2*1.7))
There can be any combinations of these words in random order.
Table A10
18A B C D
10 Greater Greater Greater 2
11 Greater Greater Equal 1.9
12 Greater Greater Less 1.7
13 Equal Greater Greater 1.6
14 Equal Greater Equal 1.5
15 Equal Greater Less 1.4
16 Less Greater Greater 1.3
17 Less Greater Equal 1.2
18 Less Greater Less 1.1
There are other combinations but you can see the columns are in order.
I hope this all makes sense.
Thanks,
Mal