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?

thanks sparky24

Hmmm.. hefty formula!

Could you post an example workbook with a bit of an explanation as to what you're trying to achieve?

Hi Spencer101,
How do i upload a sample workbook? Cant seem to work out how to attach a file?

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

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

Hi joeu2004,
thanks for your reply, i used the formula (MATCH(B7,{-1E300,1,11,21,31,41,51,61,71,81,91})-1)*1.5 that you suggested and it worked a treat, no more #value! error :)
I do not understand how the formula works but not to worry, at least it does what i was wanting to achieve,

thanks again for your help.