SOLVED

#SPILL! Error when doing simple calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-1410559%22%20slang%3D%22en-US%22%3E%23SPILL!%20Error%20when%20doing%20simple%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410559%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings!%3CBR%20%2F%3EI%20have%20had%20this%20problem%20in%20the%20past%20few%20months%20I%20think%2C%20after%20having%20used%20it%20for%2020%20years.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EOften%20I%20add%20or%20subtract%20columns%2C%20such%20as%26nbsp%3B%3D%2BX%3AX%2BI%3AI%2C%20and%20never%20had%20any%20issue.%3C%2FP%3E%3CP%3EBoth%20Columns%20X%20and%20I%20are%20simple%20numeric%20fields%2C%20be%20it%20Dollars%20or%20Unit.%3C%2FP%3E%3CP%3EHowever%2C%20lately%20it%20always%20return%20with%20errof%20%23SPILL%20and%20wont%20do%20anything.%26nbsp%3B%3CBR%20%2F%3EChecked%20the%20Online%20Help%20and%20it%20says%20%22%3CSPAN%3EThe%20spilled%20array%20formula%20you're%20attempting%20to%20enter%20will%20extend%20beyond%20the%20worksheet's%20range.%20Try%20again%20with%20a%20smaller%20range%20or%20array.%22%20which%20is%20not%20the%20case.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAppreciate%20any%20input%20from%20the%20Experts%20here.%20Thank%20you!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1410559%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410692%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20simple%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410692%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676396%22%20target%3D%22_blank%22%3E%40Pumpkin888%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%20only%20worked%20because%20of%20implicit%20intersection.%3C%2FP%3E%3CP%3ENow%20with%20new%20calculation%20engine%20you%20have%20to%20change%20it%20to%20X2%2BI2%20and%20copy%20down.%3C%2FP%3E%3CP%3EOr%20keep%20it%20in%20the%20first%20cell%20and%20delete%20the%20rows%20below.%20Now%20the%20formula%20spills.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410742%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20simple%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20your%20response.%20Sorry%20I%20dont%20quite%20know%20what%20%22%3CSPAN%3Eimplicit%20intersection%22%20is%20even%20googled%2C%20but%20suffice%20to%20say%20I%20much%20prefer%20just%20to%20use%20a%20column%2C%20instead%20of%20a%20fixed%20field.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20used%20to%20be%20able%20to%20do%20this%20in%20the%20past%2010%20years%20...%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1410837%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20simple%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1410837%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676396%22%20target%3D%22_blank%22%3E%40Pumpkin888%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CSPAN%3Ebut%20suffice%20to%20say%20I%20much%20prefer%20just%20to%20use%20a%20column%2C%20instead%20of%20a%20fixed%20field.%26nbsp%3B%3C%2FSPAN%3E%3CP%3E%3CSPAN%3EI%20used%20to%20be%20able%20to%20do%20this%20in%20the%20past%2010%20years%20...%3C%2FSPAN%3E%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EThe%20keyword%20is%20%22new%20calculation%20engine%22.%20Either%20you%20adapt%20to%20this%20or%20you%20have%20to%20revert%20from%20Excel%20365%20to%20Excel%202019.%3C%2FP%3E%3CP%3EThere%20is%20a%20third%20option%20for%20the%20formula%20which%20enforces%20implicit%20intersection%3A%20%3D%40%3CSPAN%3EX%3AX%2B%40I%3AI.%20And%20copy%20down.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415186%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20Error%20when%20doing%20simple%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415186%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWow%2C%20that%20works!!%20Beautiful%2C%20fantastic.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%20Lewin!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Greetings!
I have had this problem in the past few months I think, after having used it for 20 years.  

Often I add or subtract columns, such as =+X:X+I:I, and never had any issue.

Both Columns X and I are simple numeric fields, be it Dollars or Unit.

However, lately it always return with errof #SPILL and wont do anything. 
Checked the Online Help and it says "The spilled array formula you're attempting to enter will extend beyond the worksheet's range. Try again with a smaller range or array." which is not the case.

 

Appreciate any input from the Experts here. Thank you!

4 Replies
Highlighted

@Pumpkin888 

Your formula only worked because of implicit intersection.

Now with new calculation engine you have to change it to X2+I2 and copy down.

Or keep it in the first cell and delete the rows below. Now the formula spills.

 

Highlighted

@Detlef Lewin 

Many thanks for your response. Sorry I dont quite know what "implicit intersection" is even googled, but suffice to say I much prefer just to use a column, instead of a fixed field. 

I used to be able to do this in the past 10 years ...

Highlighted
Best Response confirmed by Pumpkin888 (New Contributor)
Solution

@Pumpkin888 

 


but suffice to say I much prefer just to use a column, instead of a fixed field. 

I used to be able to do this in the past 10 years ...


The keyword is "new calculation engine". Either you adapt to this or you have to revert from Excel 365 to Excel 2019.

There is a third option for the formula which enforces implicit intersection: =@X:X+@I:I. And copy down.

 

Highlighted

@Detlef Lewin 

Wow, that works!! Beautiful, fantastic. 

Thanks so much Lewin!