Formula Organization

Here’s a little tip for helping to organize complicated formulas.

ALT+ENTER

It works for text and formulas. ALT+ENTER sends whatever you’re typing to the next row within the same cell. If it’s text and you have wrap-around turned on it’s helpful for Headers (and etc.) but that is not why I’m mentioning ALT+ENTER.

Here’s an example of a (very) complicated formula.

WITHOUT ALT+ENTER

=IF(Start_Linear_On=”YES”,IF(AND(ISERROR(1/COUNTIFS(Linear_Plant, Start_Plant_Number,Liner_Component_Number,$B2))FALSE,Start_Linear_ Plant=”YES”)=TRUE,SUMIFS(Linear_Quantity_per_Carton,Linear_Plant,St art_Plant_Number,Liner_Component_Number,$B2)/COUNTIFS(Linear_Pla nt,Start_Plant_Number,Liner_Component_Number,$B2),IF(Start_Linear_Pl ant=”YES”,SUMIFS(Linear_Quantity_per_Carton,Linear_Plant,”ALL”,Liner _Component_Number,$B2)/COUNTIFS(Linear_Plant,”ALL”,Liner_Compon ent_Number,$B2),VLOOKUP($B2,dim,2,FALSE)))*IF(Start_Linear_Pallet= “YES”,MAX(1,IF(ISERROR(1/SUMIFS(Linear_Pallet_size_Multiplier,Linear _Plant,Start_Plant_Number,Liner_Component_Number,$B2))=TRUE,IF(IS ERROR(1/COUNTIFS(Linear_Plant,”ALL”,Liner_Component_Number,$B2 ))=TRUE,1,SUMIFS(Linear_Pallet_size_Multiplier,Linear_Plant,”ALL”,Lin er_Component_Number,$B2)/COUNTIFS(Linear_Plant,”ALL”,Liner_Comp onent_Number,$B2)),MAX(1,IF(ISERROR(1/COUNTIFS(Linear_Plant,Star t_Plant_Number,Liner_Component_Number,$B2))=FALSE,IF(Start_Linear _Plant=”YES”,SUMIFS(Linear_Pallet_size_Multiplier,Linear_Plant,Start_P lant_Number,Liner_Component_Number,$B2)/COUNTIFS(Linear_Plant,St art_Plant_Number,Liner_Component_Number,$B2),VLOOKUP($B2,dim,3, FALSE)),1)))),1),#N/A)

WITH ALT+ENTER

=IF(Start_Linear_On=”YES”,

IF(AND(ISERROR(1/COUNTIFS(Linear_Plant,Start_Plant_Number, Liner_Component_Number,$B2))=FALSE,Start_Linear_Plant=”YES”)=TRUE,

SUMIFS(Linear_Quantity_per_Carton,Linear_Plant,Start_Plant_Number, Liner_Component_Number,$B2)

/

COUNTIFS(Linear_Plant,Start_Plant_Number,Liner_Component_Number,$B2),

IF(Start_Linear_Plant=”YES”,

SUMIFS(Linear_Quantity_per_Carton,Linear_Plant,”ALL”,Liner_Component_Number,$B2)

/

COUNTIFS(Linear_Plant,”ALL”,Liner_Component_Number,$B2),

VLOOKUP($B2,dim,2,FALSE)))

*

IF(Start_Linear_Pallet=”YES”,

MAX(1,IF(ISERROR(1/SUMIFS(Linear_Pallet_size_Multiplier,Linear_Plant, Start_Plant_Number,Liner_Component_Number,$B2))=TRUE, IF(ISERROR(1/COUNTIFS(Linear_Plant,”ALL”,Liner_Component_Number, $B2))=TRUE,

1,

SUMIFS(Linear_Pallet_size_Multiplier,Linear_Plant,”ALL”, Liner_Component_Number,$B2)

/

COUNTIFS(Linear_Plant,”ALL”,Liner_Component_Number,$B2)),

MAX(1,IF(ISERROR(1/COUNTIFS(Linear_Plant,Start_Plant_Number, Liner_Component_Number,$B2))=FALSE,

IF(Start_Linear_Plant=”YES”,

SUMIFS(Linear_Pallet_size_Multiplier,Linear_Plant,Start_Plant_Number, Liner_Component_Number,$B2)

/

COUNTIFS(Linear_Plant,Start_Plant_Number,Liner_Component_Number,$B2),

VLOOKUP($B2,dim,3,FALSE)),1)))),1),

#N/A)

…maybe it’s an overcomplicated answer but it can be helpful for separating information to help change the formula later if needed.

Good Luck!

About these ads