values
Posted by Brian on November 10, 2001 10:18 AM
Aloha,
I would like to type a value in say A1 and a corresponding number appears in A2. As an example, entering 1 in A1 and the value 1000 appears in A2. I would like to have at least 20 enteries with corresponding values appearing. I used =if(A1=1,1000) but I can't expand it.
Mahalo....Brian
Posted by Aladin Akyurek on November 10, 2001 10:26 AM
Do you mean
A1 = 1
A2 = A1 *1000
A3 = 2
A4 = A3 * 1000
or what?
==========
Posted by Brian on November 10, 2001 11:07 AM
Aloha,
Thanks for the fast response. What I mean is I have different models of cars with different weights. Car 1 weighs 2000 lbs, car 2 weighs 2500 lbs etc. When I enter 1 in cell A1, the 2000lbs appears in A2 automatically. If I enter 2 in A1, 2500 lbs appears in A2 automatically and so on for cars 3, 4, 5 etc with each car having a corrsponding weight appearing in A2
Posted by Mark W. on November 10, 2001 11:29 AM
Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0) (nt)
Posted by Brian on November 10, 2001 1:44 PM
Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0) (nt)
Aloha Mark,
It doesn't work.....
Thanks for trying
Brian
Posted by Mark W. on November 10, 2001 1:59 PM
Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)
It most certainly does... care to try again?
Posted by Aladin Akyurek on November 10, 2001 2:06 PM
Vlookup
Brian,
What Mark is suggesting is that you have a table in which you look up. From your description he concluded that you have combinations
1 2000
2 2500
3 3000
4 3500
5 4000
If the combinations are different (not this regular), modify the array table
{1,2000;2,2500;3,3000;4,3500;5,4000}
int one that fits your situation.
Aladin
===========
Posted by Brian on November 10, 2001 2:09 PM
Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)
Mark,
I'm using 97, does that make a difference? I've copied and pasted and get an error message.
Brian
Posted by Mark W. on November 10, 2001 2:36 PM
Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)
I'm using Excel 97 too. What error message are
you getting? Did you inadvertantly copy the "(nt)"
off the subject line?
Posted by Brian on November 10, 2001 3:43 PM
Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)
Mark,
The error message I get is:
The formula you typed contains an error.
The formula I used was =if(a1=1,1000)+if(a1=2,2000) etc. but I can only nest 7 of these. Your way I can use more than 7 numbers
Brian
Posted by Brian on November 10, 2001 3:45 PM
Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)
Mark,
I got it!......Much MAHALO!!!!!
Brian
Posted by Brian on November 10, 2001 3:48 PM
Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)
Mark,
What does the 2 and 0 stand for at the end of the formula?
Brian
Posted by Mark W. on November 10, 2001 4:01 PM
Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)
As Aladin pointed out...
{1,2000;2,2500;3,3000;4,3500;5,4000}
is a 2-column lookup array. When I specify 2 as
the 3rd argument in the VLOOKUP function I'm
asking it to return a value from 2nd column of the
lookup array. When I set VLOOKUP's optional
4th argument to 0 or FALSE I'm insisting that
the value in A1 match a value in the 1st column
of the lookup array EXACTLY! I recommend that
you take a look at the Help Index topic for
"VLOOKUP worksheet function" for a more complete
discussion of this function.
Posted by Brian on November 10, 2001 4:07 PM
Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)
Mark,
Thanks, I understand!
Here's another if you have the time:
I would like to assign a value to a range of numbers
1-99= -10
100-199= +10....etc
Brian
Posted by Aladin Akyurek on November 10, 2001 4:14 PM
Maybe: =IF(A1<100,-10,INT(A1/100)*10) (NT)
Posted by Mark W. on November 10, 2001 4:19 PM
I'm not quite sure...
...what you mean by "assign a value to a range...",
but I believe you mean if A1 is between 1 and 99
inclusive return "-10", and if A1 is between
100 and 199 inclusive return "+10", etc...
The formula for this would be...
=VLOOKUP(A1,{1,"-10";100,"+10";200,#N/A},2)
If I've misconstrued your intent please provide
a sample data set along with the expected
solution.
