An if formula problem.

[jsk_lge]

Hi all,
I have a database, screen shot is below-

A​

B​

C​

D​

1​

Aging PDT Nimbus Wty Status

2​

1 Year Audio N

3​

2 Year REF N

4​

1 Year MNT N

5​

2 Year REF Y

6​

1 Year RAC N

7​

3 Year LTV N

8​

4 Year PTV N

9​

5 Year​

WM​

N​

I want the result in D colum, conditions are as below:-
For aging 1 Year - All Products In Wty
For aging 2 Year-3 Year - MNT and REF Nimbus In Wty, all other Out Wty
For Aging 4 Year - 5 Year - All Products Out Wty

if aging is greater than 1 Year and product is Ref but without Nimbus (N in Nimbus Field) than it will be Out Wty.

which formual should i use so that I found desired result (In Wty, Out Wty).

 

[jsk_lge]

A B C D
Aging PDT Nimbus Wty Status
1 Year Audio N
2 Year REF N
1 Year MNT N
2 Year REF Y
1 Year RAC N
3 Year LTV N
4 Year PTV N
5 Year WM N

I want the result in D colum, conditions are as below:-
For aging 1 Year - All Products In Wty
For aging 2 Year-3 Year - MNT and REF Nimbus In Wty, all other Out Wty
For Aging 4 Year - 5 Year - All Products Out Wty

if aging is greater than 1 Year and product is Ref but without Nimbus (N in Nimbus Field) than it will be Out Wty.

which formual should i use so that I found desired result (In Wty, Out Wty).

 

[NARAYANK991]

Hi ,

I am not sure I have understood your problem , but try this :

=IF(LEFT(C3)+0=1,"In Warranty",IF(LEFT(C3)+0>=4,"Out of Warranty",IF(OR(D3="MNT",D3="REF"),IF(E3="Y","In Warranty","Out of Warranty"),"Out of Warranty")))

Narayan

 

[jsk_lge]

@NARAYANK991
Dear Sir,
this formula is perfect, but here is a little problem, I specify the conditions again:-
if Aging is 1 Year then all products are In Warranty, If Aging is 2 Year - 3 Year then MNT and those REF which are nimbus are in warranty and all other Out of Warranty and if aging is greater than 3 Year then all products are out of warranty.
in this formula I got perfect result but for MNT it return wrong result, all MNT are under warranty for 3 years, all ref which are nimbus also under warranty for 3 Years and REF which are non nimbus and all other products are under warranty for only 1 Year.

This formula works for all products but for MNT it return wrong result.
so please made neccessary changes in this formula.

 

[NARAYANK991]

Hi ,

I did not understand the part about MNT ; are you saying that if the aging is 2 or 3 years , and PDT is MNT , then irrespective of whether Nimbus is Y or N , it is in warranty ?

So , if aging is 2 or 3 years , and PDT is REF , only then we need to check whether Nimbus is Y or N ?

If so , please change the formula as follows :

=IF(LEFT(C3)+0=1,"In Warranty",IF(LEFT(C3)+0>=4,"Out of Warranty",IF(OR(D3="MNT",AND(D3="REF",E3="Y")),"In Warranty","Out of Warranty")))

Narayan

 

[jsk_lge]

Yes sir,
The nimbus field is only for REF products And this forumula is also perfect. It return exact results.

Thank you Narayan.

 

[NARAYANK991]

Hi ,

I am not able to understand why the latest formula gives the wrong result ; what it is doing is :

If the aging is 2 or 3 years , and if MNT , then "In warranty".

If the aging is 2 or 3 years , and if REF , then if Nimbus = YES , then "In warranty" else "Out of warranty".

Can you post the exact data that you have , and the result that you expect for that data ? Upload sufficient data that any formula that is given can be tested adequately.

Narayan

 

[jsk_lge]

No need to post data here, sir this formula is perfect.

Thank you Narayan.