Originally Posted by joeu2004[_2_]

Errata....

"joeu2004" wrote:
In fact, if those are always your lookup and results values,
you might write:
=B4*1 + B5*1 + MAX(0,MIN(10,INT(B7/10)))*1.5 + B8*1 +
MAX(0,MIN(10,INT(B9/10)))*1.5 + etc

That should be:

=B4*1 + B5*1 + MAX(0,MIN(10,1+INT(B7/10)))*1.5 + B8*1 +
MAX(0,MIN(10,1+INT(B9/10)))*1.5 + etc


"joeu2004" wrote:
However, you can simplify things by [.... writing]:

=B4*1 + B5*1 +
LOOKUP(B7,{-1E300,1,11,21,31,41,51,61,71,81,91},{0,1,2,3,4,5,6 ,7,8,9,10})*1.5
+ B8*1 +
LOOKUP(B9,{-1E300,1,11,21,31,41,51,61,71,81,91},{0,1,2,3,4,5,6 ,7,8,9,10})*1.5
+ etc
[.... or ....]

=B4*1 + B5*1 + VLOOKUP(B7,X1:Y11,2)*1.5 + B8*1 + VLOOKUP(B9,X1:Y11,2)*1.5
+ etc
where X1:X11 has the values -1E300,1,11,21,31,41,51,61,71,81,91 and Y1:Y11
has the values 0,1,2,3,4,5,6,7,8,9,10.

If the results array is always (now) 0 through 10, you could simply use the
MATCH function as follows:

(MATCH(B7,{-1E300,1,11,21,31,41,51,61,71,81,91})-1)*1.5

or

(MATCH(B7,X1:X11)-1)*1.5