What is the maximum length of a formula within a cell?

Ben Scholz

The following is 4824 characters and it seems too long??

=IF([M1]$44 <> "",SUM(SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[P&L Impacted]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[P&L Impacted]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0))), INDEX(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""), COUNT(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""))), INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0))), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Cost Avoidance]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Cost Avoidance]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0))), INDEX(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""), COUNT(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""))), INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0))), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Type of Impact]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Type of Impact]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Forecast", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0))), INDEX(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""), COUNT(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""))), INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0)))),SUM(SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[P&L Impacted]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[P&L Impacted]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0))), INDEX(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""), COUNT(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""))), INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0))), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Cost Avoidance]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Cost Avoidance]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0))), INDEX(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""), COUNT(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""))), INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0))), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Type of Impact]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "USD"), SUMIFS([M1]$54:[M1]$134, $[P&L Impacted]$54:$[P&L Impacted]$134, $[Type of Impact]@row, $[nonEBITDA?]$54:$[nonEBITDA?]$134, false, $[Impact Name]$54:$[Impact Name]$134, "Planned", $Currency$54:$Currency$134, "CAD") * IF(ISBLANK(INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0))), INDEX(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""), COUNT(COLLECT({Conversion Rate Table - Rate}, {Conversion Rate Table - Rate}, <>""))), INDEX({Conversion Rate Table - Rate}, MATCH([M1]$10, {Conversion Rate Table - Month}, 0)))))

Ramzi K

@Ben Scholz

There is a 4K character limit on formulas. Try a abbreviating your references to bring it down in size or simplify by using several columns to break up the calculations and then using a formula to consolidate.

For Example: Instead of using {Conversion Rate Table - Month} as a reference name, try using something like {CRT - Month}

I hope this helps.

Cheers,

Ramzi

Ramzi K

@Ben Scholz

There is a 4K character limit on formulas. Try a abbreviating your references to bring it down in size or simplify by using several columns to break up the calculations and then using a formula to consolidate.

For Example: Instead of using {Conversion Rate Table - Month} as a reference name, try using something like {CRT - Month}

I hope this helps.

Cheers,

Ramzi

Ben Scholz

@Ramzi

Thank you for your response - I'm implementing now...........however I noticed the next issue with my formula.......

In the "SUM(SUMIFS([M1]$54:[M1]$134, ...." the formula is being put into a cell in the M1 column. I want to repeat the process in M2 column, M3 ....... M48 column and had hoped to use a global update (as I have 100s of sheets to add this to).

Is there special code to allow the column name to be dynamic and to move with and correspond to the same column that the formula is being placed?

Thanks!😚

Ramzi K

@Ben Scholz

As far as you have not locked your column in the formula (as in $[M1], etc. you should be able to drag copy the formula across all columns and it will automatically take on the columns it's in.

Are you using Control Center? You referred to "global update" so I thought I would ask. If you are, that may be a bit more challenging if you are trying to do this in bulk.

Cheers,

Ramzi