Return certain values by certain intervals in a cell

**pufan** · 02-28-2010, 12:31 PM

Hello,

I have a question. My problem is that I have to generate a table in excel. In my columns I have to insert in the cells certain values by certain intervals.

For example, if i have a column of numbers generated using the RAND() function, i need to insert in my cell 0 if the rand number is <=0.2; 1 if the number is between (0.2;0.6]; 2 if the no. is between (0.6;0.9] and 3 if (0.9;1].

What function should i use and how. I tried with multiple IF's but with no success.

Thank you.

Robert.

**ElmerS** · 02-28-2010, 01:35 PM

Hi,

Assuming your randomized cells are A1:A10 > in cell B1 type:

=IF(A1<=0.2,0,IF(AND(A1>0.2,A1<=0.6),1,IF(AND(A1>0.6,A1<=0.9),2,IF(AND(A1>0.9,A1<=1),3,""))))

OR:

=0*(A1<=0.2)+1*(AND(A1>0.2,A1<=0.6)+(2*(AND(A1>0.6,A1<=0.9))+3*(AND(A1>0.9,A1<=1))))

and copy down until B10

Elm