Excel formulas

Occasional Contributor
Hi ,
I am having trouble translating this formula and wondered if I could get some help.
=IF(p3<>1,IF(AND(L3=1,OR(S3=1,U3=1,W3=1,AE3=1,AL3=1,OR(AND(E3=DATE(2020,2,2),F3=DATE(2020,12,31))))),1,0)0)

I am having trouble with it all specially how the date fields come in to the equation.
4 Replies

@Mel4342 

=IF(P3<>1,IF(AND(L3=1,OR(S3=1,U3=1,W3=1,AE3=1,AL3=1,OR(AND(E3=DATE(2020,2,2),F3=DATE(2020,12,31))))),1,0),0)

 

I suppose a single " , " before the last " 0 " is missing in the formula.

Can you please translate this formula. I think it's reading like this.
If p3 not equal to 1 and l3=1 and one following equals 1. S3,u3,w3,ae3,al3 or
P3<>1 and l3 and either e3=date(2020,2,2) or f3=date(2020,12,31) then result is 1. If not result is zero is that correct?

@Mel4342 

You wrote, "I am having trouble translating this formula," and I just want to confirm that what you are wanting to do is translate the formula into words ... i.e., you are wanting to say in plain English what the IFs, ANDs and Ors are doing...

 

I wish you well. I'll take a stab at it down below, but have to say that this formula is (or could be) a case study in why Excel text books warn against nesting conditions too many levels deep.

 

It's particularly difficult for anybody just looking at the formula, not knowing what P3, L3, S3, etc are (beyond 1 or not 1). Since that value could also mean TRUE or NOT TRUE, then one has to wonder what is the condition that is either true or not true..... Related to that last point, it would appear that the end result of the whole formula is 1 or 0,  TRUE or FALSE. 

 

Is it safe to assume that this formula has come to you, but was written by somebody else? Do you know what the "business purpose" of the spreadsheet is, and are you in a position to help us help you by spelling out that more full picture, as well as what values are reflected in those cell references?

 

On a superficial level, (and I'm not all that confident on this interpretation)

  • if the dates in E3 and F3, respectively, are 2/2/20 and 12/31/20 -- presumably indicating a period of time very precisely defined--then the result is 1/TRUE
  • It would also be true if P3 is not true, but L3 is true, and any one of the remaining references (S3, W3, AE3,  AL3) is true 

@Mel4342 

=IF(OR(AND(P3<>1,I3=1,OR(S3=1,U3=1,W3=1,AE3=1,AL3=1)),AND(P3<>1,I3=1,OR(E3=DATE(2020,2,2),F3=DATE(2020,12,31)))),1,0)

 

In my understanding what you are looking for should be above formula.