IF AND Formula

[EvansB2]

Hi there,

The uploaded example shows a simple set of data and the condition in D2 should be returned as "No" as long as their is a value in cell B2, i.e. above zero, and cell C2 is higher than cell A2. If no value exists in cell B2, then it should return "not applicable". The case might also be that cells C2 and A2 have the same number, if this is the case then D2 should return "Yes"

Any help would be much appreciated.

Ben

Field01	Field02	Field03	Field04
12	£2,000	35	"NO"

 

[kweaver]

Is this what you mean?

=IF(ISBLANK(B2),"Not applicable",IF(AND(B2>0,C2>A2),"NO","YES"))

 

[Fluff]

Another option if B2 can never be negative

=IF(B2="","Not applicable",IF(C2>A2,"NO","YES"))

 

[EvansB2]

Almost. The formula would benefit from replacing the ISBLANK with if B2 was greater than zero. This is because it is someone else's sheet and all nil value cells have a value, i.e.£0 and are not blank. I don't want to re-format it. Does that makes sense?

Can you show me what the new formula would look like in this instance?

Ben

 

[Fluff]

Can B2 be blank or "", or will it always have a number?

 

[EvansB2]

It will always have a number.

Ben

 

[Fluff]

Ok, how about

=IF(B2<=0,"Not applicable",IF(C2>A2,"NO","YES"))

 

[EvansB2]

Brilliant. Thanks for your time.

Regards

Ben

 

[Fluff]

Glad we could help & thanks for the feedback.