help with Excel function

[torata]

I think this is the right place to post this.

I wonder if someone can help me with the excel function/s that can adjust the base score to the final score as influenced by the 3 variables

Scores run from 1-10, cannot be higher than 10
Final score can only be a max 2 higher or lower than base score
Variable 1: if it's 1, then -1 to the base score, if it's 10 then +1
Variable 2: if it's 1 or 2, then -1 to the base score, if it's 9 or 10 then +1
Variable 3: if it's 1, then -2 to the base score; if 2, then -1; if 9, then +1; if 10 then +2



Example 1: no influence by variable scores
Example 2: base + variable 1's +1
Example 3: base + variable 1's -1
Example 4: base + +1 from each variable but limited to max score 10
Example 5: base + +1 from each variable but limited to max 2 point difference
Example 6: base + +2 from variable 3
Example 7: base + +2 from variables 1 & 2, -2 from variable 3

I'd like a single formula to put into final score cell if possible.

I can't get my head around it. Can anyone?

many thanks in advance

torata

[Alaric]

I can't get my head around it. Can anyone?

One simple if lengthy approach is to exploit the feature that true/false evaluates to 1 or 0.

So you write an expression of the form (cell=value1) * (formula1) + (cell=value2) * (formula2) etc.

You could do something similar with the "IF" function.

So IF (cell=value1,formula1,0) + IF (cell=value2, formula2,0) + ...

[Stompa]

To avoid loads of nested conditionals, I think I'd be inclined to use a lookup table, something like:

1	1	-1	-2
2	0	-1	-1
3	0	0	0
4	0	0	0
5	0	0	0
6	0	0	0
7	0	0	0
8	0	0	0
9	0	1	1
10	-1	1	2

then

final=base+VLOOKUP(var1,lookuptable,2)+VLOOKUP(var2,lookuptable,3)+VLOOKUP(var3,lookuptable,4)

To limit to base+/-2, then

=MAX(MIN(final,base+2),base-2)

so as a single formula:

=MAX(MIN(base+VLOOKUP(var1,lookuptable,2)+VLOOKUP(var2,lookuptable,3)+VLOOKUP(var3,lookuptable,4),base+2),base-2)

[Stompa]

To avoid loads of nested conditionals...

Oops, my mistake, they wouldn't of course need to be nested!