correcting #value! problem

[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(
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,4
,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 cell
B7,B9,B12,B13,B15. If zero is entered into any of the listed cells the
#value! is displayed. As i would need to enter a zero on some occasions
can anyone offer a solution to this?

thanks sparky2

 

[joeu2004]

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.

 

[joeu2004]

Errata....

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

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

 

[sparky24]

;1602117']Errata....

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

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.

+ 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 an 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 us
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 formul
(MATCH(B7,{-1E300,1,11,21,31,41,51,61,71,81,91})-1)*1.5 that yo
suggested and it worked a treat, no more #value! error
I do not understand how the formula works but not to worry, at least i
does what i was wanting to achieve,

thanks again for your help

 

[joeu2004]

i used the formula
(MATCH(B7,{-1E300,1,11,21,31,41,51,61,71,81,91})-1)*1.5 [....]
I do not understand how the formula works but not to
worry, at least it does what i was wanting to achieve,

It is unwise to use a formula without understanding it. If you understand
the LOOKUP expression that you used originally, you should understand the
MATCH expression after reading the Help page.

Without a 3rd parameter (defaults to 1), MATCH returns the index (1 to n) of
the value in the array {-1E300,1,...,91} that B7 is equal to or that is the
largest value less than B7.

Thus, if B7 is empty or less than 1, MATCH returns 1 because B7
matches -1E300. If B7 is 1 or more but less than 11, MATCH returns 2
because B7 matches 1. If B7 is 11 or more but less than 21, MATCH returns 3
because B7 matches 11. Et cetera.

But your original expression returned 1 if B7 matches 1, 2 if B7 matches 11,
etc. And I added: 0 if B7 is empty or less than 1.

Therefore, we must reduce MATCH by 1. Thus, MATCH(...)-1.

Of course, multiplying 1.5 comes from your original formula. Thus,
(MATCH(...)-1)*1.5.