Forum Discussion

Diego Barrera's avatar
Diego Barrera
Copper Contributor
Jul 05, 2017

Nested IF formula

Hello Everyone:

 

I have a total of 5 ranges of prices clasified by a alpha numeric code (IE: P5 from 1USD to 5 USD - P4 from 6USD to 10 and so on until P1 from 25 and above), and I have a data base with the numeric prices of many items; I would like to have a formula that analyses the price level and according to the ranges defined put the correct code. 

 

I was trying to use a nested IF function like this: 

IF(Y(T6>0;T6<=5);"P5";IF(Y(T6>=6;T6<10);"P4";IF(Y(T6>=11;T6<15);"P3";IF(Y(T6>=16;T6<25);"P2";"P1")

 

But it seems to be so many nested functions. 

 

Could anyone help me with the correct way to do this.

 

Thanks in advance

 

Diego B

Formulas & Functions
Need Help

1 Reply

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jul 05, 2017

    Hi

     

    Your fomula has some definition holes.

    If you are using whole numbers 10 and 15 are not definied.

    If you are using decimal numbers a lot more is not defined.

     

    Instead of nested IF() functions it is better to set up a lookup table (W1:X5). Column W has to be sorted in ascending order.

     

     

    =LOOKUP(T6,$W$1:$W$5,$X$1:$X$5)

     

Resources