Too Many If Statements in formula?

[Cammandk]

I have the code below - which is not working. I think I have too many If statements.
This formula is in V40.
I also need to add in one further condition.

=IF(!AT40=1,"P",IF(!I40="F","F",IF(OR(!I40="O","B"),"A",IF(!I40="P1","U",IF(!I40="P","A",IF(!I40="I","U",IF(!I40="S","U",IF(!I40="S1","U",IF(!I40="E","U",IF(OR(!I40="R1","R2"),"P",""))))))))))

What needs to happen is:
If AT40 = 1 then V40 = "P". This overrides everything else (can made the 1 a "P" if it helps)
If I40 = "F" then V40 = "F"
If I40 = "I" then V40 = "U"
If I40 = "O" or "B" then V40 = "A"
If I40 = "R1" or "R2" then V40 = "P"
If I40 = "S1" or "P1" AND J40 is greater than TODAY() then "A" otherwise "U"
IF I40 blank then V40 is blank.

DK

 

[Roseus]

I would string several REPT functions together to accomplish this more cleanly, without worrying about hitting the IF limit.

In V40:

=REPT("P",AT40=1)&REPT("F",I40="F")&REPT("U",I40="I")&REPT("A",or(I40="O",I40="B") etc...

If none of those conditions are met, the output is a blank, per your last req.

 

[Cammandk]

Hi Roseus
Thanks for this.
How would the need for the > Today() condition fit into this. (2nd last line on my listing).

DK

 

[Roseus]

That line is a little inspecific, I could interpret more than one way...

Let me ask this - if I40 was "AAA", would the output would be "U" because it isn't "S1 or P1" or any of the other items?

 

[Somendra Misra]

Hi DK,

See the attached file (and note left in RED colour). Please note solution in file is not a single formula solution, but involve a helper table.

Just advise if any issue.

Regards,

 

[Cammandk]

Hi Somendra

Thanks for this. I do need the solution to be in a single formula.
I understand how to get the basic "I"/"O" etc to provide the required result the bit that I am struggling with is
getting the "S1" & "P1" to work with TODAY() and then provide the correct answer depending on the date.

If I40 = "S1" or "P1" AND (J40 (date) is greater than TODAY() ) then "A" otherwise "U"

DK

 

[Faseeh]

Hi Cammandk,

try this:

=IF(AT40=1,IF(I40<>AND(OR(I40="S1",I40="P1")),IF(AND(OR(I40="S1",I40="P1"),J40>TODAY()),"A","U"),CHOOSE(MATCH(I40,{"F","I","O","B","R1","R2"},0),"F","U","A","A","P","P")),"…")

with Ctrl+Shift+Enter

 

[Cammandk]

Hi Faseeh
I can't get your solution to work. I copied into my real sheet.
Then I opened up your file and that doesn't give the expected results?
I40 = F / J40 no date / V40 should be "F"?

DK.

 

[Faseeh]

Please use this one in V40

=IF(AT40=1,IF(I40<>AND(OR(I40="S1",I40="P1")),IF(AND(OR(I40="S1",I40="P1"),J40>TODAY()),"A","U"),CHOOSE(MATCH(I40,{"F","I","O","B","R1","R2"},0),"F","U","A","A","P","P")),"…")

 

[Cammandk]

This looks exactly the same formula above? Still not getting result. With P1 in I40 and date in J40 getting "..." in V40?
DK

 

[Faseeh]

Hi DK,

Let me explain what this formula was assumed to do from my side..
1. If I40 contain S1 or P1 with date greater then today, it should show A, else should show U.
2. If I40 contains any thing else,it will discard whatever fhw date may be, and will
give corresponding character.
3. If 1 is blank it will show dots.

Is that what u needed?

 

[Cammandk]

Hi Faseeh
Not quite.

If AT40 = 1 then this takes precedence and V40 become "P"

Your point 1 correct.
Your point 2 correct - if this matches up the F/I/B/O to be F/U/A/A

If I40 is blank - V40 is blank

 

[Luke M]

Hi Faseeh,
I think something got copied wrong in your formula. This look odd:
...IF(I40<>AND(OR(I40="S1",I40="P1"))...
as I40 is not a Boolean.

 

[Luke M]

Taking my own stab at it:
=IF(AT40=1,"P",
IF(OR(I40="S1",I40="P1"),IF(J40>TODAY(),"A","U"),
CHOOSE(MATCH(I40,{"","F","I","O","B","R1","R2"},0),"","F","U","A","A","P","P")))

 

[Faseeh]

@Luke,

Yup you are correct, i just rechecked it, but i am surprises because i checked it on my pc and then i copied the formula and now it is producing wrong result! :/ anyways thank for pointing in right direction.

@Cammandk

Please refer to Luke's formula that should work!

 

[Cammandk]

Faseeh /Luke
Thanks for this guys.
Luke I can see that this should work. I've copied across but get a #N/A result if field. Any thoughts.
DK.

 

[Luke M]

Depending on which cells needed to stay absolute references, and which were relative, would determine how to fix formula. Taking a wild guess that col AT and J are constant, but I should change:
=IF($AT40=1,"P",
IF(OR(I40="S1",I40="P1"),IF($J40>TODAY(),"A","U"),
CHOOSE(MATCH(I40,{"","F","I","O","B","R1","R2"},0),"","F","U","A","A","P","P")))

if my wild guess was not right, please let us know what the actual layout looks like, and we can go from there.

 

[Cammandk]

If I add a code into I40 then the #N/A goes away. When I40 is blank get #N/A

 

[NARAYANK991]

Hi David ,

If you can use the IFERROR function , you can use this :

=IF($AT40=1,"P",IF(OR(I40="S1",I40="P1"),IF($J40>TODAY(),"A","U"),IFERROR(CHOOSE(MATCH(I40,{"F","I","O","B","R1","R2"},0),"F","U","A","A","P","P"),"")))

So whenever the MATCH function does not find a match , whether because the cell I40 is blank , or because it contains any letter other than those specified , the cell will display a blank.

Narayan