Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
How do I deal with the negative numbers? If possible I would rather not
complicate my formula with an If statement. Thanks again and again and again and again and again |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
That probably means that it can't find it in the list. If the last portion
of the Lookup function is selected as "true" the list needs to be in ascending order. If the number you are looking up is not greater than the first number in the list, it will return #N/A. Give me a little more info on the problem and I will try to help you through it! WB "DMB" wrote: How do I deal with the negative numbers? If possible I would rather not complicate my formula with an If statement. Thanks again and again and again and again and again |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
What's the last parameter of the VLOOKUP? If you're using TRUE or 1, then
your table might have to be sorted in ascending order, negetives to positives. Or maybe your looking up values with decimal places that are not showing. HTH JG "DMB" wrote: How do I deal with the negative numbers? If possible I would rather not complicate my formula with an If statement. Thanks again and again and again and again and again |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
My table is
CAPACITY 0 xxx 1000 phd2 2000 phd3 3000 phd4 4000 phd5 5000 phd6 B5 = 3233 =vlookup(B5, CAPACITY, 1) And now I get nothing but "xxx" |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
If the first column of that table is numeric (real numbers) and if b5 is numeric
(real numbers), then I get 3000 back from that formula. If b5 contains the text '3233, then I get #n/a Debra Dalgleish has some trouble shooting tips at: http://www.contextures.com/xlFunctions02.html#Trouble ps. Did you want to return column 2: =vlookup(B5, CAPACITY, 2) (assuming that Capacity is a 2 column table.) DMB wrote: My table is CAPACITY 0 xxx 1000 phd2 2000 phd3 3000 phd4 4000 phd5 5000 phd6 B5 = 3233 =vlookup(B5, CAPACITY, 1) And now I get nothing but "xxx" -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
I figured out that the column numbers are relitive to the table and not the
sheet. I also found that the result is the row before the next higher value. ROW1 ROW2 ROW3 CAPACITY HOLDOWN 0 n.r. 1000 PHD1 2000 PHD2 3000 PHD3 4000 PHD4 =VLOOKUP(1500, CAPACITY, 3) RETURNS A BLANK =VLOOKUP(1500, CAPACITY, 2) RETURNS n.r. tHE NEGATIVE PROBLEM SEEMS TO HAVE GONE AWAY. Is the return value working correctly? I am going to experiment with the true/false value. The problem with this is anyone entering data will be confused by the table. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
Actually the negative problem still exists
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
If I use
=VLOOKUP(B5,Capacity,2) with 3233 in B5 I get phd4, not good? -- Regards, Peo Sjoblom Portland, Oregon "DMB" wrote in message ... My table is CAPACITY 0 xxx 1000 phd2 2000 phd3 3000 phd4 4000 phd5 5000 phd6 B5 = 3233 =vlookup(B5, CAPACITY, 1) And now I get nothing but "xxx" |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
Make sure that the first column in Capacity is a real number
select an empty cell edit|copy select that first column edit|Paste special|check add. Then try it again. If that doesn't work, what is Capacity defined as--just share the address of that table. DMB wrote: I figured out that the column numbers are relitive to the table and not the sheet. I also found that the result is the row before the next higher value. ROW1 ROW2 ROW3 CAPACITY HOLDOWN 0 n.r. 1000 PHD1 2000 PHD2 3000 PHD3 4000 PHD4 =VLOOKUP(1500, CAPACITY, 3) RETURNS A BLANK =VLOOKUP(1500, CAPACITY, 2) RETURNS n.r. tHE NEGATIVE PROBLEM SEEMS TO HAVE GONE AWAY. Is the return value working correctly? I am going to experiment with the true/false value. The problem with this is anyone entering data will be confused by the table. -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
No it shoud have been a phd5.
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
Am I correct to assume that you want to lookup the number regardless if it's
positive or negetive? i. e. -1500 then lookup 1500? If so then try: assuming -1500 is in A1 =VLOOKUP(ABS(A1),CAPACITY,2,1) or =VLOOKUP(ABS(A1),CAPACITY,2,0) depending on your situation. HTH JG "DMB" wrote: No it shoud have been a phd5. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
So you want the larger closest value and regardless if a value is negative
so if B5 was -3233 you still want Phd5? =INDEX(Capacity,MATCH(SMALL(INDEX(Capacity,,1),COU NTIF(INDEX(Capacity,,1),"<"&ABS(B5))+1),INDEX(Capa city,,1),0),2) -- Regards, Peo Sjoblom Portland, Oregon "DMB" wrote in message ... No it shoud have been a phd5. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and "-" negative numbers are giving me a #N/A
THANKS FOR THE FANCY FORMULE Peo Sjoblom. IT WILL TAKE A LITTLE EFFORT TO
FIGURE IT OUT. This works nice for returning "12" which is one of the problems I was trying to solve. I still have to deal with the negative issue. I am currently using If statements which work fine but I have a feeling that it isn't the most effecient method. I am currently using: b5 = -5555 =IF(B5=0,IF(C17=0, 0, IF(B5<0, "N.R.",VLOOKUP(B5, StrapCapacity,2))),IF(B5<0, "N.R.",VLOOKUP(B5, StrapCapacity,2))) This formula deals with the negative value I feel that if this formula could deal with negatives it would look like this: =VLOOKUP(B5, StrapCapacity,2) much easier to manage This is the result of your formul: =INDEX(Capacity,MATCH(SMALL(INDEX(Capacity,,1),COU NTIF(INDEX(Capacity,,1),"<"&ABS(B5))+1),INDEX(Capa city,,1),0),2) =12 b5 = -5555 It should be = 5 -10000 5 0 6 1000 7 2000 8 3000 9 4000 10 5000 11 6000 12 7000 13 8000 14 9000 15 Goes this make sense? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|