formula needed for ageing

[ericlch16]

i have values in col A which is

0.21
-0.12
0.12
0.33
0.44
0.96


I want to do a formula for a range. i.e in column B is should be

20-30%
<0%
10-20%
30-40%
40-50%
90-100%

What is the best way to do it?? I have done nested if but i am getting "the specified formula cannt be entered because it uses more levels of nesting than are allowed in the current file format"

anyway to do it without nested if??

the range i want is
<0%
0-10%
11-20%
21-30%
31-40%
41-50%
51-60%
61-70%
71-80%
81-90%
91-100%

 

[sheetspread]

If your data begins in A2, paste this in B2:

=IF(A2<0,"<0%",(ROUNDDOWN(A2,1)*100)&"-"&((ROUNDDOWN(A2,1)*100)+10)&"%")

and copy down

 

[Yahya]

Try This
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0.21</td><td style=";">21-30%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">-0.12</td><td style=";"><0%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style=";">0-10%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">0.12</td><td style=";">11-20%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.1</td><td style=";">11-20%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">0.33</td><td style=";">31-40%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.2</td><td style=";">21-30%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">0.44</td><td style=";">41-50%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.3</td><td style=";">31-40%</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">0.96</td><td style=";">91-100%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.4</td><td style=";">41-50%</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">0.09</td><td style=";">0-10%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.5</td><td style=";">51-60%</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.6</td><td style=";">61-70%</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.7</td><td style=";">71-80%</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.8</td><td style=";">81-90%</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.9</td><td style=";">91-100%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=IF(<font color="Blue">A2<0,"<0%",LOOKUP(<font color="Red">A2,$H$3:$I$12</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[StephenGKorea]

There is a couple ways that you can accomplish this. You can use the lookup function, which is easy to look up information on that OR .. use this formula:

=IF(A1<0,"<0%",IF(ROUNDUP(A1,1)*100<11,"0 - 10%",ROUNDDOWN(A1,1)*100+1&" - "&ROUNDUP(A1,1)*100&"%"))