Convert numerical data to a discrete range of values using Excel

[Huskey]

Hi,

I have a database with all numerical values. I want to convert all to a discrete range of values which can be specified as I wish. How to do in Excel? Please help!!!

thanks

 

[ETID]

Can you better define discrete range?

 

[Huskey]

hi, let's say I define a discrete range. More than 0.66 is considered "1" for instance. In the column, I have many numerical values like 0.22, 0.12, 0.11, 0.89.... How the excel can automatically use the discrete range I set to convert the whole database to the discrete values?

please help

 

[ETID]

I am thinking a vlookup table....

vlookup table as follows....

A B
0.11 0
0.12 0
0.22 1
0.66 1
0.89 1

assuming you values are in col D
then formula in any other col...


=vlookup(d2,a:b,2)
...fill down


Returns the highest value in the table (experiment with the vlookup table) you may only need values that correspond to thresholds that define a discrete range I.E. the formula will return 0 for any value from 0 to 0.22 so 0.11 and 0.12 would not be needed


=vlookup(d2,a:b,2,false)


will return only an exact match or if non found an "n/a"

 

[ETID]

ooops...coresponding value in vlookup table for value 0.22 should be 0 not 1 but you'll define you own any way

 

[ETID]

Oh yeah...the vlookup table must be sorted (ascending) by col A

 

[bkpchs237]

Huskey,

I have placed a sample lookup table below for your use. It assumes that you have no values less than zero. Then use the following formula: =VLOOKUP(D2,$A$2:$B$12,2) and it should accomplish the task you describe here. This will place a zero in all cells with values from 0 up to but not including .22, a 1 in cells adjacent to values equal to .22 but less than 1, a 2 in cells with values from 1 to below 1.78, etc.

Lookup Table
0 0
0.22 1
1 2
1.78 3
2.56 4
3.34 5
4.12 6
4.9 7
5.68 8
6.46 9
7.24 10

Hope this helps.

 

[ETID]

Hmmm where did I hear that before?