• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested IF formula - getting wrong result

Status
Not open for further replies.

sn152

Member
Hi,

I need to add category according to the TAT in the attached workbook. below are the list of categories according to the TAT.



Pls see the attached excel sheet and help me.

thanks
 
Hi,

ur col B criteria is quite unclear as not shown for

1-2
7-8



But check this.
=LOOKUP(B2,{1,2,8,10},{"Less than 1 day","2-7 days","8-10 days","10+ days"})
 
Hi Deepak,

1-2 is not mentioned because Less than a day includes 1 and 2-7 includes 2 and 7-8 includes 8.
 
I tried using this formula -
=IF(ISBLANK(B2)," ",IF(B2<=1,"0-1 Day",IF(B2<=7&B2>=2,"2-7 Days",IF(B2<=10&B2>=8,"8-10 Days",IF(B2>=11,"+10 Days")))))

But it is giving wrong result. Even if the value in the TAT column is more than 10 it is showing category as "2-7 days"
 
here's if

=IF(B2<1,$I$5,IF(B2<=7,$I$6,IF(B2<=10,$I$7,$I$8)))

or

=IF(B2>10,$I$8,IF(B2>=7,$I$7,IF(B2>=1,$I$6,$I$5)))
 
here's if

=IF(B2<1,$I$5,IF(B2<=7,$I$6,IF(B2<=10,$I$7,$I$8)))

or

=IF(B2>10,$I$8,IF(B2>=7,$I$7,IF(B2>=1,$I$6,$I$5)))

Slight modified for blank

=IF(ISBLANK(B1008),"",IF(B1008>10,$I$8,IF(B1008>=7,$I$7,IF(B1008>=1,$I$6,$I$5))))

=IF(ISBLANK(B1008),"",IF(B1008<1,$I$5,IF(B1008<=7,$I$6,IF(B1008<=10,$I$7,$I$8))))
 
Hi Deepak,

I just modified the criteria in the formula which you gave.

=IF(ISBLANK(U2),"",LOOKUP(U2,{0,1,2,8,11},{"0-1 Day","0-1 Day","2-7 days","8-10 days","10+ days"}))

Now is there a way to put this formula using VB macro.

Thanks!
 
Status
Not open for further replies.
Back
Top