CODEWITHSUNDEEP
excelif-statementexcel-formula
Darious
Darious

Reputation: 121

How can simplify the nested if condition in excel formula

The excel formula is, 

=IF(OR(B42="First Proof",B42="Revised First Proof"),IF(WEEKDAY(J42-1)=1,J42-2,J42-1),IF(OR(B42="Revises2",B42="Revises3",B42="Revises4",B42="Revises5",B42="Revises6",B42="Revises7"),IF(WEEKDAY(Q42-1)=1,Q42-2,Q42*1),IF(B42="Revises1",IF(WEEKDAY(J42-1)=1,J42-2,J42-1),IF(B42="Typescript",IF(WEEKDAY(J42-1)=1,J42-2,J42-1),IF(B42="Finals",IF(WEEKDAY(J42-1)=1,J42-2,J42-1),IF(OR(B42="ELDs",B42="Post Conversion",B42="Revised ELDs"),"NA"))))))

Please help me to reduce the formula length.

Upvotes: 1

Views: 82

Answers (1)

user4039065
user4039065

Reputation:

Don't forget that you can use alt+enter and spaces to format multi-line formulas and improve readability.

=IF(or(right(B42, 11)="first proof", or(b42={"revises1", "typescript", "finals"})), J42-1-(WEEKDAY(J42, 2)=1),
 IF(AND(LEFT(B42, 7)="Revises", OR(RIGHT(B42)={"2","3","4","5","6","7"})), Q42-(WEEKDAY(Q42, 2)=1)*2,
 IF(or(b42={"elds", "post conversion", "revised elds"}), "NA", "")))

Reduced from 437 characters to ~275 characters and the OR(RIGHT(B42)={"2","3","4","5","6","7"}) isn't even necessary as Revises1 has already been checked for.

Upvotes: 1

Related Questions