Functions

[ITilerate]

I am having a problem writing an IF function and was wondering if there is a
easy way to accoplish my task as follows: a range of 20 different values in a
column and I want to assign a different value in the column next to those
values based on the range in the first column ie: if value in rows 1 to 20 of
column a is 100 to 2000 respectfully and the values I want to show in the
next column are 1 to 20 ?? This is an example of what I am trying to do
if(a1<=100,1,if(a1>100<=200,2,if(a1>=200<300,3))) but it doesn't return the
answer I look for when the value is between the whole numbers as in 1.2 or
1.5 as an example......

 

[Bob Umlas]

=INT(A1/100)+1
and fill down??

 

[ITilerate]

thks for reply I guess my explanation wasn't proper here is the data again:
column a contain these values 45,87,100,185,255,598,888,1000,1199,1400 and
the vales that I wish to show in a column next to it are respectfully
8.5,8.5,16,16,20,29.5,33,39.5,39.5,42.5,

 

[Jim Thomlinson]

How about using a Vlookup instead. Create a Table something like this in
A1:B4...

0 8.5
100 16
200 20
300 29.5
....

And then use a formula like...
=VLOOKUP(D1, A1:B4, 2, TRUE)
Place this in Cell E1 and the value you want to find in D1...
It will find the closest match for you. This should work better than the
nested if statements which you can only nest 7 deep (Limit of Excel. You can
get around it using named ranges but it is not really necessary in this case)

 

[ITilerate]

Thanks Jim, what is the number 2 and true in you sample represent.... as u
can see I am pretty illerate when it comes to this stuff, and is D1 in your
sample the cell where my original data is?

 

[Jim Thomlinson]

The vlookup function takes 4 argument as follows:
1. The itme you would like to find in the list
2. The list of data you are lookiing in
3. The offset to the valyue you would like returned. In this case 2
represents the second column of data that we are looking in (B).
4. The last argument is optional and indicates whether you requre an exact
match. By default (unless specified otherwise) the value is false and an
exact match is required. if you speicfy true then the list you are looking up
in has to be sorted, and it will return the closest match.

So the formula I posted looks for the Value of cell D1 in the range A1:A4
and finds the closest matching record (rounded down). It then returns the
second column over which is the value from column B. You may want to re-sort
the Range A1:B4 desending to see return the values you need...

 

[ITilerate]

Please forgive my ignorance but I still cannot make this work. The following
is a sample of the data and what I need done, if its is too confusing, for me
it is, thanks for your help anyway::
Col D Col E
16. $8.50
100 $16.00
250 $20.00
500 $29.50
750 $33.00
1000 $39.50
1250 $42.50
1500 $46.00
2000 $49.00
2500 $52.50
3000 $55.50
3500 $57.50
4000 $60.50
4500 $62.00
5000 $64.00
6000 $68.50
7000 $72.00
8000 $75.00
If the values in a particular column are>= to the value I show in Col D
above, I want the next column to show the value that I show in column E
above.... Does this make sense?????