If(And

[naveenakh]

Hi

=if(A2=1,"0-1 Day"

A2=2,"0-1 Day"

A2=3,"0-1 Day"

A2=4,"2 Day"


I have condition like below

Ageing Slab

1 0-1 Day

2 0-1 Day

3 0-1 Day

4 2 Days

5 3 Days

6 > 3 Days

7 > 3 Days

8 > 3 Days

9 > 3 Days


Pls help me.


Thanks

 

[naveenakh]

Pls find the link

http://speedy.sh/4fbu7/Ageing-Slab.xlsx

 

[shrivallabha]

Hi Naveena,


You have already done the tabulating part so all you need to use is VLOOKUP formula. e.g. you have ageing data in Column D starting from D2 then in E2 write formula:


=VLOOKUP(D2,$A$1:$B$10,2,0)


And then copy down.

 

[naveenakh]

Sorry I want use the if formula


I tried with =IF(OR(A2=1,A2=2,A2=3),"0-1 Day",">3 Days") it is for 1 conditions


& I "ll try to remaining 2 condition like =if(A2=4,"2 Days" & A2=5,"3 Days",">3 days"


How to combained these 2 formula to get a result.

Pls help me

 

[srinidhi]

If you want to use IF Formula, its nested Ifs & array formula.


Assuming that the data is in D2 & you want the ageing (result) in E2, The data Ageing & Slabs data are in A & B.


=IF(D2=$A$2,$B$2,IF(D2=$A$3,$B$3,IF(D2=$A$4,$B$4,IF(D2=$A$5,$B$5))))


(Shift+Ctrl+Enter) Array Formula

 

[naveenakh]

I want write formula in cell B2

My requirement is In cell A2= 1,2,3 means "0-1 Day"

if in cell A2=4 means "2 Days"

in cell A2=5, "3 days"

in cell A2= 6,7,8,9 comes means ">3 Days"

 

[srinidhi]

It is always better to keep the data in cell & not hardcore in formula,In my example a2 has the Ageing & b2 has the slabs, you can modify the same to fit your cell requirement

 

[shrivallabha]

You can use NESTED IF functions like below [normally entered]:


=IF(OR(A2=1,A2=2,A2=3),"0-1 Day",IF(A2=4,"2 Days",IF(A2=5,"3 Days","> 3 Days")))


Edit: I hope this is not some student assignment as you are insisting for specific formula only.

 

[Faseeh]

Hi Naveen,


Despite you do not need any thing else then an IF() you can also try this:

=LOOKUP(1,{1,4,5,6},{"0-1","2","3",">3"})&" Days"

Regards,