Forum Discussion

Archie077's avatar
Archie077
Copper Contributor
Aug 11, 2020

If and ISText formula

hello everyone

Good day.

 

https://1drv.ms/x/s!An7MAXsT8f3LhCEcu353KfHzIMZz?e=dPgx5Q 

 

I'm currently stuck in my formula, And i need to incorporate logics #4 to #6 to this formula set.

=IF(ISBLANK(A3),"new",IF(A3="invalid","closed",IF(AND(ISTEXT(B3),A3="Valid"),"In-Progress"

 

logics:

1. if a2 is blank value is " new"
2. if a2=invalid, value is "closed"
3. if a2=valid + b2 (istext) = "in progress"
4. if a2=valid+b2(istext)+c2(istext)="assigned"
5. if a2=valid+b2(istext)+c2(istext)+d3(istext) = "reopen"
6. if a2=valid+b2(istext)+c2(istext)+d3(istext)+e4(istext) = "closed"

 

thank you for your assistance

excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 12, 2020

    Archie077 

    Perhaps

     

    =IF( ISBLANK(A2), " new",
  IF(A2="invalid", "closed",
  IF( (A2="valid"),
  IF( ISTEXT(B2),
  IF( ISTEXT(C2),
  IF( ISTEXT(D3),
  IF( ISTEXT(E4),
    "closed",
    "reopen"),
    "assigned"),
    "in progress")
))))

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Aug 12, 2020

    Archie077 You need to build your formula, starting with the last criteria. If "valid" and all other cells contain text, then "Closed" and so on. If you don't do that, once the third condition is met it will always  returns "in progress" and never evaluate the last three IF statements. You may also consider using IFS. That would then look something like this:

    =IFS(logic6,"closed",logic5,"reopen",logic4,"assigned",logic3,"in progress",logic2,"closed",logic1,"new")

    Not convinced though that this is the most effective way to achieve your ultimate goal. Would need to see your actual schedule to recommend possible alternatives.

     

Resources