"sparky24" wrote:
I am using the following formula in a spreadsheet-
=(B4*1)+(B5*1)+(IF(B7,LOOKUP(B7,{1,11,21,31,41,51, 61,71,81,91},{1,2,3,4,5,6,7,8,9,10})*1.5,""))+(B8*
1)+(IF(B9,LOOKUP(B9,{1,11,21,31,41,51,61,71,81,91} ,{1,2,3,4,5,6,7,8,9,10})*1.5,""))+(IF(B12,LOOKUP(B
12,{1,11,21,31,41,51,61,71,81,91},{1,2,3,4,5,6,7,8 ,9,10})*0.5,""))+(IF(B13,LOOKUP(B13,{1,11,21,31,41
,51,61,71,81,91},{1,2,3,4,5,6,7,8,9,10})*0.5,""))+ (B14*0.5)+(IF(B15,LOOKUP(B15,{1,11,21,31,41,51,61,
71,81,91},{1,2,3,4,5,6,7,8,9,10})*1,""))+(B16*5)

It works ok if a number 1 or above is entered into cells
B7,B9,B12,B13,B15. If zero is entered into any of the listed cells then
#value! is displayed. As i would need to enter a zero on some occasions,
can anyone offer a solution to this?

The problem is: you wrote IF(B7,...,""). That says if B7 is zero, return
the null string. You cannot do arithmetic using the null string.

So the minimum change is: IF(B7,...,0).

However, you can simplify things by putting zero in the lookup array and
have a corresponding value in the result array (zero?). And if any of B7,
B9, B12, B13 or B15 might be negative, use some "large" negative number
like -1E300 instead of zero in the lookup array. For example:

=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

Of course, it would be better if you put the lookup and result arrays into a
range, since they all appear to be the same. Then you might write:

=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.

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

Finally, why are you multiplying B4, B5 etc by one?

It should be unnecessary in this context. Even if B4 and B5 are numeric
text instead of actual numbers, Excel will treat them as numbers when
performing any arithmetic, namely addition.