Excel - correcting #value! problem

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
can anyone offer a solution to this?

thanks sparky24




--
sparky24

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


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


[.... or ....]

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

'joeu2004[_2_ Wrote:

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.




--
sparky24

[....]

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.