Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 #value! is displayed. As i would need to enter a zero on some occasions, can anyone offer a solution to this? thanks sparky24 |
#2
|
|||
|
|||
Quote:
Could you post an example workbook with a bit of an explanation as to what you're trying to achieve? |
#3
|
|||
|
|||
Hi Spencer101,
How do i upload a sample workbook? Cant seem to work out how to attach a file? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correcting #value! problem
"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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correcting #value! problem
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 |
#6
|
|||
|
|||
Quote:
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
correcting #value! problem
"sparky24" wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Correcting Multiple Div#0s | Excel Worksheet Functions | |||
Correcting an extrapolation macro | Excel Programming | |||
Correcting a #REF! | Excel Worksheet Functions | |||
need help in correcting the formula | Excel Worksheet Functions | |||
Correcting #NAME errors | Excel Programming |