Spill Error

%3CLINGO-SUB%20id%3D%22lingo-sub-1685694%22%20slang%3D%22en-US%22%3ESpill%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1685694%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20in%20a%20class%20and%20the%20professor%20and%20I%20cannot%20figure%20out%20why%20it%20keeps%20giving%20the%20error%20for%20me.%20I%20send%20her%20my%20file%20and%20she%20will%20type%20in%20the%20formula%20the%20same%20that%20I%20do%20and%20it%20works%20for%20her%2C%20however%2C%20when%20I%20type%20it%20in%20at%20home%20it%20says%20Spill.%20Can%20anyone%20please%20help%20me%20figure%20out%20why%20it%20will%20not%20work%20for%20me%2C%20but%20will%20for%20her%3F%20I%20am%20attaching%20the%20pictures%20of%20the%20three%20formulas%20that%20keep%20doing%20the%20error%20for%20me.%20Thank%20you%20so%20much.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%2220200918_141517.jpg%22%20style%3D%22width%3A%20749px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219701i4B1696C4E0574277%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%2220200918_141517.jpg%22%20alt%3D%2220200918_141517.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%2220200918_141541.jpg%22%20style%3D%22width%3A%20749px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219702iBEA8EF1DFA9C3A62%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%2220200918_141541.jpg%22%20alt%3D%2220200918_141541.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%2220200918_141554.jpg%22%20style%3D%22width%3A%20749px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219703i9C3ECD0F2832ABE0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%2220200918_141554.jpg%22%20alt%3D%2220200918_141554.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1685694%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-1685740%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1685740%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F799306%22%20target%3D%22_blank%22%3E%40tommygirl73701%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20your%20professor%20on%20version%20of%20Excel%20which%20doesn't%20support%20dynamic%20arrays%20functionality.%20Your%20version%20supports%2C%20looks%20like%20you%20are%20on%20Office%20365%20subscription.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20the%20formulas%20like%20on%20your%20screenshot%20%3DIF(%5BCollege%20Graduate%5D%3D%22Yes%22%2C%22Yes%22%2C%22No%22)%20you%20apply%20condition%20to%20entire%20column%26nbsp%3B%5BCollege%20Graduate%5D.%20Pre-DA%20Excel%20silently%20do%20implicit%20intersection%20and%20takes%20only%20one%20element%20form%20the%20current%20row.%20DA%20Excel%20applies%20formula%20to%20entire%20column%20and%20returns%20an%20array%20with%20results%20for%20entire%20column%2C%20such%20returned%20array%20is%20called%20spill.%20Since%20you%20are%20within%20table%2C%20Excel%20can't%26nbsp%3B%20return%20entire%20spill%20into%20only%20one%20cell%20to%20which%20formula%20is%20applied.%20Thus%20you%20have%20%23SPILL!%20error.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20avoid%20it%20explicitly%20use%20formula%20only%20for%20the%20current%20row%20as%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%5B%40%5BCollege%20Graduate%5D%5D%3D%22Yes%22%2C%22Yes%22%2C%22No%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhich%20means%20you%20take%20value%20of%20Column%20Graduate%20only%20for%20the%20column%20row%20and%20apply%20formula%20to%20it.%20Excel%20automatically%20adds%20formula%20to%20all%20other%20cells%20in%20this%20column%20of%20the%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

I am in a class and the professor and I cannot figure out why it keeps giving the error for me. I send her my file and she will type in the formula the same that I do and it works for her, however, when I type it in at home it says Spill. Can anyone please help me figure out why it will not work for me, but will for her? I am attaching the pictures of the three formulas that keep doing the error for me. Thank you so much. 20200918_141517.jpg20200918_141541.jpg20200918_141554.jpg

1 Reply

@tommygirl73701 

That's since your professor on version of Excel which doesn't support dynamic arrays functionality. Your version supports, looks like you are on Office 365 subscription.

 

For the formulas like on your screenshot =IF([College Graduate]="Yes","Yes","No") you apply condition to entire column [College Graduate]. Pre-DA Excel silently do implicit intersection and takes only one element form the current row. DA Excel applies formula to entire column and returns an array with results for entire column, such returned array is called spill. Since you are within table, Excel can't  return entire spill into only one cell to which formula is applied. Thus you have #SPILL! error.

 

To avoid it explicitly use formula only for the current row as 

=IF([@[College Graduate]]="Yes","Yes","No")

which means you take value of Column Graduate only for the column row and apply formula to it. Excel automatically adds formula to all other cells in this column of the table.