Originally Posted by sparky24

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