Skip to content

Formula Organization

April 12, 2012

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

From → Uncategorized

One Comment
  1. I use Alt+Enter all of the time. It makes things much simpler to type within a range. Yes, you have to be a pro to follow some of your answers. You probably lost many of the people!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: