2 If formulas

[yrajnani]

Can we merge below mentioned 2 if formulas in 1

=IF(ISNUMBER(DR26),IF(DR26>1.2,"Excess Stock",IF(DR26>1.2,"Adequate Stock","Short Supply")),"")
&
=IF(AND(CZ26=0,DP26>=1),"Non Moving Stock"," ")

Any help will be highly appreciated.

Thanks,
Yogita

 

[jasonb75]

your first formula is flawed, it will never give adequate stock, only short or excess.

 

[yrajnani]

Hi Jason,

Thanks you are right, can you help me correct this part..

Thanks,
Yogita

 

[jasonb75]

I could if i knew the correct criteria, the only information I have so far is your formula, nothing that tells me what you're trying to do.

 

[yrajnani]

Hi,

My condition is mentioned below:

Closing Stock =cell DG2, Average sales= cell CZ2
DP23= Average closing stock
Cell DR2 has closing stock/average sale qty

If DR2 is greater than 1.2 = excess stock
If DR2 is between 1-1.2= adequate stock and if
DR2 is less than 1.2 = short stock.
Also if Average closing stock is greater than 1 and average sale is 0, then it should state non-moving stock

Pls let me know if any further info is required.
Thanks,
Yogita

 

[jasonb75]

If DR2 is greater than 1.2 = excess stock
If DR2 is between 1-1.2= adequate stock and if
DR2 is less than 1.2 = short stock.

Your logic is still flawed, 1.1 is between 1 and 1.2 making it adequate, and is also less than 1.2 making it short.

Which is it? It can't be both!

Maybe this will work.

=IF(AND(CZ26=0,DP26>=1),"Non Moving Stock",IF(DR26>1.2,"Excess Stock",IF(DR26<1,"Short Supply","Adequate Stock")))

 

[yrajnani]

Thanks, its working.. also can u pls help me in adding 1 more thing.
Incase, the formula returns an error, it should be blank.

Thanks,
Yogita

 

[jasonb75]

=IFERROR(IF(AND(CZ26=0,DP26>=1),"Non Moving Stock",IF(DR26>1.2,"Excess Stock",IF(DR26<1,"Short Supply","Adequate Stock"))),"")

 

[yrajnani]

Thanks a ton!!, it worked...