using the if formula with comparison operators

**dmorovitz** · 10-09-2008, 06:13 AM

I have a problem using the if formula with comparison operators. I have a range of fields for geographic areas and the values in these fields are a percent. I want to categorize the data based on thier value.
For example:

If the value in the cell is between 0 and 9.9 percent, I would like the catagorize this as a "1" and so forth.

The entire categories I have are:

0-9.9% = 1
10-30% = 2
30.1-39.9% = 2,5
40-60% = 3
60.1-69.9% =3,5
70-100% = 4

Using the if formula I know how to obtain an answer if the condition is exact -if(A1=0:1:A1) but I would like to enter a range of values for the comparison such as -if A1 is between 0 and 9.9 then 1, if A1 is between 10 and 30 then 2 etc. Is this possible? I have not found any answer yet.
Thanks for your help!
Dan

**rwgrietveld** · 10-09-2008, 06:15 AM

FREQUENCY does just that. look in the help file. Copy the example and you have your solution.

**rwgrietveld** · 10-09-2008, 06:25 AM

Sorry for the previous answer. Did not read! Hope this helps

Please Login or Register  to view this content.

**dmorovitz** · 10-09-2008, 08:33 AM

Thanks for the help! The index and match formula worked perfectly, fortunately changing the formula for the german version of excel wasn't too difficult!