Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions
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(a1100<=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...... -- ITilerate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions
=INT(A1/100)+1
and fill down?? "ITilerate" wrote in message ... 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(a1100<=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...... -- ITilerate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions
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, -- ITilerate "Bob Umlas" wrote: =INT(A1/100)+1 and fill down?? "ITilerate" wrote in message ... 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(a1100<=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...... -- ITilerate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions
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) -- HTH... Jim Thomlinson "ITilerate" wrote: 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, -- ITilerate "Bob Umlas" wrote: =INT(A1/100)+1 and fill down?? "ITilerate" wrote in message ... 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(a1100<=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...... -- ITilerate |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions
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? -- ITilerate "Jim Thomlinson" wrote: 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) -- HTH... Jim Thomlinson "ITilerate" wrote: 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, -- ITilerate "Bob Umlas" wrote: =INT(A1/100)+1 and fill down?? "ITilerate" wrote in message ... 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(a1100<=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...... -- ITilerate |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions
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... -- HTH... Jim Thomlinson "ITilerate" wrote: 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? -- ITilerate "Jim Thomlinson" wrote: 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) -- HTH... Jim Thomlinson "ITilerate" wrote: 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, -- ITilerate "Bob Umlas" wrote: =INT(A1/100)+1 and fill down?? "ITilerate" wrote in message ... 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(a1100<=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...... -- ITilerate |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Functions
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????? -- ITilerate "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "ITilerate" wrote: 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? -- ITilerate "Jim Thomlinson" wrote: 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) -- HTH... Jim Thomlinson "ITilerate" wrote: 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, -- ITilerate "Bob Umlas" wrote: =INT(A1/100)+1 and fill down?? "ITilerate" wrote in message ... 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(a1100<=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...... -- ITilerate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Conversion from Spreadsheet Toolkit functions (ESSV....) to EssBase API functions | Excel Programming | |||
excel functions and User defined functions | Excel Programming |