Assigning a value from 2 factors

[redforest]

Hi
I need help setting up a formula that will look at 2 values; x (being
P1(i), P1(ii), P2(i), P2(ii), P3(i), P4, P5, P6, P7, P8, L1C, L1B, L1A,
L2C, L2B, (I thought this would probably be in a column P Score). Then
look at the value y (which would determined whether the numerical value
is <20, <40<60<80<100, for each of the possibilities that x could be.
(Again I would have this in a column list - Percentage) The formula
would then have to match from the 2 columns to assign a value from a
third column. For example, x could be P2(ii) and y could be 42 which
would assign the value 2.7 from the value column. I hope this makes
sense, I tried to include a simple table using spaces to demonstrate
but on preview it did not work out that way. i hope someone can help
as I'm really stuck. Cheers in advance. redforest

 

[Pete_UK]

I'm a bit confused about this, but I think you would need a lookup
table for the first part. Enter your data in a column and put your
scores in the adjacent column like so:

P1(i) score for this value
P1(ii) score for this value, etc
P2(i)
P2(ii)
P3(i)
P4
P5
etc

Assume this table occupies P1 to Q15, and your value x is in A1, then
this will return the "score":

=VLOOKUP(A1,P$1:Q$15,2,0).

You could have another lookup table for your percentages if they are
likely to vary, but with the values you quote you could just apply
simple arithmetic to convert a value of 42 to 40 and from there
determine the factor.

If you need any further help please post a few more details.

Hope this helps.

Pete