Thread: Rounding values to specific range or increased nested if statements??

Hi everyone,
Been doing massive digging online and can't find any solution to this.
I did a nested if statement and it said too many nested if statements even though I'm using 2007 excel version.

Anyway I want a value to automatically round down or up to this specific range
[2,3,5,7,10,20,30,50,70,100,150,200]

I initially tried with the nested if and would have included the file with the failed nested if statements but it got deleted.

Not enough detail to offer a specific, but you could try something like this to overcome too many nested IFs

=IF(test1,true_result,0)&IF(test2,true_result,0)&...

This is what I have so far but no luck in stringing the argument together. I get this as a result. "005000000000" ???

=IF(A1<=2.5,"2",0)&IF((A1>2.5)*(A1<=4),"3",0)&IF((A1>4)*(A1<=6),"5",0)&IF((A1>6)*(A1<=8.5),"7",0)&IF ((A1>8.5)*(A1<=15),"10",0)&IF((A1>15)*(A1<=25),"20",0)&IF((A1>25)*(A1<=40),"30",0)&IF((A1>40)*(A1<=6 0),"50",0)&IF((A1>60)*(A1<=85),"70",0)&IF((A1>85)*(A1<=125),"100",0)&IF((A1>125)*(A1<=175),"150",0)& IF((A1>175),"200",0)

I also tried the method of =IF(a1<2.5,"2",IF((A1>2.5)*(A1<4),"3",IF((A1>4)*(A1<=6),"5", ETC....

Here are the basic parameters
2 = <2.5
3 = >2.5 <=4
5 = >4 <=6
7 = >6 <=8.5
10 = >8.5 <=15
20 = >15 <=25
30 = >25 <=40
50 = >40 <=60
70 = >50 <=85
100= >85 <=125
150= >125 <=175
200= >175

Try

=INDEX({200,150,100,70,50,30,20,10,7,5,3,2},MATCH(A1,{999,175,125,85,60,40,25,15,8.5,6,4,2.5},-1))

Thanks! I think it worked!!