SOLVED

Spill Error with Filtering Random Numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-1530447%22%20slang%3D%22en-US%22%3ESpill%20Error%20with%20Filtering%20Random%20Numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1530447%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BI'm%20trying%20to%20do%20a%20simple%20Filter%20on%20a%20sample%20data%20and%20getting%20Spill%20errors.%20Can't%20seem%20to%20explain%20why%20it's%20happening.%20So%20basically%20I%20have%2012%20months%20on%20one%20column%20and%20random%20numbers%20from%200-100%20in%20the%20next%20column.%20A%20simple%20Filter%20to%20get%20values%20%26gt%3B50%20works%20most%20of%20the%20time.%20Sometimes%20it%20returns%20a%20%23SPILL%20error.%20There%20is%20nothing%20below%20the%20cells%20to%20cause%20this.%20I'm%20attaching%20the%20sample%20file.%20To%20test%20this%20try%20hitting%20the%20F9%20key%20a%20few%20times%20to%20trigger%20the%20error.%20It%20does%20not%20happen%20if%20the%20numbers%20are%20non-random.%20Has%20anyone%20seen%20this%20error%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMost%20of%20the%20time%20this%20works%20flawlessly%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheAntony_1-1595042525300.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206169i28D4C9F4E5D070EA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TheAntony_1-1595042525300.png%22%20alt%3D%22TheAntony_1-1595042525300.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESometime%20it%20errors%20out%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheAntony_0-1595042481587.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206168i36779BD5D3E4EA10%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TheAntony_0-1595042481587.png%22%20alt%3D%22TheAntony_0-1595042481587.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1530447%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1530676%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20with%20Filtering%20Random%20Numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1530676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DiaJv6E0GRC4%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EMrExcel%20explains%20it%20all.%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1530687%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20with%20Filtering%20Random%20Numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1530687%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%2C%20Thank%20you%20for%20that!%20It%20is%20interesting%20that%20it%20works%2090%25%2B%20times.%20Good%20to%20know%20it's%20a%20known%20issue%20and%20hopefully%20will%20get%20resolved%20in%20a%20future%20update.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531292%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20Error%20with%20Filtering%20Random%20Numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531292%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20think%20such%20behaviour%20will%20be%20changed%20in%20nearest%20future.%20My%20guess%20it%20requires%20quite%20fundamental%20changes%20in%20calc%20engine.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hi All

   I'm trying to do a simple Filter on a sample data and getting Spill errors. Can't seem to explain why it's happening. So basically I have 12 months on one column and random numbers from 0-100 in the next column. A simple Filter to get values >50 works most of the time. Sometimes it returns a #SPILL error. There is nothing below the cells to cause this. I'm attaching the sample file. To test this try hitting the F9 key a few times to trigger the error. It does not happen if the numbers are non-random. Has anyone seen this error?

 

Most of the time this works flawlessly:

TheAntony_1-1595042525300.png

 

Sometime it errors out:

TheAntony_0-1595042481587.png

 

3 Replies
Best Response confirmed by TheAntony (Regular Contributor)

@Detlef Lewin , Thank you for that! It is interesting that it works 90%+ times. Good to know it's a known issue and hopefully will get resolved in a future update. 

@TheAntony 

I don't think such behaviour will be changed in nearest future. My guess it requires quite fundamental changes in calc engine.