Home
%3CLINGO-SUB%20id%3D%22lingo-sub-252944%22%20slang%3D%22en-US%22%3EPreview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-252944%22%20slang%3D%22en-US%22%3E%3CDIV%3EUntil%20now%2C%20you%20wrote%20a%20formula%20for%20each%20value%20you%20wanted%20returned%20to%20the%20grid.%20One%20formula%2C%20one%20value.%20If%20you%20wanted%20another%20value%2C%20you%20wrote%20(or%20copied)%20another%20formula.%20With%20dynamic%20arrays%2C%20that%20all%20changes.%20Now%2C%20you%20can%20write%20a%20formula%20hit%20the%20enter%20key%20and%20get%20an%20array%20of%20values%20returned.%20One%20formula%2C%20many%20values.%20This%20will%20allow%20you%20to%20build%20more%20capable%20spreadsheets%2C%20faster%2C%20with%20fewer%20formulas%20and%20less%20chance%20of%20error.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3ETo%20harness%20the%20power%20of%20dynamic%20arrays%2C%20we%E2%80%99ve%20added%20some%20amazing%20new%20functions.%20So%2C%20for%20instance%2C%20you%20can%20use%20the%20SORT%20function%20to%20sort%20a%20list%2C%20the%20UNIQUE%20function%20to%20remove%20duplicates%20from%20that%20list%2C%20then%20use%20the%20FILTER%20function%20to%20get%20just%20what%20you%20want%20from%20the%20list.%20And%20when%20your%20data%20changes%2C%20the%20dynamic%20array%20will%20resize%20and%20recalculate%20automatically!%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20664px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F49842iF3080AD39E83CD79%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Dynamic%20Arrays%20Blog%20GIF%20final.gif%22%20title%3D%22Dynamic%20Arrays%20Blog%20GIF%20final.gif%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EDynamic%20arrays%20in%20action%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSTRONG%3ESpilling%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CDIV%3EWe%20call%20the%20behavior%20of%20placing%20values%20in%20neighboring%20blank%20cells%20%22spilling%E2%80%9D%2C%20and%20you%E2%80%99ll%20see%20Excel%20indicate%20the%20formula%E2%80%99s%20%E2%80%9Cspill%20range%22%20with%20a%20thin%20blue%20border%20when%20you%20select%20any%20cell%20inside%20the%20spill%20range.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EDon%E2%80%99t%20worry%20about%20the%20spill%20range%20overlapping%20your%20data--if%20there%20isn%E2%80%99t%20enough%20space%2C%20the%20formula%20will%20roll%20up%20and%20show%20an%20informative%20%23SPILL%20error.%20When%20you%20select%20the%20%23SPILL%20error%2C%20the%20formulas%20desired%20spill%20range%20will%20be%20indicated%20by%20a%20dashed%20blue%20border.%20Just%20move%20or%20delete%26nbsp%3Bthe%20obstructing%20data%20and%20your%20formula%20will%20automatically%20spill.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20796px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F49846i666D1346B0D0C83D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22NoSpill%20Blog%20GIF%20final.gif%22%20title%3D%22NoSpill%20Blog%20GIF%20final.gif%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESpilling%20range%20not%20blank%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3ENative%20to%20Excel%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3EDynamic%20array%20support%20is%20deeply%20integrated%20into%20Excel%20and%20it's%20not%26nbsp%3Blimited%20to%20the%20functions%20shipping%20alongside%20it%20--%20any%20newly%20authored%20formula%20that%20returns%20an%20array%20will%20spill.%20For%20instance%2C%20entering%20%3DA3%3AA13%20into%20B3%20will%20cause%20the%20values%20in%20A3%3AA13%20to%20be%26nbsp%3Bspilled%20into%20B3%3AB13.%20And%20just%20like%20Excel's%20grid%2C%20dynamic%20arrays%20can%20be%202%20dimensional%20as%20shown%20in%20the%20multiplication%20table%20example%20below.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20796px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F49850iA547788EF03A8562%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Integrated%20Blog%20GIF%20final.gif%22%20title%3D%22Integrated%20Blog%20GIF%20final.gif%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EUsing%20dynamic%20arrays%20with%20existing%20functions%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3EReferencing%20the%20spill%20range%20using%20A1%23%20notation%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3EDynamic%20arrays%26nbsp%3Bmay%20seamlessly%20resize%20as%20your%20data%20changes.%20To%20make%20it%20easy%20to%20reference%20resizing%20dynamic%20arrays%2C%20we%20are%20adding%20a%20way%20to%20reference%20the%20entire%20spill%20in%20a%20dependable%2C%20resilient%20way.%20You%20can%20do%20this%20by%20following%20a%20cell%20reference%20with%20the%20%23%20symbol%2C%26nbsp%3Bfor%20example%20A1%23.%20This%20is%20equivalent%20to%20referencing%20the%20entire%20spilled%20range%20for%20the%20dynamic%20array%20in%20A1.%20We'll%20default%20to%20this%20style%20reference%20whenever%20you%20write%20a%20formula%20that%20refers%20to%20the%20entire%20spill%20range.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EIn%20the%20example%20below%20notice%20how%20the%20SUMIF%20function%20is%20using%20all%20the%20product%20names%20from%20the%20dynamic%20array%20in%20D5.%20When%20Grapes%20is%20added%20to%20the%20sales%20table%2C%20the%20D5%20spill%20range%20grows%20and%20so%20does%20the%20result%20of%20the%20SUMIF%20because%20it%20references%20D5%23%20rather%20than%20D5%3AD8.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20796px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F49849iC1EC1C37DBEE8878%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Spill%20Range%20Reference%20Blog%20GIF%20final.gif%22%20title%3D%22Spill%20Range%20Reference%20Blog%20GIF%20final.gif%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EUsing%20%23%20to%20refer%20to%20the%20spill%20range%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3ENew%20Functions%3C%2FSTRONG%3E%3CBR%20%2F%3EHere%20is%20the%20full%20set%20of%20functions%20that%20will%20be%20accompanying%20dynamic%20arrays.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%E2%80%A2%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2Ff4f7cb66-82eb-4767-8f7c-4877ad80c759%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFILTER%20%3C%2FA%3E-%20filters%20an%20array%20of%20data%20based%20on%20criteria%20you%20define.%3C%2FDIV%3E%0A%3CDIV%3E%E2%80%A2%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2Fc5ab87fd-30a3-4ce9-9d1a-40204fb85e1e%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EUNIQUE%20%3C%2FA%3E-%20returns%20a%20list%20of%20unique%20values%20from%20a%20list%20or%20range.%3C%2FDIV%3E%0A%3CDIV%3E%E2%80%A2%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2F22f63bd0-ccc8-492f-953d-c20e8e44b86c%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESORT%20%3C%2FA%3E-%20sorts%20an%20array%20of%20values.%20%3CBR%20%2F%3E%E2%80%A2%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2Fcd2d7a62-1b93-435c-b561-d6a35134f28f%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESORTBY%20%3C%2FA%3E-%20sorts%20an%20array%20based%20on%20a%20corresponding%20array.%20%3CBR%20%2F%3E%E2%80%A2%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2F57467a98-57e0-4817-9f14-2eb78519ca90%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESEQUENCE%20%3C%2FA%3E-%20generates%20a%20list%20of%20sequential%20numbers%2C%20such%20as%201%2C%202%2C%203%2C%204.%3C%2FDIV%3E%0A%3CDIV%3E%E2%80%A2%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2F7ca229ca-13ae-420b-928e-2ef52a3805ff%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESINGLE%20%3C%2FA%3E%E2%80%93%20accepts%20a%20range%20or%20array%20and%20returns%20a%20single%20value%20using%20implicit%20intersection.%3CBR%20%2F%3E%E2%80%A2%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2F21261e55-3bec-4885-86a6-8b0a47fd4d33%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ERANDARRAY%20%3C%2FA%3E-%20returns%20an%20array%20of%20random%20numbers%20between%200%20and%201.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EWe%20cannot%20wait%20to%20see%20how%20our%20users%20use%20these%20new%20building%20blocks%20in%20their%20spreadsheets.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3ELearn%20More%3C%2FSTRONG%3E%3CBR%20%2F%3EYou%20can%20learn%20more%20about%20dynamic%20arrays%20from%20these%20resources%3A%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CUL%3E%0A%3CLI%3Ethe%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2F3dd5899f-bca2-4b9d-a172-3eae9ac22efd%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Espilled%20range%20operator%3C%2FA%3E%20and%20how%20to%20use%20it%3C%2FLI%3E%0A%3CLI%3Ethe%20various%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2F205c6b06-03ba-4151-89a1-87a7eb36e531%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Edynamic%20array%20behaviors%3C%2FA%3E%26nbsp%3B%3C%2FLI%3E%0A%3CLI%3Ehow%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2Fca421f1b-fbb2-4c99-9924-df571bd4f1b4%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Edynamic%20arrays%20compare%20to%20legacy%20array%20formulas%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3Enew%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2Fffe0f555-b479-4a17-a6e2-ef9cc9ad4023%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%23SPILL%20error%3C%2FA%3E%20and%20scenarios%20that%20can%20trigger%20it%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2F7c21bf05-72f9-4261-822b-b26767efbd13%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eimplicit%20intersection%20and%20dynamic%20arrays%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Farticle%2F696e164e-306b-4282-ae9d-aa88f5502fa2%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Edynamic%20arrays%20in%20prior%20versions%3C%2FA%3E%26nbsp%3Bof%20Excel%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSTRONG%3EAvailability%20notes%3A%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CDIV%3EDynamic%20arrays%20functionality%20is%20available%20in%20Preview%20for%20users%20signed%20up%20for%20the%20Office%20365%20Insiders%20Program%20starting%20today.%20We%20will%20initially%20roll%20out%20to%20a%20subset%20of%20Insider%20users%20on%20Windows%20so%20that%20we%20can%20gather%20feedback%20and%20monitor%20feature%20quality.%20Over%20the%20next%20few%20months%2C%20we'll%20be%20increasing%20the%20number%20of%20Insider%20users%20with%20access%20to%20dynamic%20arrays%20and%20light%20up%20support%20for%20Excel%20on%20Mac%2C%20web%2C%20and%20mobile.%20You%E2%80%99ll%20know%20if%20you%20have%20dynamic%20arrays%20if%20you%20see%20any%20of%20the%20new%20functions%20in%26nbsp%3Byour%20formula%20autocomplete%20when%20you%20start%20typing%20a%20formula.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3ETo%20stay%20connected%20to%20Excel%20and%20its%20community%2C%20read%20Excel%20blog%20posts%2C%20and%20send%20us%20ideas%20and%20suggestions%20for%20the%20next%20version%20of%20Excel%20through%20UserVoice.%20You%20can%20also%20follow%20Excel%20on%20Facebook%20and%20Twitter.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EJoe%20McDaid%20(%40jjmcdaid)%3CBR%20%2F%3EProgram%20Manager%2C%20Excel%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-252944%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20dynamic%20arrays%2C%20you%20can%20write%20a%20single%20formula%20and%20get%20an%20array%20of%20values%20returned.%20One%20formula%2C%20many%20values.%20%3CSPAN%3EThis%20will%20allow%20you%20to%20build%20more%20capable%20spreadsheets%2C%20faster%2C%20with%20fewer%20formulas%20and%20less%20chance%20of%20error.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F49832iF3D5B913AAA142FE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Dynamic%20Arrays%20Blog%20GIF%20final.gif%22%20title%3D%22Dynamic%20Arrays%20Blog%20GIF%20final.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-252944%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386605%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386605%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20estimate%20on%20when%20this%20might%20be%20released%20to%20Production%20%2F%20Monthly%20channel%20in%20O365%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-375252%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-375252%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CFONT%3EI%20have%20similar%20concerns%20to%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232962%22%20target%3D%22_blank%22%3E%40Doug%20Jenkins%3C%2FA%3E%20in%20relation%20to%20functions%20that%20return%20variable%20sized%20array%20results%20and%20so%20risk%20raising%20%23SPILL%20errors.%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EIt%20would%20be%20nice%20if%20%3C%2FFONT%3Eit%20were%20possible%3CFONT%3E%20to%20access%20the%20underlying%20array%20result%20using%20the%20%23%20syntax%20without%20needing%20to%20accommodate%20all%20the%20spilled%20results%20-%20perhaps%20by%20introducing%20an%20'Array'%20cell%20data%20type%20similar%20to%20the%20new%20Geography%20%2F%20Stocks%20ones%3F%26nbsp%3B(Dynamic%20array%20variables%20of%20this%20kind%20can%20be%20created%20via%20the%20xlfSetName%20API%20function%20but%20something%20built-in%20would%20make%20things%20a%20lot%20easier.)%3C%2FFONT%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364325%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364325%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F298648%22%20target%3D%22_blank%22%3E%40Jabraham%3C%2FA%3E%26nbsp%3B-%20yes%2C%20but%20only%20for%20Office%20365%20Insiders%20Fast%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364161%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364161%22%20slang%3D%22en-US%22%3E%3CP%3Eare%20these%20available%20now%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358556%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358556%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20super%20excited%20to%20receive%20this%20update.%26nbsp%3B%20I've%20been%20waiting%20since%20the%20announcement.%26nbsp%3B%20Really%20looking%20forward%20to%20playing%20around%20with%20the%20formulas%20and%20using%20it%20in%20our%20day%20to%20day%20files.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353760%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353760%22%20slang%3D%22en-US%22%3E%3CP%3EOR%20for%20that%20kind%20calculation%20create%20%3DCOUNTDISTINCT()%20new%20function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353759%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353759%22%20slang%3D%22en-US%22%3E%3CP%3ELooks%20like%20every%20spilled%20formula%2C%20returning%20empty%20set%20(0%20rows)%20produces%20some%20error%20-%20this%20might%20not%20be%20bug%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3DLARGE(SEQUENCE(3)%3B4)%3C%2FFONT%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%2F%2F%20return%20%23NUM%3C%2FP%3E%0A%3CP%3E%3DSEQUENCE(0)%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%2F%2F%20return%20%23CALC%3CBR%20%2F%3E%3CBR%20%2F%3Emight%20be%20reasonable%20implement%20new%20error%20type%20%23EMPTY%20or%20something%20like%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353691%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%20looks%20like%20we%20introduced%20a%20bug%2C%20I'll%20get%20that%20off%20to%20engineering.%20Thanks%20for%20the%20report!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353662%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353662%22%20slang%3D%22en-US%22%3E%3CP%3EDid%20UNIQUE()%20change%20in%20the%20last%20week%20or%20so%3F%20I%20have%20a%20worksheet%20returning%20%23VALUE%20now%20that%20wasn't%20when%20I%20created%20the%20formula%20a%20few%20months%20ago.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTA(UNIQUE(ObsoleteTable%5Bitem_no%5D))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20has%20no%20records.%20It%20was%20returning%20zero%2C%20but%20now%20is%201%20because%20UNIQUE()%20is%20returning%20%23VALUE.%20I%20am%20not%20sure%20what%20it%20was%20returning%20when%20it%20was%20empty%20before%2C%20but%20the%20COUNTA()%20around%20the%20UNIQUE(no%20records)%20was%20returning%20zero.%20Pretty%20sure%20it%20was%20zero.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20an%20error%20table%20and%20should%20have%20zero%20records.%20I%20know%20how%20to%20write%20it%20a%20different%20way%2C%20so%20not%20asking%20for%20help.%20Just%20want%20to%20know%20if%20UNIQUE()%20changed%20in%20the%20last%20few%20weeks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-347651%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-347651%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%26nbsp%3B%20-%20any%20progress%20on%20the%20updates%3F%20Excited%20to%20test%20it.%20And%20did%20you%20get%20my%20file%20that%20SINGLE()%20was%20corrupting%20formulas%20so%20non-Dynamic%20Array%20aware%20versions%20got%20errors%20instead%20of%20CSE%20style%20functions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332622%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332622%22%20slang%3D%22en-US%22%3E%3CP%3EFurther%20to%20Ed%20Hansberry's%20last%20post%2C%20it%20seems%20to%20me%20that%20the%20basic%20problem%20is%20that%20the%20dynamic%20arrays%20either%20return%20the%20full%20array%2C%20or%20they%20return%20the%20%22spill%22%20message.%26nbsp%3B%20That%20may%20be%20OK%20for%20some%20applications%2C%20but%20for%20many%20it%20makes%20them%20unusable%2C%20with%20the%20only%20workaround%20being%20to%20enter%20the%20array%20as%20a%20fixed%20size%2C%20with%20Ctrl-Shift-Enter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20the%20following%20changes%20that%20would%20provide%20all%20the%20benefits%20of%20the%20dynamic%20arrays%2C%20without%20the%20drawbacks.%3C%2FP%3E%3CP%3E1.%20When%20an%20array%20is%20too%20large%20to%20display%20in%20full%2C%20replace%20the%20%22spill%22%20message%20with%20a%20display%26nbsp%3B%20of%20as%20much%26nbsp%3B%20of%20the%20array%20as%20will%20fit%2C%20with%20a%20distinctive%20border%20to%20indicate%20that%20it%20is%20a%20partial%20display.%3C%2FP%3E%3CP%3E2.%20Retain%20the%20Ctrl-shift-enter%20functionality%20to%20allow%20display%20of%20partial%20arrays%20without%20the%20border.%3C%2FP%3E%3CP%3E3.%20Improve%20the%20Ctrl-shift-enter%20so%20that%20arrays%20can%20be%20re-sized%20to%20a%20selected%20range%2C%20or%20returned%20to%20dynamic%20mode%20by%20entering%20with%20just%20enter.%3C%2FP%3E%3CP%3E4.%20Drop%20the%20%22single%22%20function%20entirely%2C%20or%20at%20least%20don't%20insert%20it%20automatically.%3C%2FP%3E%3CP%3E5.%20Any%20arrays%20that%20return%20a%20single%20value%2C%20such%20as%20the%20Index%20examples%2C%20would%20display%20as%20normal%2C%20without%20the%20%22single%22%20function%20added.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332447%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332447%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E.%20I%20think%20the%20confusion%2C%20at%20least%20on%20my%20part%20is%20why%20does%20this%20happen%3F%3C%2FP%3E%3CUL%3E%3CLI%3ECreate%20formula%26nbsp%3B%3DINDEX(tblCalendar%5BYears%5D%2CSettings!C1)%20in%20Insider%20build.%20Stays%20like%20that.%20It%20works%20without%20SINGLE()%3C%2FLI%3E%3CLI%3ECreate%20formula%26nbsp%3B%3DINDEX(tblCalendar%5BYears%5D%2CSettings!C1)%20in%20other%20build%2C%20it%20works%20in%20non-insider%20build.%20When%20opened%20in%20Insider%2C%20it%20is%20wrapped%20in%20SINGLE().%20Saving%2C%20closing%20and%20opening%20back%20in%20non-insider%20build%20SINGLE()%20is%20gone.%3C%2FLI%3E%3CLI%3EIn%20some%20cases%2C%20the%20non-insider%20build%20is%20converted%20to%20a%20CSE%20funtion%26nbsp%3B%3D%7BINDEX(tblCalendar%5BYears%5D%2CSettings!C1)%7D%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20know%20this%20is%20a%20transition%20issue%2C%20but%20it%20will%20actually%20last%20years%20as%20there%20are%20people%20with%20Excel%202013%2F2016%2F2019%20that%20won't%20get%20this%20until%20they%20either%20upgrade%20to%20O365%2C%20or%20get%20Excel%202022%20or%20whatever%20the%20next%20perpetual%20license%20version%20is.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20clearer%20to%20me%20if%20SINGLE()%20was%20required%20to%20get%20the%20function%20to%20work%20properly%20in%20the%20new%20Dynamic%20Array%20calculation%20engine%2C%20but%20in%20every%20case%20I've%20seen%20with%20my%20workbooks%2C%20the%20addition%20of%20SINGLE%20does%20nothing%20except%20perhaps%20render%20the%20function%20as%20an%20error%20in%20non-insider%20builds.%20I%20have%20more%20than%20one%20workbook%20in%20our%20org%20that%20I%20cannot%20open%20in%20the%20Insider%20build%20as%20it%20trashes%20a%20pretty%20long%20formula%20in%20a%20table%20(so%20it%20changes%20thousands%20of%20records)%20for%20use%20in%20any%20version%20that%20doesn't%20support%20Dynamic%20Arrays%20simply%20by%20wrapping%20it%20in%20SINGLE().%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332075%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332075%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246352%22%20target%3D%22_blank%22%3E%40GabrielMichaud%3C%2FA%3E%20This%20is%20expected%20as%20UDF's%20have%20the%20ability%20to%20return%20ranges%2Farrays%20and%20trigger%20implicit%20intersection.%20That%20said%2C%20we%20will%20be%20announcing%20some%20improvements%20to%20the%20experience%20in%20the%20next%20few%20days%20-%20so%20watch%20this%20space.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332068%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332068%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20the%20same%20behaviour%20expected%20for%20custom%20functions%20with%20an%20undefined%2Fvariable%20return%20type%3F%20We%20have%20custom%20XLL%20functions%20that%20rely%20on%20a%20RTD%20server%20(built%20with%20ExcelDNA)%20and%20in%20an%20earlier%20build%20we%20were%20seeing%20functions%20getting%20wrapped%20in%20%7B%7D%20(as%20if%20they%20were%20array%20functions)%2C%20even%20though%20we%20only%20return%20single%20values.%20I%20posted%20something%20about%20that%20problem%20a%20month%20or%20so%20ago%20and%20the%20explanation%20I%20got%20was%3C%2FP%3E%3CP%3Ethat%20my%20functions%20are%20using%20implicit%20intersection.%20Not%20clear%20if%20we%20have%20to%20change%20something%20or%3C%2FP%3E%3CP%3Eif%20it%E2%80%99s%20a%20bug%20in%20the%20dynamic%20array%20features%20since%20it%E2%80%99s%20so%20random.%20I%20had%20to%20revert%20back%20to%20the%20official%3C%2FP%3E%3CP%3Ebuild%20since%20it%20was%20breaking%20our%20spreadsheets...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332067%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332067%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20the%20same%20behaviour%20expected%20for%20custom%20functions%20with%20an%20undefined%2Fvariable%20return%20type%3F%20We%20have%20custom%20XLL%20functions%20that%20rely%20on%20a%20RTD%20server%20(built%20with%20ExcelDNA)%20and%20in%20an%20earlier%20build%20we%20were%20seeing%20functions%20getting%20wrapped%20in%20%7B%7D%20(as%20if%20they%20were%20array%20functions)%2C%20even%20though%20we%20only%20return%20single%20values.%20I%20posted%20something%20about%20that%20problem%20a%20month%20or%20so%20ago%20and%20I%20had%20to%20revert%20back%20to%20the%20official%3C%2FP%3E%3CP%3Ebuild%20since%20it%20was%20breaking%20our%20spreadsheets...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332066%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332066%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F236847%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%26nbsp%3BThat's%20expected.%20INDEX%20returns%20an%20array%20or%20range%20when%20the%202nd%2F3rd%20argument%20is%200.%20If%20Setting!C1%20ever%20resolves%20to%200%2C%20that%20formula%20would%20have%20implicitly%20intersected%20in%20old%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332052%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332052%22%20slang%3D%22en-US%22%3E%3CP%3EHoping%20the%20changes%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%26nbsp%3Breally%20curtail%20the%20SINGLE()%20issue.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20found%20out%20a%20workbook%20with%20the%20simple%20formula%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DINDEX(tblCalendar%5BYears%5D%2CSettings!C1)%3C%2FPRE%3E%3CP%3Egot%20wrapped%20in%20Single%20when%20I%20opened%20it%20in%20my%20insider%20build.%20tblCalendar%20is%20a%20simple%202%20column%20Excel%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-330601%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-330601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F277178%22%20target%3D%22_blank%22%3E%40MOBMD%3C%2FA%3E%20There%20was%20a%20change%20that%20impacted%20macros%20injecting%20formulas%20into%20cells%20-%20the%20latest%20build%20is%20essentially%20interpreting%20them%20as%20old%20style%20formulas%20which%20is%20why%20you%20are%20seeing%20the%20SINGLE's%20appear.%20I'll%20have%20some%20info%20and%20further%20news%20on%20DA%20related%20enhancements%20in%20the%20next%20few%20days.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-330506%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-330506%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%26nbsp%3B%2C%20I%20don't%20know%20whether%20you%20saw%20this%20post%20from%20me%2C%20but%20I%20think%20one%20of%20Excel's%20greatest%20MVPs%20might%20have%20made%20you%20aware%20of%20my%20recent%20dilemma.%26nbsp%3B%20Thanks%20for%20taking%20a%20look%20at%20it.%26nbsp%3B%20MOBMD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%22Since%20this%20latest%202%2F1%2F2019%20update%2C%20there%20is%20a%20new%20glitch%20with%20the%20Office%20Insider%20Dynamic%20Array%20functions.%26nbsp%3B%20It%20seems%20that%20many%2C%20many%20more%20VBA-written%20formulas%20are%20now%20erroneously%20auto-inserting%20the%20SINGLE%20function%2C%20apparently%20due%20to%20the%20Excel%20Calc%20engine%20assuming%20a%20need%20for%20Implicit%20Intersection.%26nbsp%3B%20This%20incorrect%20insertion%20of%20the%20SINGLE%20function%20is%20much%20more%20pervasive%20than%20it%20was%20as%20recently%20as%20the%20morning%20of%202%2F1%2F2019%20(prior%20to%20the%20update)%20--%20in%20the%20same%20workbooks%2C%20despite%20no%20interval%20change%20in%20the%20macros%20within%20those%20workbooks.%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EIs%20there%20a%20way%20to%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bdisable%20auto-insertion%20of%20the%20SINGLE%20function%2C%20at%20least%20until%20this%20glitch%20is%20corrected%20with%20the%20next%20build%3F%22%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-329482%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-329482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F2888%22%20target%3D%22_blank%22%3E%40Mark%20E%20Weisman%3C%2FA%3E%26nbsp%3B%2C%20so%20far%20that's%20for%20Office%20Insiders%20(aka%20Insiders%20Fast)%2C%20only%20Office%20365%20is%20not%20enough%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-329467%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-329467%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20installed%20version%201901%20of%20Excel%20for%20Office%20365.%26nbsp%3B%20Why%20can't%20I%20use%20the%20new%20dynamic%20array%20formulas%20(%20e.g.%2C%20UNIQUE%2C%20FILTER)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313510%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313510%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20current%20version%20you%20can%20display%20part%20of%20the%20full%20array%2C%20including%20a%20single%20cell%2C%20by%20entering%20with%26nbsp%3B%20Ctrl-Shift-Enter%3B%20so%20in%20effect%20that%20works%20as%20in%20earlier%20Excel%20versions.%3C%2FP%3E%3CP%3EI%20certainly%20hope%20that%20feature%20is%20retained%2C%20it%20will%20be%20very%20bad%20if%20it%20isn't.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20also%20be%20good%20if%20we%20could%20re-size%20the%20displayed%20array%20by%20selecting%20the%20required%20range%20and%20pressing%20Ctrl-shift-enter%20again%2C%20or%20revert%20to%20the%20new%20behaviour%20by%20pressing%20just%20enter%2C%20but%20I%20haven't%20seen%20any%20response%20to%20that%20suggestion.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313438%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313438%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F263656%22%20target%3D%22_blank%22%3E%40ErikW%3C%2FA%3E%26nbsp%3B-%20I%20seriously%20doubt%20it.%20This%20is%20a%20change%20to%20the%20calculation%20engine%20itself%2C%20not%20an%20addon%20like%20Power%20Query.%20My%20understanding%20is%20MS%20is%20working%20hard%20to%20ensure%20any%20widespread%20addins%20(XLL%20or%20otherwise)%20are%20updated%20so%20they%20aren't%20using%20implicit%20intersection%2C%20or%20if%20they%20are%20are%20using%20it%20purposefully%20and%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-313422%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-313422%22%20slang%3D%22en-US%22%3E%3CP%3EWill%20it%20be%20possible%20for%20the%20user%20to%20turn%20off%20this%20feature%20if%20it%20interfering%20with%20something%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20it%20be%20possible%20to%20mark%20some%20functions%20that%20we%20write%20(say%20written%20in%20VBA%20or%20an%20XLL)%20as%20not%20using%20this%20dynamic%20feature%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-302124%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-302124%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20anyone%20know%20of%20a%20way%20to%20reliably%20trap%20the%20use%20of%20these%20new%20functions%20in%20an%20older%20version%20of%20Excel%3F%20What%20is%20happening%20is%20the%20function%20becomes%20essentially%20static.%20This%20is%20what%20happens%3A%3C%2FP%3E%3CPRE%3E%3DUNIQUE(ObsoleteTable%5Bitem_no%5D)%3C%2FPRE%3E%3CP%3Eworks%20in%20insider%20build.%20In%20older%20builds%2C%20this%20is%20what%20comes%20up%3A%3C%2FP%3E%3CPRE%3E%7B%3D_xlfn.UNIQUE(ObsoleteTable%5Bitem_no%5D)%7D%3C%2FPRE%3E%3CP%3Ewhich%20returns%20a%20%23NAME%20error%20or%20the%20static%20value%20of%20the%20array%20from%20the%20last%20calculation%20in%20the%20version%20of%20Excel%20that%20supports%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20tried%20to%20trap%20%23NAME%20with%20this%3A%3C%2FP%3E%3CPRE%3E%3DIF(ISERROR(UNIQUE(ObsoleteTable%5Bitem_no%5D))%2C%22test%22%2CUNIQUE(ObsoleteTable%5Bitem_no%5D))%3C%2FPRE%3E%3CP%3EIn%20old%20versions%2C%20%22Test%22%20is%20properly%20returned.%20But%20in%20Insider%20Builds%2C%20%22Test%22%20is%20%3CEM%3Ealso%20%3C%2FEM%3Ereturned%20if%20the%20last%20version%20to%20open%20the%20file%20is%20an%20old%20version%2C%20unless%20you%20press%20F2%20on%20the%20formula%20to%20edit%2C%20then%20enter.%20Opening%20the%20file%20doesn't%20recalculate%20it%2C%20pressing%20F9%20doesn't%2C%20and%20neither%20does%20CTRL-ALT-F9.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20start%20doing%20is%20where%20I%20am%20using%20these%20in%20some%20files%20is%20displaying%20a%20message%20that%20%22This%20version%20of%20Excel%20doesn't%20support%20all%20data%20in%20this%20workbook%22%20in%20old%20versions%2C%20and%20works%20in%20new%20versions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20given%20that%20the%20ISERROR()%20won't%20recalc%20in%20the%20Insider%20builds%2C%20this%20plan%20won't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%20This%20will%20be%20an%20issue%20for%20years%20to%20come%20if%20the%20behavior%20is%20the%20same.%20Internally%20we'll%20ultimately%20be%20fine%20as%20dynamic%20arrays%20roll%20out%20to%20Monthly%20builds%2C%20but%20as%20we%20share%20files%20with%20outsiders%2C%20they%20may%20get%20the%20wrong%20results%20if%20looking%20at%20a%20static%20array%20if%20they%20are%20on%20an%20older%20version%20of%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-300524%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-300524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20I%20wish%20my%20mind%20worked%20in%20the%20way%20needed%20for%20you%20to%20have%20figured%20that%20out.%26nbsp%3B%20Incredible.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much.%26nbsp%3B%20That%20solution%20is%20also%20great%20for%20showing%20me%20some%20new%20options.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-300333%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-300333%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20update%20on%20availability%3F%20currently%20on%20monthly%20targeted%20release%20and%20waiting%20patiently...%E2%80%A6%20ever%20so%20patiently!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299848%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246352%22%20target%3D%22_blank%22%3E%40GabrielMichaud%3C%2FA%3E%2C%20sorry%2C%20that%20was%20reply%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F252677%22%20target%3D%22_blank%22%3E%40ChrisTank%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299834%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299834%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don%E2%80%99t%20have%20any%20tables%2C%20array%20or%20reference%20that%20is%20more%20than%201%20cell%20in%20my%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DACCOUNTNAME(%22Demo%22%2C%20A1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYet%20Excel%20thinks%20this%20is%20an%20array%20function%20and%20suddenly%2Frandomly%20changes%20it%20into%20a%20CSE%20array%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299818%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299818%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246352%22%20target%3D%22_blank%22%3E%40GabrielMichaud%3C%2FA%3E%2C%20as%20variant%20that%20could%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F62583iAE73AF66ACCE9D12%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20in%20J1%3AM1%20is%20list%20of%20columns%20you'd%20like%20to%20extract%20in%20order%20in%20which%20you'd%20like%20to%20have%20them.%20The%20name%20of%20the%20source%20table%20here%20is%20%22Test%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299774%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299774%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.reddit.com%2Fr%2Fexcel%2Fcomments%2F9iva5a%2Ffundamental_changes_coming_to_excel_formulas_will%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ERead%20this%20thread%3C%2FA%3E%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246352%22%20target%3D%22_blank%22%3E%40GabrielMichaud%3C%2FA%3E.%20There%20are%20a%20number%20of%20addins%20and%20formulas%20that%20are%20using%20implicit%20intersection%20and%20don't%20know%20it.%20Follow%20the%20links%2C%20including%20to%20Bill's%20article%20and%20video%20on%20implicit%20intersection.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299754%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299754%22%20slang%3D%22en-US%22%3E%40EdHansberry%20can%20you%20clarify%20what%20you%20mean%20by%20implicit%20intersection%3F%20All%20our%20functions%20expect%20a%20single%20scalar%20value%3B%20we%20don't%20use%20range%20or%20references%20anywhere%20in%20our%20XLL.%20The%20one%20I%20put%20as%20a%20screenshot%20in%20my%20original%20message%20has%20two%20string%20arguments%3B%20and%20I%20only%20pass%20a%20single%20cell%20reference%20(A1).%20I%20was%20suspecting%20my%20problem%20had%20to%20do%20with%20the%20return%20type%20of%20the%20function%20rather%20than%20the%20arguments...%20The%20fact%20that%20the%20issue%20occurs%20randomly%20is%20a%20bit%20challenging%20for%20me%20to%20debug%2Ftroubleshoot%20as%20well%2C%20it's%20not%20consistent%20at%20all.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299744%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299744%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246352%22%20target%3D%22_blank%22%3E%40GabrielMichaud%3C%2FA%3E-%20hoping%20you%20can%20get%20access%20to%20the%20feedback%20channel.%20Not%20sure%20what%20it%20is%20for%20direct%20access.%20The%20issue%20though%20is%20your%20XLL's%20are%20using%20implicit%20intersection%20and%20the%20new%20dynamic%20array%20calculations%20detect%20that%20and%20will%20either%20wrap%20in%20%7B%7D%20or%20wrap%20in%20%3DSINGLE().%20The%20fix%20is%20to%20remove%20implicit%20intersection%20from%20your%20XLL%20functions.%20This%20is%20one%20reason%20MS%20is%20being%20really%20slow%20to%20roll%20this%20change%20out.%20They%20want%20to%20make%20sure%20that%20any%20addin%20inadvertently%20using%20implicit%20intersection%20is%20updated%20and%20re-released.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299736%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20That%20works%20to%20an%20extent.%26nbsp%3B%20I%20wish%20I%20thought%20to%20share%20a%20screenshot%20previously.%26nbsp%3B%20The%20below%20is%20what%20I%20am%20trying%20to%20accomplish.%26nbsp%3B%20The%20filter%20formula%20would%20go%20in%20A22.%26nbsp%3B%20Ultimatley%20I%20would%20be%20placing%20it%20in%20a%20different%20sheet%20within%20the%20workbook%20and%20it%20would%20use%20the%20data%20table%20for%20the%20data%20to%20filter.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F62567iAFABD74D59840CDC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22FILTER%20WITH%20INCONSISTENT%20COLUMN%20SKIP%20EXAMPLE.png%22%20title%3D%22FILTER%20WITH%20INCONSISTENT%20COLUMN%20SKIP%20EXAMPLE.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20even%20possible%20with%20the%20new%20array%20formulas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299695%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299695%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhat's%20the%20right%20channel%20to%20provide%20feedback%20on%20the%20new%20dynamic%20array%20features%20in%20Excel%20in%20insider%20builds%3F%20We%20have%20an%20issue%20where%20our%20custom%20functions%20(XLL%20functions%20created%20using%20ExcelDna)%20are%20occasionally%20detected%20by%20Excel%20as%20being%20array%20functions%20and%20get%20%22converted%22%20to%20CSE-array%20formula.%20It%20happens%20randomly%20and%20I%20have%20not%20found%20any%20way%20to%20fix%20files%20affected%20by%20this%20issue%20beside%20starting%20from%20an%20earlier%20backup%20of%20the%20file%2C%20or%20using%20a%20version%20of%20Excel%20that%20doesn't%20have%20the%20new%20dynamic%20array%20feature%20enabled.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F62556iC59D6F5E0042A73F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202018-12-11%20at%2009.20.44.png%22%20title%3D%22Screen%20Shot%202018-12-11%20at%2009.20.44.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20used%20the%20feedback%20button%20in%20Excel%20and%20also%20posted%20it%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FDynamic-aware-Excel-incorrectly-flagging-our-custom-XLL%2Fm-p%2F291362%23M22091%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FDynamic-aware-Excel-incorrectly-flagging-our-custom-XLL%2Fm-p%2F291362%23M22091%3C%2FA%3E%26nbsp%3Bbut%20I'm%20worried%20this%20will%20eventually%20hit%20customers%20using%20normal%20release%20builds%20of%20Office.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20creates%20a%20host%20of%26nbsp%3Bproblems%20for%20us%20-%20our%20custom%20functions%20are%20RTD%20functions%2C%20and%20there's%20a%20bug%20or%20limitation%20in%20Excel%20where%26nbsp%3Bthe%20RTD%20server%26nbsp%3Bdoes%20not%20disconnect%20when%20used%20in%20an%20array%20formula%20(%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgist.github.com%2Fgovert%2F03df749f38b9582b1217).%26nbsp%3BTo%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgist.github.com%2Fgovert%2F03df749f38b9582b1217%3C%2FA%3E%26nbsp%3B).%20To%20be%20clear%2C%20our%20functions%20are%20not%20array%20functions%2C%20however%20since%20Excel%20*thinks*%20they%20are%20array%20function%20the%20RTD%20server%20does%20not%20properly%20disconnect%20from%20the%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20the%20root%20of%20the%20issue%20is%20that%20Excel%20looks%20at%20the%20return%20type%20of%20the%20function%20to%20decide%20if%20it's%20a%20function%20that%20can%20potentially%20return%20an%20array%2C%20and%20since%20the%20return%20type%20is%20just%20%22object%22%20it%20suddenly%20decides%20to%20make%20the%20cell%20formula%20an%20array%20formula.%20As%20per%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fdynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fdynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2%3C%2FA%3E%2C%20%22%3CSPAN%3EWhenever%20you%20write%20a%20formula%20in%20dynamic%20aware%20Excel%2C%20it%20determines%20if%20that%20formula%20has%20the%20potential%20to%20return%20an%20array.%20If%20it%20could%20return%20an%20array%2C%20we%20will%20save%20it%20as%20a%20dynamic%20array.%22.%26nbsp%3BWhat%20I%20don't%20understand%20is%20why%20it%20randomly%20happens%2C%20and%20whether%20or%20not%20we%20can%20avoid%20this...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299172%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299172%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F252677%22%20target%3D%22_blank%22%3E%40ChrisTank%3C%2FA%3E%2C%20it%20could%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20558px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F62435i8715ACC85A551EF9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299079%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299079%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20skip%20columns%20when%20using%20FILTER%3F%26nbsp%3B%20I%20have%20data%20in%20A%26gt%3BF%20but%20only%20want%20to%20return%20the%20filtered%20data%20from%20A%2CB%2CD%2CF.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-298993%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-298993%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20I%20will%20have%20a%20look%20at%20the%20post.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-298990%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-298990%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20have%20option%20to%20upgrade%20into%20insiders%20-%20available%20immediately%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20instruction%20(unfortunately%20in%20Estonian)%20how%20to%20do%20-%20see%20sarviktaat.wordpress.com%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-298923%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-298923%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThis%20new%20set%20of%20functions%20seams%20just%20wonderful.%3C%2FP%3E%3CP%3EIs%20there%20any%26nbsp%3B%3CSPAN%3EETA%20for%20when%20they%20will%20be%20available%20to%20%3C%2FSPAN%3Eregular%20Office%20365%20users%20(I%20have%20the%20version%201811%20on%20a%20Monthly%20update%20channel).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293999%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293999%22%20slang%3D%22en-US%22%3E%3CP%3E2%20missing%20formulas%20or%20I%20can't%20find%3CBR%20%2F%3E%3CBR%20%2F%3E%3DFirst(%20spill%20%2C%20n%20)%3CBR%20%2F%3E%3DLast%20(%20spill%2C%20n%20)%3CBR%20%2F%3E%3DReverse%20(%20spill%20)%3CBR%20%2F%3E%3CBR%20%2F%3ENB!%20Goot%20to%20have%20%3DSKIP(spill%2C%20n)%20too%20or%20alternative%20allow%20OFFSET%20like%20thing%20against%20expression%3CBR%20%2F%3E%3CBR%20%2F%3EI%20know%20how%20to%20do%20this%20using%20%3Doffset%20but%20when%20I%20need%20to%20include%20this%20into%20one%20formula%3CBR%20%2F%3E%3CBR%20%2F%3Eactual%20request%20is%20how%20to%20summary%20last%203%20filled%20cells%20in%20range%20(using%20one%20formula)%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293693%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F56123%22%20target%3D%22_blank%22%3E%40Henn%20Sarv%3C%2FA%3E-%20that%20function%20worked%20as%20it%20was.%20Has%20to%20be%20K9%23%20to%20get%20the%20whole%20array.%20I%20changed%20the%20formula%20to%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DROUND(SUM(FILTER(tblRoyaltyAccrual%5B%26lt%3BAccrual%26gt%3B%20%2F%20Payment%5D%2C(tblRoyaltyAccrual%5BVendor%5D%3DF9)*(tblRoyaltyAccrual%5BDate%5D%26lt%3B%3DdtCurrentMonthEnd)))%2C2)%3C%2FPRE%3E%3CP%3EI%20just%20pull%20the%20%5B%3CACCRUAL%3E%2FPayment%5D%20column%20but%20filtered%20by%20%5BVendor%5D%20and%20%5BDate%5D%2C%20so%20I%20only%20get%20a%20single%20column%20in%20my%20results%20to%20SUM.%20I%20was%20returning%20the%20entire%204%20column%20table%20in%20my%20FILTER()%20function%20before%20I%20figured%20it%20out.%3C%2FACCRUAL%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293665%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293665%22%20slang%3D%22en-US%22%3E%3CP%3EEd%2C%20try%20to%20replace%201st%20K9%23%20to%20K9%20or%20%24k%249%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHenn%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293640%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293640%22%20slang%3D%22en-US%22%3E%3CP%3EAnyone%20have%20any%20idea%20how%20to%20sum%20a%20column%20that%20is%20the%20result%20of%20a%20FILTER()%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20FILTER%20function%20that%20returns%20a%20variable%20number%20of%20rows%2C%20and%204%20columns.%20The%203rd%20column%20has%20values%20and%20I%20just%20want%20the%20SUM()%20of%20that%20column.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I%20put%20the%20FILTER()%20in%20K9%2C%20then%20the%20following%20works%3A%3C%2FP%3E%3CPRE%3E%3DSUM(OFFSET(K9%23%2C0%2C2%2CROWS(K9%23)%2C1))%3C%2FPRE%3E%3CP%3EI%20would%20like%20that%20in%20one%20formula%20though%20if%20possible%2C%20but%20OFFSET()%20requires%20a%20reference%20in%20the%20first%20parameter%2C%20not%20a%20formula%20that%20returns%20a%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20that%20DAX%20is%20messing%20me%20up%20here.%20This%20is%20a%20snap%20in%20DAX%20and%20I%20am%20thinking%20of%20tables%2C%20records%2C%20and%20fields.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%20Figured%20it%20out.%20I%20can%20filter%20one%20column%20based%20on%20multiple%20other%20columns.%20I%20had%20it%20in%20my%20head%20I%20needed%20to%20filter%20the%20entire%20table.%20(DAX%20again....)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291909%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291909%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20interesting%2C%20but%20better%20to%20convert%20some%20extra%20and%20ensure%20compatibility%20(if%20not%20use%20new%20dynamic%20array%20functions).%20The%20point%20is%20it%20works%20in%20both%20versions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20you%20may%20simplify%20your%20formula%20a%20bit%3C%2FP%3E%0A%3CPRE%3E%3DSUM(Table1%5B%40%5BColumn1%5D%3A%5BColumn5%5D%5D)%2BN(OFFSET(%5B%40Balance%5D%2C-1%2C0))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291874%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291874%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20understand%20why%20this%20is%20happening.%20I%20have%20created%20a%20simple%20table%20in%20Excel%2C%20and%20I%20want%20the%20last%20column%20to%20be%20a%20running%20total%2C%20so%20everything%20the%20current%20row%20plus%20the%20results%20of%20the%20previous%20row%20as%20long%20as%20the%20previous%20row%20isn't%20the%20title%20row%2C%20which%20would%20cause%20an%20error.%20This%20formula%20also%20work%20100%25%20of%20the%20time%20when%20you%20insert%20and%20delete%20rows%20in%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20is%20in%20the%20Balance%20column%20of%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DSUM(Table1%5B%40%5BColumn1%5D%3A%5BColumn5%5D%5D)%2BIFERROR(OFFSET(%5B%40Balance%5D%2C-1%2C0)*1%2C0)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20create%20that%20formula%20in%20the%20Insider%20build%2C%20it%20works%20fine.%20When%20I%20open%20it%20on%20any%20other%20build%20(Monthly%20Targeted%2C%20and%20the%20iOS%20Beta%20of%20Excel%20-%202.19.18110500)%20it%20makes%20it%20a%20CSE%20array%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%7B%3DSUM(Table1%5B%40%5BColumn1%5D%3A%5BColumn5%5D%5D)%2BIFERROR(OFFSET(%5B%40Balance%5D%2C-1%2C0)*1%2C0)%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20can%20edit%20it%20on%20those%20platforms%20and%20make%20it%20a%20normal%20formula%2C%20and%20it%20works%20just%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20with%20Monthly%20Targeted%20(1811)%20I%20changed%20it%20to%20a%20normal%20non-CSE%20formula.%20If%20i%20change%20data%20with%20Insider%2C%20the%20formulas%20do%20not%20get%20converted%20back%20to%20CSE%20formulas%2C%20but%20if%20I%20edit%20the%20formula%2C%20it%20does.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeems%20it%20is%20too%20aggressively%20converting%20the%20formulas%20when%20it%20isn't%20necessary.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288223%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288223%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%20Actually%20SUMPRODUCT%20isn't%20needed%20anymore.%20I%20used%20the%20following%20based%20on%20your%20idea%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DSUM((LEFT(Items%5BItem%20number%5D%2C6)%3DLEFT(Items%5B%40%5BItem%20number%5D%5D%2C6))*1)%26gt%3B1%3C%2FPRE%3E%3CP%3EI%20think%20this%20not%20really%20intuitive%20though.%20I%20guess%20I%20have%20the%20mindset%20of%20how%20DAX%20functions%20work%20and%20when%20you%20return%20a%20%22range%22%20of%20data%20(table%20or%20column)%20you%20are%20returning%20the%20contents%20of%20that%20to%20the%20function%2C%20so%20it%20seems%20logical%20that%20COUNTIF(S)%20should%20accept%20the%20contents%20of%20the%20range%20and%20not%20just%20the%20range%20reference.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288161%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288161%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Joe%2C%3C%2FP%3E%3CP%3EI've%20just%20got%20the%20Dynamic%20Arrays%20last%20week.%26nbsp%3B%20And%20it%20is%20simply%20awesome!%26nbsp%3B%20Super%20COOL!%20I%20do%20hope%20the%20corporate%20world%20will%20move%20to%20Office%20365%20quicker%20so%20that%20we%20can%20use%20the%20full%20potential%20of%20Excel%20anywhere%2C%20not%20just%20at%20home.%20%3CLI-EMOJI%20id%3D%22lia_face-with-tongue%22%20title%3D%22%3Aface_with_tongue%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%26nbsp%3Bencountered%20a%20strange%20behaviour%20in%20Data%20Validation%26nbsp%3Busing%20Dynamic%20Array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20did%20was%20to%20create%20a%20%22Shrinking%20Dynamic%20Dropdown%22%20-%20Once%20an%20item%20is%20selected%2C%20it%20will%20be%20gone%20for%20the%20next%20input.%26nbsp%3B%20With%20Dynamic%20Arrays%2C%20it%26nbsp%3Bcan%20be%26nbsp%3B%20achieved%20easily%20in%20just%20a%20couple%20of%20minutes.%26nbsp%3B%20However%20I%20encountered%20an%20unexpected%20behaviour%20-%20if%20I%20input%20a%20value%20through%20manual%20typing%2C%20it%20would%20trigger%20a%20recalculation%20before%20validation...%20which%20makes%20my%20trick%20fail...%20The%20trick%20works%20well%20if%20I%20select%20an%20value%20from%20the%20dropdown...%3C%2FP%3E%3CP%3EIn%20short%2C%20my%20observation%20is%3A%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSTRONG%3EUsing%20dropdown%20menu%3A%20Validation%20first%2C%20recalculation%20afterward%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EUsing%20manual%20input%3A%20Recalculation%20first%2C%20validation%20afterward%3C%2FSTRONG%3E%20(and%20hence%20the%20validation%20failed%20the%20input)%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI've%20described%20the%20situation%20in%20more%20details%20in%20my%20post%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwmfexcel.com%2F2018%2F11%2F14%2Fdynamic-shrinking-dropdown-with-dynamic-arrays-in-excel-365%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwmfexcel.com%2F2018%2F11%2F14%2Fdynamic-shrinking-dropdown-with-dynamic-arrays-in-excel-365%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFYI%2C%20I%20didn't%20encounter%26nbsp%3Bthis%20when%20using%20old%20tricks%20of%20array%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20why%20it%20is...%20and%20I%20do%20not%20know%20where%20and%20who%20I%20should%20report%20this%20to...%20So%20I%20leave%20you%20a%20comment%20here.%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3EMF%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288011%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288011%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F236847%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%2C%20in%20COUNTIFS%20criteria%20range%20can't%20be%20a%20formula%2C%20only%20range.%20Thus%20error.%20If%20you%20use%20%24E%242%23%20that%20is%20shortcut%20for%20%24E%242%3A%24E%247%2C%20not%20for%26nbsp%3B%3CSPAN%3ELEFT(Table1%5BNumber%5D%2C6).%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIn%20general%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((LEFT(Table1%5BNumber%5D%2C6)%3DLEFT(Table1%5B%40Number%5D%2C6))*1)%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%3Eworks%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287977%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287977%22%20slang%3D%22en-US%22%3E%3CP%3EFew%20additional%20comments%3A%3CBR%20%2F%3E%3CBR%20%2F%3Efor%20my%20everyday%20work%20I%20find%20quick%20tool%20like%20formula%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3Dunichar(transpose(sequence(256%3B256%3B0)))%20-%20try%20on%20empty%20sheet%20-%20extreamly%20useful%20tool%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eplus%20one%20negative%20comment%3A%3CBR%20%2F%3E%3CBR%20%2F%3EData%20validation%20list%20formula%20%3DTable%5BColumn%5D%20still%20not%20work%3CBR%20%2F%3EData%20validation%20list%20formula%20%3DIndirect(%22Table%5BColumn%5D%22)%20works%20fine%20(workaround%20for%20previous)%3CBR%20%2F%3EData%20validation%20list%20formula%20%3DSort(Indirect(%22Table%5BColumn%5D%22)%20don't%20work%20again%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287465%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287465%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I'm%20having%20trouble%20understanding%20why%20the%20below%20doesn't%20work.%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20table%20(just%20called%20Table1)%20with%20one%20column%20called%20%22Number%22.%20In%20it%20are%206%20item%20numbers.%3C%2FP%3E%3CP%3ENumber%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3Ea123456%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ea123444%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ea000013%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ea004025%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ea004023%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ea123457%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20following%20formula%20in%20E2%3A%26nbsp%3B%20%3DLEFT(Table1%5BNumber%5D%2C6)%3CBR%20%2F%3EThen%20I%20have%20another%20formula%20in%20F2%3A%20%3DCOUNTIFS(%24E%242%23%2CLEFT(Table1%5B%40Number%5D%2C6))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20formula%20I%20copy%20down%20from%20F2%3AF7.%20Works%20perfectly.%20It%20tells%20me%20if%20the%20first%206%20digits%20of%20the%207%20digit%20item%20numbers%20are%20repeated.%20The%20numbers%20a123456%20and%20a123457%20would%20show%20repeats%20because%20the%20first%206%20digits%2C%20a12345%2C%20are%20repeated%202x%20in%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20if%20I%20replace%20the%20%24E%242%23%20reference%20with%20the%20LEFT(Table1%5BNumber%5D%2C6)%20function%2C%20Excel%20tells%20me%20there%20is%20an%20error%20with%20the%20formula%20and%20won't%20let%20me%20enter%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20with%20Dynamic%20Arrays%20to%20get%20this%20to%20be%20a%20single%20formula%20without%20this%20%22helper%20formula%22%20in%20E2%23%3F%20I've%20tried%20COUNTIF()%20as%20well%2C%20and%20no%20matter%20what%20I%20do%2C%20I%20cannot%20get%20it%20to%20accept%20an%20array%20from%20a%20function%2C%20but%20it%20does%20from%20a%20%23SPILL%20reference.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281381%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281381%22%20slang%3D%22en-US%22%3E%3CP%3EActually%20I%20find%20solution%20how%20to%20add%20sorted%20column%20into%20table%20using%20funktsioon%20%3DSORT()%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20Table%20(Table1)%20and%20column%20Name%20in%20this%3C%2FP%3E%0A%3CP%3EI%20create%20column%20SortedName%20with%20following%20formula%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFONT%3E%3DINDEX(SORT(%5BNimi%5D)%3BROW()-ROW(Table1%5B%23Headers%5D))%3C%2FFONT%3E%3CBR%20%2F%3E%3CBR%20%2F%3EWORKS!%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-278934%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-278934%22%20slang%3D%22en-US%22%3E%3CP%3E%3DSEQUENCE(n)%20is%20very%20nice%20new%20function%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Etill%20today%20I%20used%20for%20this%20following%20%3DROW(OFFSET(%24A%241%3B0%3B0%3Bn%3B1))%20when%20needed.%20One%20example%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFONT%3E%3DIF(ISERROR(TEXT((CODE(MID(%22FEDCA%40%22%3BLEFT(A11%3B1)%3B1))-50)*1000000%2BLEFT(A11%3B7)%3B%220000%5C.00%5C.00%22)%2B0)%3B%22isikukood%20vigane%22%3BIF(IF(MOD(SUMPRODUCT((MID(A11%3BCOLUMN(%24A%241%3A%24J%241)%3B1)%2B0)%3B(MID(%221234567891%22%3BCOLUMN(%24A%241%3A%24J%241)%3B1)%2B0))%3B11)%3D10%3BMOD(MOD(SUMPRODUCT((MID(A11%3BCOLUMN(%24A%241%3A%24J%241)%3B1)%2B0)%3B(MID(%223456789123%22%3BCOLUMN(%24A%241%3A%24J%241)%3B1)%2B0))%3B11)%3B10)%3BMOD(SUMPRODUCT((MID(A11%3BCOLUMN(%24A%241%3A%24J%241)%3B1)%2B0)%3B(MID(%221234567891%22%3BCOLUMN(%24A%241%3A%24J%241)%3B1)%2B0))%3B11))%3DMID(A11%3B11%3B1)%2B0%3B%22isikukood%20%C3%B5ige%22%3B%22kontrollj%C3%A4rk%20vale%22))%3C%2FFONT%3E%3CBR%20%2F%3E%3CBR%20%2F%3Ethis%20is%20to%26nbsp%3Bvalidate%20Estonian%20Personal%20ID%20-%20now%20I%20can%20rewrite%20this%20to%20shorter.%3CBR%20%2F%3E%3CBR%20%2F%3EOne%20question%20too%3A%3CBR%20%2F%3EIs%20there%20any%20idea%20or%20plan%20to%20allow%20dynamic%20arrow%20formulas%20to%20be%20used%20in%20TABLES.%20In%20same%20table%20(like%20%3DSORT)%20or%20on%20another%20table%20(like%20%3DUNIQUE)%3CBR%20%2F%3E%3CBR%20%2F%3Efor%20example%20-%20currently%20I%20have%20to%20%22sort%22%20the%20names%20on%20table%20column%20use%20following%20practice%3A%3CBR%20%2F%3E1.%20add%26nbsp%3B%20column%20RANK%3A%3D%3C%2FP%3E%0A%3CTABLE%20width%3D%2264%22%20style%3D%22border-collapse%3A%20collapse%3B%20width%3A%2048pt%3B%22%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%0A%3CTBODY%3E%0A%3CTR%20style%3D%22height%3A%2014.4pt%3B%22%3E%0A%3CTD%20width%3D%2264%22%20height%3D%2219%22%20style%3D%22height%3A%2014.4pt%3B%20width%3A%2048pt%3B%22%3E%0A%3CP%3E%3DCOUNTIF(%5BName%5D%3B%22%26lt%3B%22%26amp%3B%5B%40Name%5D)%2B1%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%3CBR%20%2F%3E2.%20then%20add%20column%20SortedName%3A%3D%3C%2FP%3E%0A%3CTABLE%20width%3D%2299%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2299%22%3E%0A%3CP%3E%3DINDEX(%5BName%5D%3BMATCH(ROW()-ROW(Table1%5B%5B%23Headers%5D%3B%5BSortedName%5D%5D)%3B%5BRank%5D%3B0))%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%3CBR%20%2F%3EDynamic%20arrays%20will%20help%20this%20a%20lot%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-278911%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-278911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F8200%22%20target%3D%22_blank%22%3E%40joe%3C%2FA%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EHere%20are%20some%20interesting%20ways%20of%20using%20Dynamic%20Array%20Formulas%3C%2FP%3E%3CP%3ESample%20File%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Ff%2Fs!AiKBTsYfZw-vgrtkdDXnLzxgimxokg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Ff%2Fs!AiKBTsYfZw-vgrtkdDXnLzxgimxokg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Multi%20Criteria%20SUMIFS%20%2F%20COUNTIFS%3C%2FP%3E%3CP%3E%3DSUMIFS(D%5BCOST%5D%2CD%5BCLIENT%5D%2CINDEX(N2%23%2C%2C1)%2CD%5BREGION%5D%2CINDEX(N2%23%2C%2C2))%3C%2FP%3E%3CP%3E2.%20Multi%20Column%20Sort%3C%2FP%3E%3CP%3E%3DSORT(D%2C%7B1%2C2%2C3%2C4%7D%2C%7B1%2C1%2C1%2C-1%7D)%3C%2FP%3E%3CP%3E3.%20Sort%20by%20Custom%20List%20e.g%20North%2C%20South%2C%20East%2C%20West%20or%20Jan%2C%20Feb%2C%20Mar..%3C%2FP%3E%3CP%3E%3DSORTBY(D%2CD%5BCLIENT%5D%2C1%2CMATCH(D%5BREGION%5D%2CCustList%2C0)%2C1%2CD%5BCOST%5D%2C-1)%3C%2FP%3E%3CP%3ECustList%20is%20either%20a%20named%20Range%20containing%20North%2C%20South%2C%20East%2C%20West%20or%20an%20array%20Constant%3C%2FP%3E%3CP%3E4.%20Emulate%20Wild%20cards%20in%20the%20Filter%20Function%3C%2FP%3E%3CP%3Ea)%20Contains%20(*Text*)%3C%2FP%3E%3CP%3E%3DFILTER(D%2CIFERROR(SEARCH(K1%2CD%5BCLIENT%5D)%2C0)%2C%22No%20Data%22)%3C%2FP%3E%3CP%3Eb)%20Begins%20with%20(Text*)%3C%2FP%3E%3CP%3E%3DFILTER(D%2CIFERROR(LEFT(D%5BCLIENT%5D%2CLEN(Q1))%3DQ1%2C0)%2C%22No%20Data%22)%3C%2FP%3E%3CP%3Ec)%20Ends%20with%20(*Text)%3C%2FP%3E%3CP%3E%3DFILTER(D%2CIFERROR(RIGHT(D%5BCLIENT%5D%2CLEN(V1))%3DV1%2C0)%2C%22No%20Data%22)%3C%2FP%3E%3CP%3E5.%20Clients%20with%20Characters%20(%3D%3F%3F%3F)%3C%2FP%3E%3CP%3E%3DFILTER(D%2CIFERROR(LEN(D%5BCLIENT%5D)%3DLEN(AC1)%2C0)%2C%22No%20Data%22)%3C%2FP%3E%3CP%3E6.%20Extract%20a%20user%20defined%20number%20of%20Random%20Records%20from%20a%20Dataset%3C%2FP%3E%3CP%3E%3DINDEX(D%2CRandIndex%2CSEQUENCE(%2CCOUNTA(D%5B%23Headers%5D)))%3C%2FP%3E%3CP%3EWhere%20RandIndex%20%3D%20NDEX(SORTBY(SEQUENCE(ROWS(D))%2CRANDARRAY(COUNTA(SEQUENCE(ROWS(D)))))%2CSEQUENCE(NoOfSamples))%3C%2FP%3E%3CP%3E7.%20Multiple%20OR%20Criteria%3C%2FP%3E%3CP%3Ea)%20Clients%20%3A%20ABB%20or%20BHEL%20or%20ORACLE%3C%2FP%3E%3CP%3E%3DFILTER(F%2CCOUNTIFS(mClients%2CF%5BCLIENT%5D))%3C%2FP%3E%3CP%3Eb)%20BU%20%3A%20US%20or%20DE%20or%20GB%3C%2FP%3E%3CP%3E%3DFILTER(F%2CMMULT(IFERROR(--(SEARCH(TRANSPOSE(mBU)%2CF%5BBU%5D)%3D1)%2C0)%2CSEQUENCE(COUNTA(mBU)%2C%2C%2C0)))%3C%2FP%3E%3CP%3Ec)%20Cost%20(%26gt%3B%2010%20and%20%26lt%3B%2020)%26nbsp%3B%20or%20(%26gt%3B90%20and%20%26lt%3B100)%3C%2FP%3E%3CP%3E%3DSORT(FILTER(F%2C((F%5BCOST%5D%26gt%3B10)*(F%5BCOST%5D%26lt%3B20))%2B((F%5BCOST%5D%26gt%3B90)*(F%5BCOST%5D%26lt%3B100)))%2C5)%3C%2FP%3E%3CP%3E8.%20Remove%20and%20Rearrange%20Columns%3C%2FP%3E%3CP%3E%3DINDEX(F%2CSEQUENCE(ROWS(F))%2C%7B3%2C5%2C1%7D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnjoy%3C%2FP%3E%3CP%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-278880%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-278880%22%20slang%3D%22en-US%22%3E%3CP%3EFYI%20-%20for%20those%20that%20also%20use%20the%20Mac%20version%20of%20Office%20365%20on%20the%20Insider%20Fast%20ring%2C%20these%20Dynamic%20Array%20functions%20showed%20up%20on%20my%20Mac%20Friday%20when%20I%20updated%20the%20latest%20build.%26nbsp%3B%3CIMG%20id%3D%22smileyhappy%22%20class%3D%22emoticon%20emoticon-smileyhappy%22%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Fi%2Fsmilies%2F16x16_smiley-happy.png%22%20alt%3D%22Smiley%20Happy%22%20title%3D%22Smiley%20Happy%22%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnxiously%20awaiting%20the%20Online%20version%20of%20Excel%20to%20support%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277674%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277674%22%20slang%3D%22en-US%22%3E%3CP%3EEd%20-%20yes%2C%20Large()%20will%20work%2C%20but%20it's%20a%20huge%20amount%20of%20work%20to%20wrap%20Large%20around%20every%20function%20where%20it%20might%20be%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegarding%20the%20Single()%20function%2C%20I%20just%20checked%20today%20(25th%20Oct)%2C%20and%20it%20doesn't%20seem%20to%20work%20as%20you%20said%3A%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20using%20the%20same%20UDF%20to%20return%20a%20series%20of%20columns.%26nbsp%3B%20As%20currently%20working%20the%20function%20is%20copied%20down%20each%20column%20and%20is%20not%20entered%20as%20an%20array%20function.%26nbsp%3B%20Each%20instance%20of%20the%20function%20returns%20a%201D%20array%2C%20but%20only%20the%20first%20value%20is%20displayed.%3C%2FP%3E%3COL%3E%3CLI%3EOn%20opening%20in%20Excel%20with%20dynamic%20arrays%20everything%20displays%20correctly%2C%20and%20the%20Single()%20function%20has%20not%20been%20applied.%26nbsp%3B%20I%20don't%20know%20if%20this%20is%20a%20change%20in%20functionality%2C%20or%20if%20the%20Single()%20function%20is%20only%20applied%20under%20different%20conditions.%3C%2FLI%3E%3CLI%3EIf%20I%20change%20any%20input%20values%20all%20the%20functions%20recalculate%20correctly.%3C%2FLI%3E%3CLI%3EIf%20I%20press%20F2%20then%20Enter%20with%20any%20function%2C%20it%20returns%20SPILL.%26nbsp%3B%20Undo%20will%20return%20the%20original%20display%2C%20but%20obviously%20removes%20any%20edits.%3C%2FLI%3E%3CLI%3EIf%20I%20press%20F2%20then%20Ctrl-Shift-Enter%20the%20function%20displays%20correctly%20(with%20%7B%7D%20in%20the%20Edit%20box).%3C%2FLI%3E%3CLI%3EIf%20I%20wrap%20the%20function%20in%20Single()%20it%20displays%20the%20first%20result%20when%20entered%20with%20Enter.%3C%2FLI%3E%3CLI%3EIf%20I%20save%20and%20open%20in%20Excel%20without%20array%20functions%20the%20Single()%26nbsp%3B%20functions%20display%20as%20%23NAME%2C%20with%20the%20function%20preceded%20by%20_xlfunc.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIn%20summary%3A%3C%2FP%3E%3COL%3E%3CLI%3EThe%20Single()%20function%20seems%20redundant%2C%20since%20you%20can%20get%20the%20same%20effect%20with%20Ctrl-Shift-Enter%2C%20which%20will%20also%20work%20in%20previous%20versions.%3C%2FLI%3E%3CLI%3EUsing%20Ctrl-Shift-Enter%20also%20allows%26nbsp%3Ba%20function%20to%20be%20edited%2C%20and%2For%20a%20partial%20array%20to%20be%20returned.%3C%2FLI%3E%3CLI%3ERather%20than%20deprecating%20Ctrl-Shift-Enter%2C%20it%20would%20greatly%20enhance%20the%20use%20of%20dynamic%20arrays%20if%20it%20was%20retained%20as%20an%20active%20feature%2C%20and%20was%20also%20enhanced%20to%20allow%20the%20size%20of%20the%20array%20output%20range%20to%20be%20reduced%2C%20as%20well%20as%20increased.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277526%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232962%22%20target%3D%22_blank%22%3E%40Doug%20Jenkins%3C%2FA%3E%3C%2FP%3E%3COL%3E%3CLI%3ECan%20you%20wrap%20your%20function%20in%20LARGE()%20to%20return%20only%20the%20topN%20results%20to%20prevent%20it%20from%20returning%20too%20many%20rows%3F%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel-Blog%2FPreview-of-Dynamic-Arrays-in-Excel%2Fbc-p%2F268979%2Fhighlight%2Ftrue%23M355%22%20target%3D%22_self%22%3ESee%20Sergei's%20example%3C%2FA%3E%20here.%3C%2FLI%3E%3CLI%3EThe%20%3DSINGLE()%20function%20will%20automatically%20show%20up%20sometimes%20in%201811%20when%20it%20appears%20that%20implicit%26nbsp%3Bintersection%26nbsp%3Bis%20happening%2C%20but%20when%20you%20reopen%20the%20file%20in%20a%20version%20of%20Excel%20that%20doesn't%20understand%20Dynamic%20Array%20functions%2C%20the%20%3DSINGLE()%20is%20removed.%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel-Blog%2FPreview-of-Dynamic-Arrays-in-Excel%2Fbc-p%2F268556%2Fhighlight%2Ftrue%23M348%22%20target%3D%22_self%22%3ESee%20Joe's%20explanation%20of%20what%20is%20going%20on%20here%3C%2FA%3E.%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277206%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277206%22%20slang%3D%22en-US%22%3E%3CP%3EAlthough%20dynamic%20arrays%20are%20good%20in%20principle%2C%20the%20current%20implementation%20has%26nbsp%3Btwo%20features%20that%20cause%20significant%20problems%20for%20current%20users%20of%20array%20functions.%3C%2FP%3E%3CP%3E1.%26nbsp%3B%20I%20use%20UDFs%20returning%20arrays%20extensively.%26nbsp%3B%20Often%20I%20only%20need%20the%20top%20left%20value%2C%20or%20some%20part%20of%20the%20return%20array.%26nbsp%3B%20If%20the%20whole%20array%20overlaps%20some%20existing%20data%2C%20these%20functions%20now%20return%20SPILL.%26nbsp%3B%3C%2FP%3E%3CP%3E2.%26nbsp%3B%20It%20seems%20that%20sometimes%20the%20new%20Single%20function%20is%20applied%20automatically.%26nbsp%3B%20Presumably%20this%20is%20not%20compatible%20with%20earlier%20Excel%20versions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERather%20than%20deprecating%20use%20of%20Ctrl-Shift-Enter%2C%20why%20not%20incorporate%20it%20in%20the%20new%20functionality%3F%26nbsp%3B%20I%20suggest%3A%3C%2FP%3E%3CP%3E1.%20If%20the%20function%20is%20entered%20with%20the%20Enter%20key%2C%20it%20will%20return%20the%20entire%20array%2C%20or%20SPILL%2C%20i.e.%20as%20in%20the%20current%20version.%3C%2FP%3E%3CP%3E2.%20If%20it%20is%20entered%20with%20Ctrl-Shift-Enter%20it%20will%20return%20a%20single%20cell%2C%20or%20whatever%20range%20is%20selected%20(or%20SPILL%20if%20the%20selected%20range%20contains%20any%20data).%3C%2FP%3E%3CP%3E3.%20Allow%20the%20returned%20range%20size%20to%20be%20reduced%20in%20extent%2C%20as%20well%20as%20increased%2C%20when%20entering%20with%20Ctrl-Shift-Enter.%3C%2FP%3E%3CP%3E4.%20Allow%20conversion%20between%20the%20two%20behaviours%20by%20re-entering%20with%20Enter%20or%20Ctrl-Shift-Enter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277102%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277102%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232473%22%20target%3D%22_blank%22%3E%40erol%20sinan%20zorlu%3C%2FA%3E%26nbsp%3B-%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fwhat-s-new-in-excel-2019-for-windows-5a201203-1155-4055-82a5-82bf0994631f%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EYou%20can%20see%20here%20what%20is%20new%20in%20Excel%202019%3C%2FA%3E%20over%202016.%20The%20Dynamic%20Array%20functions%20will%20no%20doubt%20be%20in%20the%20next%20version%20of%20Excel%20(2022%20or%20whatever)%20but%20right%20now%20it%20isn't%20even%20in%20Office%20365%20yet%2C%20just%20insider%20builds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20the%20problem%20with%20the%20perpetual%20license.%20You%20get%20what%20you%20get%20for%20when%20it%20was%20released.%20My%20understanding%20is%20Excel%202019%20is%20effectively%20the%201803%20build%20of%20Excel%20from%20Office%20365%2C%20so%20what%20Office%20365%20users%20generally%20had%20in%20March%20of%202018.%20Then%20I%20suppose%20they%20spent%20those%206%20months%20really%20fixing%20any%20bugs%20in%201803%20before%20slapping%20the%202019%20sticker%20on%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20what%20Paul%20Thurrott%20calls%20%22%3CA%20href%3D%22https%3A%2F%2Fwww.thurrott.com%2Fpodcasts%2Fwindows-weekly%2F141349%2Fwindows-weekly-539-yesterdays-office-tomorrow%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGetting%20Yesterday's%20Office%20Tomorrow%3C%2FA%3E%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277069%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277069%22%20slang%3D%22en-US%22%3E%3CP%3EWell%20I%20was%20expecting%20Excel%202019%20would%20be%20a%20massive%20step%20forward%20however%20missing%20these%20dynamic%20array%20formulas%20it%20seems%20to%20be%20a%20face%20lift%20of%202016.%20It%20is%20a%20pity%20to%20hear%20also%20there%20is%20no%20plan%20on%20implementing%20this%20function%20to%20it.%20I%20am%20very%20dissapointed%20right%20now%20and%20see%20no%20reason%20to%20upgrade%20it%20from%202016.%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277058%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277058%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232473%22%20target%3D%22_blank%22%3E%40erol%20sinan%20zorlu%3C%2FA%3E%20Dynamic%20Arrays%20will%20not%20be%20coming%20to%20Excel%202019%2C%20only%20Excel%20365.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%20No%20plans%20to%20deprecate%20SUMPRODUCT%2C%20it%20has%20its%20advantages.%20Many%20users%20find%20it%20more%20readable%2Fdescriptive%20than%20SUM(A1%3AA10*B1%3AB10)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276906%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276906%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232597%22%20target%3D%22_blank%22%3E%40Charley%20Kyd%3C%2FA%3E%26nbsp%3B-%20this%20really%20highlights%20the%20need%20to%20allow%20dynamic%20array%20functions%20to%20work%20in%20Excel%20Tables%2C%20causing%20them%20to%20expand%20as%20necessary%20to%20handle%20the%20results.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276898%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276898%22%20slang%3D%22en-US%22%3E%3CP%3Ethis%20new%20fnctions%20are%20a%20good%20addition%20however%20I%20can%20not%20find%20them%20in%20Excel%202019.%26nbsp%3BWhere%20can%20I%20find%20them%3F%20Or%20are%20they%20only%20available%20for%20Excel%20365%3F%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276879%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276879%22%20slang%3D%22en-US%22%3E%3CP%3EHmmm...%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20any%20way%20to%20set%20up%20a%20grand%20total%20formula%20and%20top-border%20format%20immediately%20below%20a%20spilled%20range%3F%20Or%20are%20we%20going%20to%20have%20to%20accustom%20the%20world%20to%20our%20placing%20aggregates%20above%20a%20list%20of%20data%2C%20rather%20than%20below%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276854%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276854%22%20slang%3D%22en-US%22%3E%3CP%3EOutstanding!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20I%20be%20able%20to%26nbsp%3Btreat%20Pivot%20Tables%20as%20a%20Dynamic%20Array%3F%20Right%20now%2C%20pivots%20don't%20offer%20a%20built-in%20way%20to%20reference%20them%20in%20formulas%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-275948%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-275948%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20join%20the%20office%20insider%20program%3F%3C%2FP%3E%3CP%3EI%20am%20a%20Mac%20user%20with%20an%20office%20365%20subscription.%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-271191%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271191%22%20slang%3D%22en-US%22%3E%3CP%3EHmmm...%20so%20in%20New%20Excel%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(A1%3AA10*B1%3AB10)%20replaces%20%3DSUMPRODUCT(A1%3AA10%2CB1%3AB10)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20SUMPRODUCT()%20and%20other%20functions%20that%20ignore%20implicit%20intersection%20be%20depreciated%20as%20existing%20functions%20start%20ignoring%20it%20as%20well%20-%20where%20you'd%20need%20to%20use%20SINGLE()%20to%20continue%20using%20it%3F%20(Not%20dropped%2C%20but%20where%20things%20like%20CONCATENATE()%20are%20not%20encouraged%20as%20CONCAT()%20is%20the%20new%20way%20to%20do%20it.)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-271179%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271179%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20anyone%20know%20a%20way%20to%20find%20Excel%20files%20in%20Office%20365%20with%20CSE%20formulas%20in%20them%3F%20Searching%20for%20%7B%20and%20%3D%7B%20returns%20nothing%20or%20in%20the%20case%20of%20Delve%2C%20errors.%20I'd%20love%20to%20see%20what%20has%20been%20done%20in%20the%20org%20over%20the%20past%202%20decades%20and%20see%20what%20can%20be%20replaced%20with%20the%20new%20functions.%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-271176%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F16571%22%20target%3D%22_blank%22%3E%40sameer%20bhide%3C%2FA%3E%3C%2FP%3E%3COL%3E%3CLI%3EThe%20array%20functions%20can%20be%20used%20in%20the%20criteria%20section%20of%20the%20Advanced%20Filter%20feature%2C%20but%20the%20new%20array%20functions%20will%20return%20results%20that%20dynamically%20expand%20and%20contract%2C%20so%20make%20sure%20the%20range%20in%20the%20criteria%20will%20dynamically%20expand%20as%20well.%20By%20default%20it%20uses%20absolute%20references.%3C%2FLI%3E%3CLI%3EYou%20can%20use%20the%20arrays%20in%20a%20Match%2C%20though%20in%20your%20example%2C%20sorting%20isn't%20necessary%20to%20match.%20But%20it%20would%20work%20great%20with%20a%20FILTER()%20for%20example.%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-271112%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271112%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E..%20Thanks%20for%20the%20update!%20I%20will%20be%20looking%20forward%20to%20get%20this%20update.%20Super%20excited%20to%20explore%20new%20features!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EMohit%20Verma%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-271108%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271108%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F227392%22%20target%3D%22_blank%22%3E%40Mohit%20Verma%3C%2FA%3E%2C%20not%20everyone%20gets%20everything%20straight%20away%20in%20Office%20Insiders%20Fast%2C%26nbsp%3B%20things%20are%20gradually%20released.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20can%20be%20frustrating%20having%20to%20wait%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-271105%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271105%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%26nbsp%3BCan't%20see%20Functions%20like%20Sequence%2C%20Filter%20in%20Office%20365%20Insider!%20Could%20anyone%20please%20help%20here%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EMohit%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-270962%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-270962%22%20slang%3D%22en-US%22%3E%3CP%3E1.%20Can%20the%20new%20array%20formulas%20be%20used%20in%20the%20Criteria%20Range%20of%20an%20Advanced%20Filter%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Can%20SORT%20be%20used%20as%20below%20to%20take%20advantage%20of%20the%20Binary%20search%20option%3C%2FP%3E%3CP%3EMATCH(B1%2CSORT(A1%3AA10)%2C1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268991%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268991%22%20slang%3D%22en-US%22%3E%3CP%3EHrm....%20Now%20I%20am%20trying%20to%20recreate%20what%20I%20did.%20SEQUENCE(1)%20works%2C%20but%20not%20anything%20%26gt%3B1.%20Maybe%20I%20was%20doing%20some%20inside%20of%20and%20some%20outside%20of%20a%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20for%20your%20LARGE()%20function.%20Very%20nice.%20Much%20nicer%20and%20cleaner.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268989%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268989%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%2C%20how%20did%20you%20receive%20%3DSEQUENCE(10)%20in%20the%20table%20without%20errors%3F%20SPILL%20doesn't%20work%20with%20tables%26nbsp%3B%3CFONT%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2F-spill-error-table-formula-8bfa1758-1be4-42f4-a974-c4d4bc513d4b%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2F-spill-error-table-formula-8bfa1758-1be4-42f4-a974-c4d4bc513d4b%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268979%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268979%22%20slang%3D%22en-US%22%3E%3CP%3EBy%20the%20way%2C%20%3DLARGE(array%2CSEQUENCE(N))%20works%20as%20TopN%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20539px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F55890i48D711E0466D4D1C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268900%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268900%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%26nbsp%3B%3CSPAN%3E%23CALC!%20is%20a%20new%20one%20and%20not%20documented%20yet%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268866%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268866%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20this%20is%20a%20bug%20with%20SEQUENCE().%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3COL%3E%3CLI%3ECreate%20a%20table%20with%2010%20rows%20not%20including%20the%20header.%3C%2FLI%3E%3CLI%3EHave%20at%20least%202%20columns%2C%20say%20Col1%20and%20Col2%3C%2FLI%3E%3CLI%3EFill%20column%201%20with%20jibberish.%3C%2FLI%3E%3CLI%3EPut%20this%20in%20the%20first%20row%20of%20Col2%3A%20%3DSEQUENCE(ROWS(%5BCol1%5D)%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIt%20returns%20%23SPILL!%20instead%20of%20a%20sequence%20of%20numbers%20that%20matches%20the%20number%20of%20rows%20in%20the%20table.%20ROWS(%5BCol1%5D)%20is%20correctly%20returning%2010.%20Replace%20ROWS()%20with%2010%20and%20it%20works%2C%20but%20it%20is%20no%20longer%20dynamic.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20submitted%20a%20frown%20face%20for%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268863%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268863%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20guess%20%23CALC!%20is%20a%20new%20error%20type%20when%20the%20arrays%20go%20awry%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActually%20looks%20like%20%23UNKNOWN!%20and%20%23FIELD!%20are%20also%20new%20for%20Array%20errors.%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Ferror-type-function-10958677-7c8d-44f7-ae77-b9a9ee6eefaa%3FNS%3DEXCEL%26amp%3BVersion%3D90%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL900%26amp%3BHelpId%3Dxlmain11.chm60309%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EThis%20page%20needs%20to%20be%20updated%3C%2FA%3E.%20I'd%20like%20more%20explanation%20on%20what%20these%20error%20types%20are%20to%20trap%20things%20with%20a%20bit%20less%20trial%20and%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268841%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268841%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%2C%20voted%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268815%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268815%22%20slang%3D%22en-US%22%3E%3CP%3EDone%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%26nbsp%3B%20-%20%3CA%20href%3D%22https%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F35667973-add-topn-optional-parameter-for-new-dynamic-array%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F35667973-add-topn-optional-parameter-for-new-dynamic-array%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wasn't%20thinking%20of%20a%20separate%20TopN%20function%2C%20just%20an%20optional%20parameter%20in%20the%20Dynamic%20Array%20functions%20that%20it%20would%20make%20sense%20for.%20SORT%20and%20FILTER%20could%20definitely%20use%20it%20for%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268598%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268598%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20these%20functions%20be%20used%20in%20the%20Criteria%20Range%20of%20an%20Advanced%20Filter%3C%2FP%3E%3CP%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268556%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268556%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%20Great%20to%20hear%20you%20now%20have%20access!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOld%20Excel%20would%20silently%20apply%20implicit%20intersection%20logic%20to%20its%20formulas%20to%20force%20single%20value%20results.%20Dynamic%20Array%20Excel%20no%20longer%20needs%20this%20as%20it%20can%20output%20arrays%20natively.%20So%20instead%20of%20implicitly%20intersecting%2C%20DA%20Excel%20calls%20out%20where%20this%20could%20have%20happened%20in%20old%20formulas%20using%20the%20SINGLE%20function.%20In%20your%20example%2C%20%3CSPAN%3E%3DTableName%5BField%5D%20is%20a%26nbsp%3B%20full%20column%20reference%20that%20only%20has%20one%20value%20but%20if%20you%20ever%20added%20a%20new%20row%20to%20that%20table%2C%20that%20very%20same%20reference%20would%20return%20multiple%20values%20and%2C%20in%20old%20Excel%2C%20it%20would%20have%20triggered%20implicit%20intersection.%20To%20ensure%20your%20formula%20continues%20to%20operate%20as%20originally%20authored%2C%20we%20show%20where%20implicit%20intersection%20would%20take%20place%20using%20SINGLE.%20You%20are%20free%20to%20remove%20the%20SINGLE%20from%20your%20formula%20if%20you%20do%20not%20want%20implicit%20intersection%20to%20apply.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ETopN%20sounds%20like%20a%20great%20function%20candidate.%20%3C%2FSPAN%3E%3CSPAN%3EOur%20initial%20focus%20is%20on%20getting%26nbsp%3BDynamic%20Arrays%20out%20with%20the%201st%20wave%20of%20functions%20but%20we%20are%20looking%20forward%20to%26nbsp%3Bunlocking%20more%20scenarios%20with%20future%20functions.%26nbsp%3BIf%20there%20is%20a%20function%20you'd%20like%20to%20see%2C%20please%20add%20it%20to%20%3CA%20href%3D%22https%3A%2F%2Fexcel.uservoice.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Euservoice%20%3C%2FA%3Eso%20that%20we%20have%20a%20place%20to%20discuss%20the%20design%20and%20the%20community%20can%20vote.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268541%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268541%22%20slang%3D%22en-US%22%3E%3CP%3EWondering%20if%20this%20early%20in%20the%20process%20there%20is%20still%20time%20to%20get%20changes%20to%20these%20dynamic%20array%20functions.%20I%20think%20a%20super%20useful%20argument%20to%20these%20would%20be%20a%20TopN%20optional%20argument.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20%3DSORT(A1%3AA100%2C%2C%2C%2C10)%20would%20sort%20the%20data%20in%20A1%3AA100%2C%20but%20only%20return%20the%20top%2010.%20The%20only%20way%20I%20can%20see%20to%20do%20that%20is%20to%20put%20the%20%3DSORT()%20funcition%20in%20Cell%20C1%2C%20highlight%20C1%3AC10%2C%20then%20edit%2C%20and%20old-school%20CSE%20the%20thing%20to%20a%20hard%20array.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20might%20be%20able%20to%20figure%20out%20a%20way%20nesting%20FILTER()%20and%20SEQUENCE()%2C%20but%20a%20TopN%20argument%20would%20make%20it%20so%20simple.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh%20wait.%20This%20works.%20Put%20some%20random%20text%20in%20R3%3AR20.%20This%20will%20return%20the%20top%2010%20items.%20this%20is%20ugly%2C%20with%20a%20capital%20U.G.L.Y.%20But...%20it%20isn't%20a%20CSE%20formula.%20I'm%20sure%20I%20could%20clean%20this%20up%20as%20I%20threw%20this%20together%20in%20about%2010min%2C%20but%20still.%20TopN%20dude....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLEFT(FILTER(R3%23%26amp%3BSEQUENCE(10%2C1%2C0.00001%2C0.0001)%2CNOT(ISNA(R3%23%26amp%3BSEQUENCE(10%2C1%2C0.00001%2C0.0001))))%2CFIND(%22.%22%2CFILTER(R3%23%26amp%3BSEQUENCE(10%2C1%2C0.00001%2C0.0001)%2CNOT(ISNA(R3%23%26amp%3BSEQUENCE(10%2C1%2C0.00001%2C0.0001)))))-2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268534%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268534%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B-%20makes%20sense%2C%20and%20was%20along%20the%20lines%20of%20what%20I%20was%20thinking.%20I%20think%20this%20will%20cause%20a%20bit%20of%20initial%20confusion%2C%20and%20wonder%20if%20the%20conversion%20from%20old%20to%20new%20should%20be%20smarter%20and%20not%20put%20%3DSINGLE()%20every%20possible%20place.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20%3DTableName%5BField%5D%20gives%20same%20result%20as%20%3DSINGLE(TableName%5BField%5D)%20then%20don't%20wrap%20it%20with%20SINGLE().%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20this%20is%20one%20of%20the%20biggest%20changes%20Microsoft%20has%20done%20with%20Excel%20in%20recent%20memory%20that%20has%20such%20implications%20to%20not%20change%20how%20calculations%20are%20expected%20to%20work%20as%20files%20are%20upgraded%20to%20the%20new%20calculation%20engine.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268528%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%2C%20I%20guess%20that's%20due%20to%20algorithm%20of%20the%20conversion%20of%20old%20style%20expressions%20to%20new%20one.%20That's%20like%20%3D%7B1%2C2%2C3%7D%20in%20current%20Excel%20is%20converted%20to%20first%20element%20in%20background%20and%20returns%201.%20New%20Excel%20will%20return%20spill%20for%20the%20same%20formula.%20To%20return%20only%20first%20element%20we%20shall%20use%20%3DSINGLE(%7B1%2C2%2C3%7D).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20we%20open%20in%20new%20Excel%20the%20file%20with%20such%20formula%20created%20in%20classic%20Excel%20it%20shows%20such%20conversion%20with%20SINGLE.%26nbsp%3B%20In%20classic%20Excel%20%22single%22%20is%20somewhere%20in%20background%20and%20not%20visible.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETable%5BColumn%5D%20is%20an%20array%20which%20is%20returned%20to%20the%20range.%20For%20one%20element%20only%20it%20could%20be%20converted%20into%20one%20cell%20range%20and%20we%20see%20something%20as%20above.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAll%20above%20is%20only%20my%20guess%2C%20I%20didn't%20dig%20too%20deep%20with%20investigations%20and%20have%20no%20Microsoft%20knowledge%20of%20things.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-268504%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-268504%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20anyone%20seeing%20a%20lot%20of%20formulas%20being%20wrapped%20with%20SINGLE()%20after%20getting%20the%20new%20functions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20number%20of%20spreadsheets%20that%20return%20a%20single%20row%20table%20from%20Power%20Query%2C%20and%20then%20I%20refer%20to%20those%20tables%20in%20other%20formulas.%20So%20%3DTableName%5BFieldName%5D%20would%20give%20me%20the%20count%20of%20rows%20in%20a%20query%2C%20or%20the%20total%20of%20a%20value%2C%20etc.%20Usually%20used%20for%20error%20checking.%20One%20of%20my%20files%20this%20morning%20had%207%20SINGLE()%20functions%20in%20it.%20some%20simple%20as%20above%2C%20some%20deeply%20nested%20in%20those%20stupidly%20long%20uneditable%20formulas%20I%20am%20prone%20to%20do%20over%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20those%20are%20all%20showing%20up%20as%26nbsp%3B%3CSPAN%3E%3DSINGLE(TableName%5BFieldName%5D)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECreating%20a%20new%20single%20row%20table%20in%20a%20new%20file%2C%20typing%20%3DTable2%5Bdata%20field%5D%20does%20not%20cause%20it%20to%20wrap%20in%20SINGLE()%2C%20however....%20if%20I%20create%20the%20formula%20in%20Excel%20Online%20or%20an%20older%20version%20of%20Excel%2C%26nbsp%3B%3DTable1%5BTest%20data%5D%20becomes%26nbsp%3B%3DSINGLE(Table1%5BTest%20data%5D)%20when%20I%20open%20it%20in%20the%20new%20version.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EEDIT%3A%20Is%20this%20the%20right%20place%20to%20discuss%20issues%20or%20questions%20about%20these%20new%20functions%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-266361%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266361%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20love%20to%20know%20the%20roll-out%20timetable%2C%20I'm%20unfortunately%20not%20on%20the%20insider%20list%20but%20want%20to%20get%20all%20my%20work%20colleagues%20tee'd%20up%20at%20the%20right%20time%20to%20change%20all%20of%20our%20many%20spreadsheets%20-%20so%20much%20complexity%20can%20be%20removed%2C%20so%20much%20storage%20space%20can%20be%20saved!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-265414%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-265414%22%20slang%3D%22en-US%22%3E%3CP%3EWho%20do%20I%20have%20to%20bribe%20to%20get%20them%20to%20enable%20this%20on%20my%20Fast%20Insider%20build%3F%20I%20can%20send%20money%20via%20paypal%2C%20venmo%2C%20apple%20pay%2C%20etc.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-264820%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264820%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%26nbsp%3BWhat%20would%20be%20the%26nbsp%3Bofficial%20way%20of%20checking%20whether%20an%20Excel%20instance%20hosting%20my%20add-in%20supports%20dynamic%20arrays%3F%26nbsp%3BIs%20there%20a%20first%20Excel%20version%20that%20supports%20dynamic%20arrays%20and%20any%20later%20version%20will%20too%2C%20or%20will%20this%20be%20selectively%20enable%20depending%20on%20other%20factors%3F%20How%20should%20I%20check%20from%20inside%20my%20add-in%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-264446%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264446%22%20slang%3D%22en-US%22%3E%3CP%3EReally%20powerful%20new%20feature%2C%26nbsp%3B%20absolutely%20loving%20it.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDynamic%20drop%20down%20lists%20are%20now%20simple%3C%2FP%3E%0A%3CP%3EMany%20uses%20of%20Pivot%20Tables%20are%20now%20easily%20replicated%20in%20automatically%20refreshing%20formulas%3C%2FP%3E%0A%3CP%3EWell%20done%20Excel%20Team!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-264374%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%26nbsp%3Bwe're%20glad%20you%20like%20the%20look%20of%20the%20feature!%20Yes%20you%20can%20wrap%20functions.%26nbsp%3B%3CSPAN%3E%3DCOUNTA(UNIQUE(A1%3AA10))%20will%20work%20as%20expected.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F88597%22%20target%3D%22_blank%22%3E%40Emma%20Bailey%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F216786%22%20target%3D%22_blank%22%3E%40Phil%20Trick%3C%2FA%3E%26nbsp%3BThanks!%20Looking%20forward%20to%20getting%20it%20out.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F33094%22%20target%3D%22_blank%22%3E%40Jon%20Wittwer%3C%2FA%3E%26nbsp%3BIt%20can%20be%20used%20with%20Data%20Validation.%20DV%20%2B%20UNIQUE%20%2B%20A1%23%20%3D%20%3Aok_hand%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BDynamic%20arrays%20will%20not%20be%20in%20Excel%202019.%20Excel%202019%20has%20just%20been%20released%20and%20this%20feature%20is%20only%20just%20making%20it%20into%20O365%20insiders.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F218373%22%20target%3D%22_blank%22%3E%40Patrick%20Matthews%3C%2FA%3E%26nbsp%3BSure%20can!%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F213104%22%20target%3D%22_blank%22%3E%40Govert%20van%20Drimmelen%3C%2FA%3E%26nbsp%3BGreat%20questions!%20I'll%20follow%20up%20with%20the%20answers%20next%20week.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-264295%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264295%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20then%2C%20to%20get%20a%20concatenated%20list%20of%20the%20unique%20values%20in%20some%20range%2C%20just%20embed%20UNIQUE%20inside%20TEXTJOIN%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBrilliant!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-263778%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-263778%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20someone%20that%20has%20these%20know%20if%20you%20can%20wrap%20other%20functions%20around%20them%3F%20For%20example%2C%20would%20%3DCOUNTA(UNIQUE(A1%3AA10))%20return%20the%20number%20of%20unique%20items%20in%20A1%3AA10%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20old%20way%20with%20the%20CTRL-SHIFT-ENTER%20would%20involve%20something%20like%26nbsp%3B%3CSPAN%3E%3DSUM(IF(FREQUENCY(MATCH(B2%3AB10%2CB2%3AB10%2C0)%2CMATCH(B2%3AB10%2CB2%3AB10%2C0))%26gt%3B0%2C1)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-263724%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-263724%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BLoving%20the%20new%20spill%20concept%20and%20new%20functions.%20Will%20vastly%20improve%20dynamic%20named%20ranges%20so%20that%20no%20longer%20is%20it%20an%20offset%20with%20a%20calculated%20height%20and%20width%20but%20just%20%24F%245%23.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20thank%20you%20for%20native%20UNIQUE%20and%20SORT%20functions.%20I%20can't%20wait%20to%20retire%26nbsp%3Bmy%20Excel%20VBA%20QuickSort%20%2F%20Unique%20library%20plus%20this%26nbsp%3Breduces%20the%20number%20of%20functions%20I%20need%20to%20rewrite%20in%20the%20Javascript%20API%2C%20so%20double%20points.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-263712%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-263712%22%20slang%3D%22en-US%22%3E%3CP%3EAwesome!%3C%2FP%3E%3CP%3EBut%2C%20I%20hope%20you%20update%20Excel%202019%20to%20include%20these%20new%20features!%3C%2FP%3E%3CP%3EPlease%20do%20not%20just%20limit%20them%20to%20Excel%20for%20Office%20365.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-263485%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-263485%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20awesome!%26nbsp%3BI've%20been%20hoping%20that%20Excel%20would%20add%20the%20UNIQUE%20and%20SORT%26nbsp%3Bfunctions%20that%20have%20been%20available%20in%20Google%20Sheets%2C%20but%20I%20am%20even%20more%20impressed%20that%20Excel%20has%20done%20far%20more%20than%20just%20adding%20these%20functions.%20I%20can't%20wait%20to%20see%20what%20I%20can%20do%20with%20the%20new%20A1%23%20notation%20(I'm%20really%20hoping%20it%20can%20be%20used%20in%20data%20validation%20dropdowns).%20And%20the%20SEQUENCE%20function%20is%20brilliant%20-%20ought%20to%20make%20creating%20advanced%20formulas%20easier%20(and%20hopefully%20without%20being%20volatile).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-262595%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-262595%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20-%20this%20is%20a%20very%20exciting%20feature.%3C%2FP%3E%3CP%3EI%20have%20a%20few%20initial%20questions%3A%3C%2FP%3E%3CUL%3E%3CLI%3ECan%20dynamic%20arrays%20be%20used%20with%20RTD-based%20functions%3F%20Currently%20there%20are%20some%20bugs%20(including%20buggy%26nbsp%3BRTD%20topic%20disconnect)%20when%20RTD%20is%20called%20from%20legacy%20CSE%20array%26nbsp%3Bformulae%20(even%20if%20the%20RTD%20call%20is%20through%20an%20indirection).%20It%20would%20be%20an%20immense%20help%20if%26nbsp%3Bthere%20were%20RTD%20support%20in%20conjunction%20with%20the%20dynamic%20arrays%2C%20for%20async%2C%20streaming%20and%20other%20scenarios.%3C%2FLI%3E%3CLI%3EDo%20I%20understand%20correctly%20that%20any%20UDF%20defined%20in%20a%20.xll%20add-in%20which%20returns%20an%20XLOPER%20will%26nbsp%3Bautomatically%20work%20with%20the%20dynamic%20array%20behaviour%3F%26nbsp%3BCan%20I%20effectively%20recreate%20a%20%22FILTER%22%20or%20%22SORT%22%20function%20inside%20an%20add-in%20or%20do%26nbsp%3Bthese%20functions%20themselves%20have%20internal%20magic%20too%3F%20Is%20%22SINGLE%22%20special%20or%20can%20I%20opt%20into%20implicit%20intersection%20behaviour%20from%20inside%20my%20array-returning%20UDF%20function%20by%20calling%20SINGLE%20via%20the%20C%20API%3F%3C%2FLI%3E%3CLI%3ECan%20dynamic%20arrays%20be%20used%20inside%20Tables%3F%20Currently%20array%20functions%20cannot%20be%20put%20in%20Tables.%20It%20would%20be%20great%20to%20have%20the%20auto-expanding%20and%20shrinking%20Table%20behaviour%20anchored%20on%20a%20synamic%20array%20function.%3C%2FLI%3E%3CLI%3EHow%20does%20the%20%23%20spill%20operator%20impact%20the%26nbsp%3Breference%20passed%20to%20a%20function%26nbsp%3Btaking%20an%20XLOPER%20reference%20parameter%20in%20the%20C%20API%3F%20Is%20there%20any%20way%20to%20know%20that%20a%20parameter%20is%20a%20dynamic%20array%20(as%20opposite%20to%20a%20fixed%2C%20explicit%20range)%3F%3C%2FLI%3E%3CLI%3EHow%20is%20the%20caller%20identified%20for%20dynamic%20array%20functions%20(via%20xlfCaller%20in%20the%20C%20API%20or%20Application.Caller%20in%20VBA)%3F%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-262566%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-262566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EThis%20is%20super%20exciting!%20I%20use%20the%20FILTER%20function%20in%20Google%20Sheets%20so%20having%20this%20%3C%2FSPAN%3E%3CSPAN%3Efunctionality%20come%20to%20Excel%20is%20great.%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%20I%20can%20already%20see%20several%20ways%20these%20new%20features%20are%20going%20to%20help%20out%20in%20my%20work%2C%20so%20I%E2%80%99m%20looking%20forward%20to%20trying%20them%20(and%20hoping%20to%20be%20in%20the%20initial%20Insider%20testing%20group%20to%20get%20them%20sooner!)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-262111%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-262111%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20incredible.%20I%20cannot%20wait%20to%20try%20them%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20on%20Insider%20Fast%20in%20Office%20365%20but%20they%20don't%20exist%20for%20me%20yet.%20I%20think%20it%20is%20a%20shame%20that%20even%20if%20we%20sign%20up%20for%20Insider%20Fast%20the%20rollout%20of%20new%20features%20to%20try%20is%20spread%20out.%20I%20thought%20the%20point%20of%20Insider%20Fast%20was%20to%20get%20the%20latest%20publicly%20available%20features%2C%20not%20to%20sign%20up%20to%20be%20on%20a%20list%20to%20randomly%20get%20the%20latest%20features.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-450699%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-450699%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20waiting%20patiently%20since%20this%20was%20announced%20in%20September%2C%20I'm%20on%20the%20insider%20program%20and%20have%20been%20checking%20every%20month%20but%20nothing%20yet.%3C%2FP%3E%3CP%3EAny%20update%20on%20when%20these%20new%20functions%20will%20become%20a%20reality%3F%3C%2FP%3E%3CP%3Echeers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-450975%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-450975%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320797%22%20target%3D%22_blank%22%3E%40mdallura%3C%2FA%3E%20%2C%20do%20you%20see%20Office%20Insider%20if%20you%20check%20File-%26gt%3BAccount%20for%20your%20Excel%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20516px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108676i343F781AA06113CA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455791%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455791%22%20slang%3D%22en-US%22%3E%3CP%3EToday%20I%20start%20for%20intent%20get%20dynamically%20set%20of%20random%20numbers%3CBR%20%2F%3E%3CBR%20%2F%3Esome%20attempts%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSEQUENCE(10)*0%2BRANDBETWEEN(10%3B20)%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%3DSEQUENCE(10)*0%2BRAND()%3CBR%20%2F%3E%3CBR%20%2F%3ENO%20help%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20idea%20-%20how%20to%20get%20set%20of%20random%20numbers%20dynamically%3CBR%20%2F%3E%3CBR%20%2F%3EOR%20random%20subset%20of%20actual%20numbers%20dynamically%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455838%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F56123%22%20target%3D%22_blank%22%3E%40Henn%20Sarv%3C%2FA%3E%20DA's%20calculates%20in%20a%201x1%20context%2C%20they%20then%20spill.%20This%20means%20that%20RAND%20and%20RANDBETWEEN%20will%20only%20provide%201%20value%20which%20is%20what%20you%20are%20seeing.%26nbsp%3B%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3EThis%20is%20why%20we%20released%20RANDARRAY().%20Give%20it%20a%20shot%2C%20it's%20very%20flexible.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-462885%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462885%22%20slang%3D%22en-US%22%3E%3CP%3ETh%C3%A4nks%20-%20Randarray%20is%20exact%20what%20I%20needed%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-549980%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-549980%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20something%20to%20do%20to%20jump%20over%20gap%20of%20missing%20functions%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Elike%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DUNION(C1%23%3BE1%23)%20%26nbsp%3B%26nbsp%3B%20%2F%2F%20option%20to%20union%202%20arrays%3CBR%20%2F%3E%3DINTERSECT(C1%23%3BE1%23)%3CBR%20%2F%3E%3DEXCEPT(C1%23%3BE1%23)%20%26nbsp%3B%20%2F%2F%20might%20be%20limited%20for%201-column%20arrays%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20is%20there%20some%20option%20to%20get%20more%20easy%20something%20like%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3DINDEX(%20M16%23%3BINT(SEQUENCE(ROWS(M16%23)*COLUMNS(M16%23)%3B1%3B0)%2FCOLUMNS(M16%23))%2B1%3BMOD(SEQUENCE(ROWS(M16%23)*COLUMNS(M16%23)%3B1%3B0)%3BCOLUMNS(M16%23))%2B1)%3C%2FFONT%3E%3CBR%20%2F%3E%3CBR%20%2F%3Efor%20example%20%3DFLAT(M16%23)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3Emight%20be%20some%20option%20for%20direction%20(vertical%20%2F%20horizontal)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-672420%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-672420%22%20slang%3D%22en-US%22%3E%3CP%3EStrange%20behaviour%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20the%20page%20containing%20several%20dynamic%20array%20formulas%20when%20I%20select%20some%20empty%20cell%20and%20click%20on%20formula%20tab%20%22show%26nbsp%3B%20dependences%22%2C%20I%20see%20following%20picture%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117287iCC37BACEBE03F773%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22dependencies.jpg%22%20title%3D%22dependencies.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENO%20one%20referenced%20cells%20don't%20contain%20any%20reference%20to%20pointed%20cell%3CBR%20%2F%3E%3CBR%20%2F%3EN2%3A%3D%20%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EUNIQUE(Kuu%23)%20%2F%2F%20Kuu%20is%20reference%20C2%3CBR%20%2F%3EC2%3A%3D%20Kuup%C3%A4ev%23-DAY(Kuup%C3%A4ev%23)%2B1%20%2F%2F%20Kuup%C3%A4ev%20is%20reference%20B2%3C%2FFONT%3E%3CBR%20%2F%3EB2%3A%3D%20%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EMIN(M%C3%A4nniku%5BKuup%C3%A4ev%5D)%2BSEQUENCE(%24L%241%2BTODAY()-MIN(M%C3%A4nniku%5BKuup%C3%A4ev%5D))-1%20%2F%2F%20M%C3%A4nniku%20is%20table%20on%20other%20sheet%20not%20containing%20external%20formulas%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20is%20the%20reason%20of%20those%20dependency%20arrows.%20I%20can%20provide%20the%20workbook%20containing%20the%20case%20-%20not%20confidential%3C%2FFONT%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-672733%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-672733%22%20slang%3D%22en-US%22%3E%3CP%3Efollow%20up%20to%20previous%20-%20to%20repeat%20the%20possible%20bug%3CBR%20%2F%3E%3CBR%20%2F%3Eon%20the%20cell%20A3%20I%20create%20formula%20%3DSEQUENCE(10)%26nbsp%3B%3CBR%20%2F%3EI%20assign%20name%20%22seq%22%20to%20cell%20A3%3CBR%20%2F%3E%3CBR%20%2F%3Eon%20the%20cell%20c1%20I%20enter%20some%20number%20-%20let%20it%20be%207%20(no%20important)%3CBR%20%2F%3Eon%20the%20cell%20c3%20I%20enter%20formula%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3DFILTER(A3%23%3BA3%23%26gt%3B5)%2B%24C%241%3C%2FFONT%3E%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20now%20I%20ask%20to%20trace%20dependenc%20for%20C1%20-%20I%20get%20normal%20one%20arrow%20to%20C3%3CBR%20%2F%3E%3CBR%20%2F%3Enow%20I%20change%20the%20formula%20on%20C3%20to%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3DFILTER(seq%23%3Bseq%23%26gt%3B5)%2B%24C%241%3C%2FFONT%3E%20%2F%2F%20name%23%20references%20on%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20now%20the%20trace%20dependenc%20for%20C1%20looks%20like%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20515px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117290i4057CA1B57189421%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22dependencies2.jpg%22%20title%3D%22dependencies2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3ESo%20looks%20like%20name%23%20reference%20influences%20the%20full%20story%20(calculations%20works%20fine%20-%20only%20trace%20arrows)%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-680515%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-680515%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%20is%20there%20any%20update%20when%20this%20might%20be%20available%20on%20MacOS%20(insiders%20build%20or%20otherwise%3F).%20Thanks!%20J%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-681418%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-681418%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357514%22%20target%3D%22_blank%22%3E%40jamescrowley%3C%2FA%3E%26nbsp%3B-%20this%20has%20been%20in%20the%20Office%20365%20Insiders%20build%20of%20MacOS%20for%20months.%20January%202019%20at%20least.%20I've%20been%20playing%20with%20it%20on%20both%20Windows%20and%20Mac%20for%20quite%20a%20while.%20The%20only%20insider%20platforms%20it%20isn't%20on%20is%20iOS%20and%20Android.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-681459%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-681459%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20for%20being%20boring%2C%20more%20exactly%20that's%20for%20Insiders%20Fast.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOffice%20365%20Insiders%20considers%20these%20channels%20under%20its%20umbrella%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20id%3D%22app-container%22%3E%0A%3CSECTION%20class%3D%22handbook-page%20context-control%22%20data-grid%3D%22container%22%3E%0A%3CDIV%3E%0A%3CDIV%20class%3D%22desktopView%22%3E%0A%3CDIV%20class%3D%22handbook-content%20x-hidden-focus%22%3E%0A%3CDIV%3E%0A%3CP%3E%3CSTRONG%3EFAST%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CP%20class%3D%22x-hidden-focus%22%3EWhat%20it's%20called%20on%20your%20platform%3A%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EInsider%3C%2FSTRONG%3E%20-%20PC%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EInsider%20Fast%3C%2FSTRONG%3E%20-%20Mac%2C%20Android%2C%20and%20Windows%20Mobile%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CP%3EBest%20for%20people%20who%20want%20to%20use%20the%20very%20earliest%20builds%20to%20identify%20issues%20and%20provide%20feedback%20about%20new%20features%20still%20in%20development.%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CP%3EIt's%20ideal%20for%20those%20who%20don't%20mind%20the%20bit%20of%20risk%20involved%20in%20using%20unsupported%20builds.%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CP%3E%3CSTRONG%3ESLOW%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CP%3EWhat%20it's%20called%20on%20your%20platform%3A%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EMonthly%20Channel%20(Targeted)%3C%2FSTRONG%3E%20-%20PC%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EInsider%20Slow%3C%2FSTRONG%3E%20-%20Mac%2C%20Android%2C%20and%20Windows%20Mobile%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FSECTION%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-681521%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-681521%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthanks%20for%20the%20response!%20The%20thing%20is%2C%20I%20switched%20to%20the%20fast%20insiders%20option%20in%20Excel%20but%20it%20said%20there%20were%20no%20updates%20available%2C%20but%20the%20formulae%20don't%20work...%20Any%20idea%20if%20I%20am%20missing%20something%20obvious%20%3F%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-681562%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-681562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357514%22%20target%3D%22_blank%22%3E%40jamescrowley%3C%2FA%3E%26nbsp%3B%2C%20what%20do%20you%20mean%20exactly%20under%20%22don't%20work%22%3F%20For%20example%2C%20if%20you%20start%20typing%20%3Dseq%20do%20you%20see%20function%20name%20or%20it's%20no%20prompt%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20318px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117688i32CB78BBFAD60635%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E(screenshort%20is%20for%20Windows%2C%20for%20Mac%20it%20shall%20be%20similar)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-681880%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-681880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F357514%22%20target%3D%22_blank%22%3E%40jamescrowley%3C%2FA%3E%26nbsp%3Bgive%20it%202-3%20days.%20The%20feature%20is%20turned%20on%20at%20the%20server.%20So%20you%20can%20be%20using%20Excel%20and%20it%20will%20just%20start%20working.%20I%20don't%20know%20why%20they%20roll%20out%20some%20features%20to%20insiders%20this%20way%2C%20but%20they%20do%2C%20and%20Dynamic%20Array%20formulas%20was%20done%20this%20way.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-682013%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-682013%22%20slang%3D%22en-US%22%3E%3CP%20style%3D%22text-align%3A%20justify%3B%22%3EMy%20understanding%20is%20that%20not%20all%20insiders%20will%20have%20access%20yet.%20%26nbsp%3B%20It%E2%80%99s%20being%20phased%20in%20as%20part%20of%20testing.%20%26nbsp%3BSome%20are%20%E2%80%9Clucky%E2%80%9D%20and%20will%20have%20it%2C%20some%20won%E2%80%99t%3C%2FP%3E%0A%3CP%20style%3D%22text-align%3A%20justify%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22text-align%3A%20justify%3B%22%3EThings%20may%20have%20changed%20by%20now%20but%20that%E2%80%99s%20how%20it%20started%20off%20anyway.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-727919%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-727919%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20news%20on%20when%20this%20becomes%20available%20to%20the%20general%20public%3F%20I.e.%20365%20users%3F%20I'm%20currently%20using%20365%20ProPlus%20if%20that%20is%20of%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805536%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805536%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20love%20to%20be%20using%20the%20new%20features%2C%20but%20despite%20having%20the%20current%20version%20(%3CSPAN%3EExcel%20365%20for%20Windows%20-%20v%201907%20build%2011901.20176%3C%2FSPAN%3E)%2C%20I%20am%20not%20seeing%20the%20features%20you%20list.%20Please%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805769%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89379%22%20target%3D%22_blank%22%3E%40Derek%20White%3C%2FA%3E%26nbsp%3B-%20you%20have%20to%20either%20have%20the%20Insider%20build%2C%20which%20is%20on%201909%20now%2C%20or%20the%20Monthly%20Targeted%20build%2C%20which%20is%20on%201908%20right%20now.%20Even%20if%20you%20have%20the%20Monthly%20build%20and%20it%20is%20on%201908%2C%20I%20don't%20think%20the%20dynamic%20arrays%20have%20mode%20it%20to%20the%20normal%20monthly%20cycle.%20In%20Excel%2C%20go%20to%20the%20File%2C%20Account%20page%20and%20look%20at%20About%20Excel.%20The%20last%20line%20will%20tell%20you%20what%20channel%20you%20are%20on.%20If%20it%20doesn't%20say%20%22Monthly%20Channel%20(Targeted)%22%20or%20%22Office%20Insider%22%20you'll%20either%20need%20to%20switch%20to%20one%20of%20those%20(Targeted%20is%20more%20stable%20than%20Insider)%20or%20wait%20a%20bit%20longer.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-897964%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897964%22%20slang%3D%22en-US%22%3E%3CP%3EI%20really%20have%20trouble%20with%20how%20UDFs%20are%20treated%20by%20this%20design%2C%20especially%20when%20a%20model%20is%20created%20on%20a%20machine%20that%20supports%20DA%20and%20then%20later%20viewed%20on%20a%20machine%20that%20doesn't...%20or%20vice-versa.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20say%20I%20am%20a%20software%20vendor%20writing%20the%20FooMaster%20add-in%2C%20which%20contains%20a%20single%20(awesome)%20UDF%20called%20Foo.%26nbsp%3B%20Foo%20always%20returns%20a%20double%20value.%26nbsp%3B%20There%20are%20no%20arrays%20involved.%26nbsp%3B%20FooMaster%20has%20been%20around%20for%2030%20years%20and%20has%20thousands%20of%20customers%20who%20have%20used%20it%20for%20forever.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EProblem%20Scenario%20%231%20(Minor)%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EFred%20makes%20a%20workbook%20in%20Excel%202016%20that%20calls%20%3DFoo()%20in%20some%20cell%20and%20saves%20it%2C%20just%20like%20he%20always%20does.%3C%2FLI%3E%3CLI%3EFred%20sends%20his%20model%20to%20Wilma%20who%20is%20using%20an%20Excel%20with%20DA%20support.%3C%2FLI%3E%3CLI%3EWilma%20opens%20the%20model%20and%20sees%20%3D%40Foo()%20in%20a%20cell.%3C%2FLI%3E%3CLI%3EWilma%20is%20confused%20because%20she's%20not%20seen%20the%20%40%20symbol%20before.%26nbsp%3B%20Office%20365%20just%20was%20updated%20and%20all%20of%20sudden%20Fred's%20models%20are%20all%20weird.%3C%2FLI%3E%3CLI%3EShe%20does%20some%20reading%20learns%20it%20has%20something%20to%20do%20with%20the%20handling%20of%20arrays%20and%20says%20to%20herself...%20%22But%20Foo%20has%20nothing%20to%20do%20with%20arrays!%26nbsp%3B%20What%20is%20going%20on%3F%26nbsp%3B%20The%20FooMaster%20people%20must%20have%20messed%20up!%26nbsp%3B%20Or%20Fred's%20been%20smoking%20something%20with%20Barney%20again.%22%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EProblem%20Scenario%20%232%20(Major)%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EHarry%20has%20just%20gotten%20a%20DA%20enabled%20version%20of%20Excel.%3C%2FLI%3E%3CLI%3EHe%20makes%20a%20new%20workbook%20with%20%22%3DFoo()%22%20in%20it.%26nbsp%3B%20(He%20doesn't%20type%20%3D%40Foo()%20because%20why%20should%20he%3F%26nbsp%3B%20Foo%20has%20nothing%20to%20do%20with%20arrays%20and%20he's%20been%20typing%20these%20function%20this%20way%20for%20forever.)%3C%2FLI%3E%3CLI%3EHe%20sends%20his%20workbook%20to%20Hermione%20who's%20still%20slumming%20with%20Office%202016.%3C%2FLI%3E%3CLI%3EHermione%20opens%20the%20model%2C%20and%20sees%20a%20CSE%20array!%26nbsp%3B%20Hermione%20freaks%20out%20because%20she%20hates%20CSE%20arrays%20because%20they%20are%20scary.%3C%2FLI%3E%3CLI%3EWorse%2C%20FooMaster%20has%20a%20bunch%20of%20routines%20that%20scan%20workbooks%20looking%20for%20%3DFoo()%20functions%20so%20they%20can%20be%20tabulated%20and%20edited.%26nbsp%3B%20But%20FooMaster%20doesn't%20handle%20CSE%20array%20functions.%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EAll%20of%20a%20sudden%20FooMaster%20workbooks%20can%20no%20longer%20be%20freely%20exchanged%20between%20users!%3C%2FSTRONG%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOne%20possible%20cure%20for%20this%20would%20be%20to%20let%20a%20UDF%20creator%20be%20able%20to%20specify%20that%20a%20function%20will%20never%20return%20an%20array.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20consideration.%3CBR%20%2F%3EErik%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898302%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898302%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20lovely.%20Looking%20forwards%20to%20use%20the%20array%20functions%20soon%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-904561%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-904561%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20office%20365%20education%20don't%20have%20dynamic%20array%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-919776%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919776%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20really%20appreciated%20the%20FILTER%20function.%20I%20%3CEM%3Emay%3C%2FEM%3E%20have%20found%20a%20bug%20when%20I%20try%20and%20concatenate%20a%20cell%20reference%20with%20a%20wild%20card%20search.%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20what%20returns%20the%20error%20message%20%23CALC!%3C%2FP%3E%3CP%3E%3DFILTER('Registration%20Form'!N1%3AAB999%2C'Registration%20Form'!AA1%3AAA999%3DA1%26amp%3B%22*%22)%3C%2FP%3E%3CP%3EI'm%20expecting%20an%20array%20of%205%20form%20entries.%3C%2FP%3E%3CP%3EI%20know%20I%20can%20search%20using%20this%20format%20(concatenating%20a%20cell%20reference%20with%20a%20wild%20card)%20because%20the%20code%20below%20produces%20exactly%20what%20I%20would%20expect%3A%20the%20value%205.%3C%2FP%3E%3CP%3E%3DCOUNTIF('Registration%20Form'!AA%3AAA%2CA1%26amp%3B%22*%22)%3C%2FP%3E%3CP%3EIs%20this%20a%20known%20bug%2C%20or%20have%20I%20entered%20something%20incorrectly%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039980%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421592%22%20target%3D%22_blank%22%3E%40Erik_Westwig%3C%2FA%3E%3CSPAN%20class%3D%22%22%3E%2C%20have%20you%20received%20any%20response%20from%20Microsoft%3F%20I%20raised%20concerns%20about%20the%20same%20thing%20about%20a%20year%20ago%20and%20am%20still%20not%20satisfied%20with%20the%20behavior%20in%20the%20current%20version.%20I%20am%20very%20worried%20that%20this%20will%20be%20released%20as-is%20and%20that%20spreadsheets%20created%20with%20our%20custom%20functions%20will%20suddenly%20show%20up%20as%20CSE%20array%20functions%20when%20they%20only%20return%20scalar%20values.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EWe%20use%20real-time%20data%20servers%20(RTD)%20for%20recalculation%20and%20there's%20a%20bug%20in%20older%20versions%20of%20Excel%20where%20the%20RTD%20server%20does%20not%20disconnect%20and%20will%20never%20refresh%20the%20data%20unless%20you%20restart%20Excel.%20I%20know%20this%20issue%20has%20been%20fixed%20in%20newer%20versions%2C%20however%20my%20worry%20is%20users%20opening%20the%20same%20files%20in%20older%20versions%20and%20running%20into%20all%20sorts%20of%20issues%20because%20everything%20is%20now%20marked%20as%20a%20CSE-array%20function...%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1040358%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1040358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246352%22%20target%3D%22_blank%22%3E%40GabrielMichaud%3C%2FA%3E-%20No%20I'm%20afraid%20I%20haven't%20heard%20anything.%26nbsp%3B%20The%20whole%20compatibility%20issue%20with%20DA%20and%20UDF%20functions%20seems%20to%20me%20to%20be%20a%20very%20big%20mess%2C%20and%20I%20share%20your%20concern%20that%20they%20will%20just%20ship%20it%20like%20it%20works%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1041177%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1041177%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20still%20not%20seeing%20Dynamic%20Array%20features.%20My%20Excel%20version%20is%201910%20(Build%2012130.20390%20Click%20to%20Run)%20Monthly%20Channel.%3C%2FP%3E%3CP%3EOffice%20Updates%20are%20set%20to%20automatically%20download%20and%20install.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1041181%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1041181%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89379%22%20target%3D%22_blank%22%3E%40Derek%20White%3C%2FA%3E%26nbsp%3B%2C%20deploying%20on%20Monthly%20Channel%20started%20only%20few%20days%20ago.%20I%20don't%20know%20how%20many%20users%20are%20covered%20and%20how%20much%20time%20it%20takes.%20My%20guess%20it's%20not%20significant%20per%20cent%20and%20entire%20deployment%20could%20take%20months.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBuild%20number%20doesn't%20matter.%20Only%20channel%20matters%2C%20after%20that%20is%20the%20lottery%20when%20Microsoft%20flick%20the%20switch%20for%20you.%3C%2FP%3E%0A%3CP%3EAs%20variant%20you%20may%20receive%20any%20new%20functionality%20even%20without%20build%20update%2C%20the%20code%20could%20be%20already%20within%20your%20build.%20Just%20be%20online%20to%20be%20activated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1054341%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054341%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20people%20You%20know%20things%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20one%20conceptual%20(or%20technical)%20question%20about%20those%20dynamic%20arrays.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20correctly%20format%20cells%20filled%20(spilled)%20with%20dynamic%20array%20formulas%20so%20all%20and%20only%20spilled%20cells%20will%20be%20formatted%20accordingly%3CBR%20%2F%3E%3CBR%20%2F%3EI%20know%20currently%203%20options%3A%3CBR%20%2F%3E*%20manual%20formatting%20after%20spilled%20area%20change%3CBR%20%2F%3E*%20formatting%20all%20candidate%20range%20(column%20or%20row)%20accordingly%3CBR%20%2F%3E*%20using%20conditional%20formats%20with%20Applies%20to%20%3Dxxx%23%3CBR%20%2F%3E%3CBR%20%2F%3Eunfortunately%20this%20last%20one%20DONT%20WORK.%20Applies%20to%20C16%23%20will%20be%20changed%20to%20regular%20range%20and%20not%20any%20more%20dynamic%20and%20exact%20same%20happend%20with%20INDIRECT%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160560i1DDF30543BCE7956%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160559i1464544D50C78205%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1054353%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F56123%22%20target%3D%22_blank%22%3E%40Henn%20Sarv%3C%2FA%3E%26nbsp%3B-%20yes%2C%20conditional%20formatting%20works%20only%20with%20static%20ranges.%20At%20least%20so%20far.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1054870%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054870%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20running%20Office%20365%20Personal%2C%3C%2FP%3E%3CP%3EVersion%201911%20(Build%2012228.20332%20Click%20to%20Run)%2C%3C%2FP%3E%3CP%3EOffice%20Insider%3A%3C%2FP%3E%3CUL%3E%3CLI%3EYou%20are%20signed%20up%20for%20the%20Monthly%20Chanel%20(Targeted)%3C%2FLI%3E%3CLI%3EYou%20will%20receive%20new%20builds%20of%20Office%20once%20or%20twice%20a%20month%3C%2FLI%3E%3C%2FUL%3E%3CP%3EWhat%20can%20I%20do%2C%20to%20get%20DA%20Excel%20version%20to%20test%20it%3F%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1054979%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F482001%22%20target%3D%22_blank%22%3E%40Gradjevinac%3C%2FA%3E%26nbsp%3B-%20it%20should%20be%20in%20the%20Monthly%20Targeted%20channel%20unless%20they%20have%20halted%20it%20for%20some%20reason.%20But%20even%20at%20that%2C%20it%20can%20take%20a%20few%20days%20to%20show%20up%20after%20a%20fresh%20install%20or%20a%20move%20from%20one%20channel%20to%20a%20faster%20channel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055293%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055293%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F482001%22%20target%3D%22_blank%22%3E%40Gradjevinac%3C%2FA%3E%26nbsp%3Band%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F236847%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDA%20is%20slowly%20being%20release%20across%20the%20Monthly%20Targeted%20so%20not%20everyone%20gets%20hit%20with%20it%20in%20one%20go.%26nbsp%3B%20You%20can't%20control%20it%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%20Check%20out%20Excel%20Online%20as%20it%20should%20now%20be%20showing%20up%20there%3C%2FP%3E%0A%3CP%3EAlso%2C%20rather%20than%20Monthly%20Targeted%20you%20could%20switch%20to%20Office%20Insider%20build%201912%20to%20get%20access%20right%20now%20in%20the%20desktop%20version%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055419%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055419%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%2C%20my%20understanding%20DA%20is%20slowly%20being%20release%20across%20the%20Monthly%20channel.%20It%20shall%20be%20available%20on%20Monthly%20Targeted%2C%20deployment%20for%20this%20channel%20started%20in%20June%202019%20as%20I%20remember%20and%20now%20it%20shall%20be%20fully%20covered.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055486%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055486%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20Monthly%20too%20sorry%2C%20but%20also%20given%20it%20isn%E2%80%99t%20showing%20up%20for%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F482001%22%20target%3D%22_blank%22%3E%40Gradjevinac%3C%2FA%3E%26nbsp%3BI%E2%80%99m%20thinking%20they%20are%20still%20testing%20with%20insiders%20too.%20%26nbsp%3BJust%20a%20thought.%20I%E2%80%99m%20not%20sure%20if%20%2F%20when%20a%20channel%20is%20%E2%80%9C%20fully%20covered%E2%80%9D.%20%26nbsp%3B%20I%E2%80%99d%20be%20interested%20to%20know%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055499%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055499%22%20slang%3D%22en-US%22%3E%3CP%3Ejust%20few%20days%20ago%20I%20installed%20(using%20ODT)%20on%20some%20training%20coputer%20InsiderFast%20and%20got%20Dynamic%20Arrays%20immediately%3CBR%20%2F%3Eon%20other%20computer%20I%20had%20to%20wait%20few%20days%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055577%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055577%22%20slang%3D%22en-US%22%3E%3CP%3EWyn%2C%20I%20also%20don't%20know%20for%20sure%20about%20Monthly%20Targeted%2C%20just%20my%20guess%20-%20for%206%20months%20and%20partly%20having%20now%20DA%20on%20Excel%20Online%20and%20Monthly%2C%20I%20assume%20Monthly%20Targeted%20is%20covered.%20At%20least%20on%20English%20SKU.%20With%20this%20case%20I'd%20support%20Henn%2C%20perhaps%20some%20time%20gap%20between%20installing%20new%20build%20and%20activation%20of%20the%20functionality%20on%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1062107%22%20slang%3D%22en-US%22%3ERe%3A%20Preview%20of%20Dynamic%20Arrays%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1062107%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20explanations%20and%20answers.%20Just%20to%20give%20feedback%2C%20I%20just%20got%20DA%20Excel%20version.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft
Until now, you wrote a formula for each value you wanted returned to the grid. One formula, one value. If you wanted another value, you wrote (or copied) another formula. With dynamic arrays, that all changes. Now, you can write a formula hit the enter key and get an array of values returned. One formula, many values. This will allow you to build more capable spreadsheets, faster, with fewer formulas and less chance of error.
 
To harness the power of dynamic arrays, we’ve added some amazing new functions. So, for instance, you can use the SORT function to sort a list, the UNIQUE function to remove duplicates from that list, then use the FILTER function to get just what you want from the list. And when your data changes, the dynamic array will resize and recalculate automatically! 
 

Dynamic Arrays Blog GIF final.gifDynamic arrays in actionSpilling

We call the behavior of placing values in neighboring blank cells "spilling”, and you’ll see Excel indicate the formula’s “spill range" with a thin blue border when you select any cell inside the spill range.
 
Don’t worry about the spill range overlapping your data--if there isn’t enough space, the formula will roll up and show an informative #SPILL error. When you select the #SPILL error, the formulas desired spill range will be indicated by a dashed blue border. Just move or delete the obstructing data and your formula will automatically spill.
 
NoSpill Blog GIF final.gifSpilling range not blank
Native to Excel
Dynamic array support is deeply integrated into Excel and it's not limited to the functions shipping alongside it -- any newly authored formula that returns an array will spill. For instance, entering =A3:A13 into B3 will cause the values in A3:A13 to be spilled into B3:B13. And just like Excel's grid, dynamic arrays can be 2 dimensional as shown in the multiplication table example below.
 
 Integrated Blog GIF final.gifUsing dynamic arrays with existing functions
Referencing the spill range using A1# notation
Dynamic arrays may seamlessly resize as your data changes. To make it easy to reference resizing dynamic arrays, we are adding a way to reference the entire spill in a dependable, resilient way. You can do this by following a cell reference with the # symbol, for example A1#. This is equivalent to referencing the entire spilled range for the dynamic array in A1. We'll default to this style reference whenever you write a formula that refers to the entire spill range.
 
In the example below notice how the SUMIF function is using all the product names from the dynamic array in D5. When Grapes is added to the sales table, the D5 spill range grows and so does the result of the SUMIF because it references D5# rather than D5:D8.
 
 Spill Range Reference Blog GIF final.gifUsing # to refer to the spill range
New Functions
Here is the full set of functions that will be accompanying dynamic arrays.
 
FILTER - filters an array of data based on criteria you define.
UNIQUE - returns a list of unique values from a list or range.
SORT - sorts an array of values.
SORTBY - sorts an array based on a corresponding array.
SEQUENCE - generates a list of sequential numbers, such as 1, 2, 3, 4.
SINGLE – accepts a range or array and returns a single value using implicit intersection.
RANDARRAY - returns an array of random numbers between 0 and 1.
 
We cannot wait to see how our users use these new building blocks in their spreadsheets.
 
Learn More
You can learn more about dynamic arrays from these resources:
 
 
Availability notes:
Dynamic arrays functionality is available in Preview for users signed up for the Office 365 Insiders Program starting today. We will initially roll out to a subset of Insider users on Windows so that we can gather feedback and monitor feature quality. Over the next few months, we'll be increasing the number of Insider users with access to dynamic arrays and light up support for Excel on Mac, web, and mobile. You’ll know if you have dynamic arrays if you see any of the new functions in your formula autocomplete when you start typing a formula.
 
To stay connected to Excel and its community, read Excel blog posts, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.
 
Joe McDaid (@jjmcdaid)
Program Manager, Excel
136 Comments
Occasional Visitor

I have been waiting patiently since this was announced in September, I'm on the insider program and have been checking every month but nothing yet.

Any update on when these new functions will become a reality?

cheers.

@mdallura , do you see Office Insider if you check File->Account for your Excel?

image.png 

Occasional Contributor

Today I start for intent get dynamically set of random numbers

some attempts:

=SEQUENCE(10)*0+RANDBETWEEN(10;20)

or

=SEQUENCE(10)*0+RAND()

NO help :(

Any idea - how to get set of random numbers dynamically

OR random subset of actual numbers dynamically




 

Microsoft

@Henn Sarv DA's calculates in a 1x1 context, they then spill. This means that RAND and RANDBETWEEN will only provide 1 value which is what you are seeing. This is why we released RANDARRAY(). Give it a shot, it's very flexible.

Occasional Contributor

Thänks - Randarray is exact what I needed

Occasional Contributor

Is there something to do to jump over gap of missing functions

 

like

 

=UNION(C1#;E1#)    // option to union 2 arrays
=INTERSECT(C1#;E1#)
=EXCEPT(C1#;E1#)   // might be limited for 1-column arrays

and is there some option to get more easy something like:

=INDEX( M16#;INT(SEQUENCE(ROWS(M16#)*COLUMNS(M16#);1;0)/COLUMNS(M16#))+1;MOD(SEQUENCE(ROWS(M16#)*COLUMNS(M16#);1;0);COLUMNS(M16#))+1)

for example =FLAT(M16#)

might be some option for direction (vertical / horizontal)

Occasional Contributor

Strange behaviour

On the page containing several dynamic array formulas when I select some empty cell and click on formula tab "show  dependences", I see following picture:

dependencies.jpg

 

NO one referenced cells don't contain any reference to pointed cell

N2:= UNIQUE(Kuu#) // Kuu is reference C2
C2:= Kuupäev#-DAY(Kuupäev#)+1 // Kuupäev is reference B2

B2:= MIN(Männiku[Kuupäev])+SEQUENCE($L$1+TODAY()-MIN(Männiku[Kuupäev]))-1 // Männiku is table on other sheet not containing external formulas 

What is the reason of those dependency arrows. I can provide the workbook containing the case - not confidential


Occasional Contributor

follow up to previous - to repeat the possible bug

on the cell A3 I create formula =SEQUENCE(10) 
I assign name "seq" to cell A3

on the cell c1 I enter some number - let it be 7 (no important)
on the cell c3 I enter formula =FILTER(A3#;A3#>5)+$C$1

and now I ask to trace dependenc for C1 - I get normal one arrow to C3

now I change the formula on C3 to =FILTER(seq#;seq#>5)+$C$1 // name# references on formula

 

and now the trace dependenc for C1 looks like:

dependencies2.jpg

So looks like name# reference influences the full story (calculations works fine - only trace arrows) 

Visitor
@Joe McDaid is there any update when this might be available on MacOS (insiders build or otherwise?). Thanks! J
Contributor

@jamescrowley - this has been in the Office 365 Insiders build of MacOS for months. January 2019 at least. I've been playing with it on both Windows and Mac for quite a while. The only insider platforms it isn't on is iOS and Android.

Sorry for being boring, more exactly that's for Insiders Fast. 

Office 365 Insiders considers these channels under its umbrella

 

FAST

What it's called on your platform:

  • Insider - PC
  • Insider Fast - Mac, Android, and Windows Mobile

Best for people who want to use the very earliest builds to identify issues and provide feedback about new features still in development.

It's ideal for those who don't mind the bit of risk involved in using unsupported builds.

SLOW

What it's called on your platform:

  • Monthly Channel (Targeted) - PC
  • Insider Slow - Mac, Android, and Windows Mobile
Visitor

@Ed Hansberry @Sergei Baklan thanks for the response! The thing is, I switched to the fast insiders option in Excel but it said there were no updates available, but the formulae don't work... Any idea if I am missing something obvious ? Thanks!

@jamescrowley , what do you mean exactly under "don't work"? For example, if you start typing =seq do you see function name or it's no prompt?

image.png

(screenshort is for Windows, for Mac it shall be similar)

Contributor

@jamescrowley give it 2-3 days. The feature is turned on at the server. So you can be using Excel and it will just start working. I don't know why they roll out some features to insiders this way, but they do, and Dynamic Array formulas was done this way.

My understanding is that not all insiders will have access yet.   It’s being phased in as part of testing.  Some are “lucky” and will have it, some won’t

 

Things may have changed by now but that’s how it started off anyway.

Occasional Visitor

Any news on when this becomes available to the general public? I.e. 365 users? I'm currently using 365 ProPlus if that is of any help.

Regular Visitor

I would love to be using the new features, but despite having the current version (Excel 365 for Windows - v 1907 build 11901.20176), I am not seeing the features you list. Please help!

Contributor

@Derek White - you have to either have the Insider build, which is on 1909 now, or the Monthly Targeted build, which is on 1908 right now. Even if you have the Monthly build and it is on 1908, I don't think the dynamic arrays have mode it to the normal monthly cycle. In Excel, go to the File, Account page and look at About Excel. The last line will tell you what channel you are on. If it doesn't say "Monthly Channel (Targeted)" or "Office Insider" you'll either need to switch to one of those (Targeted is more stable than Insider) or wait a bit longer.

Regular Visitor

I really have trouble with how UDFs are treated by this design, especially when a model is created on a machine that supports DA and then later viewed on a machine that doesn't... or vice-versa.

 

For example, say I am a software vendor writing the FooMaster add-in, which contains a single (awesome) UDF called Foo.  Foo always returns a double value.  There are no arrays involved.  FooMaster has been around for 30 years and has thousands of customers who have used it for forever.

 

Problem Scenario #1 (Minor)

  • Fred makes a workbook in Excel 2016 that calls =Foo() in some cell and saves it, just like he always does.
  • Fred sends his model to Wilma who is using an Excel with DA support.
  • Wilma opens the model and sees =@Foo() in a cell.
  • Wilma is confused because she's not seen the @ symbol before.  Office 365 just was updated and all of sudden Fred's models are all weird.
  • She does some reading learns it has something to do with the handling of arrays and says to herself... "But Foo has nothing to do with arrays!  What is going on?  The FooMaster people must have messed up!  Or Fred's been smoking something with Barney again."

 

Problem Scenario #2 (Major)

  • Harry has just gotten a DA enabled version of Excel.
  • He makes a new workbook with "=Foo()" in it.  (He doesn't type =@Foo() because why should he?  Foo has nothing to do with arrays and he's been typing these function this way for forever.)
  • He sends his workbook to Hermione who's still slumming with Office 2016.
  • Hermione opens the model, and sees a CSE array!  Hermione freaks out because she hates CSE arrays because they are scary.
  • Worse, FooMaster has a bunch of routines that scan workbooks looking for =Foo() functions so they can be tabulated and edited.  But FooMaster doesn't handle CSE array functions.
  • All of a sudden FooMaster workbooks can no longer be freely exchanged between users!

 

One possible cure for this would be to let a UDF creator be able to specify that a function will never return an array.

 

Thank you for your consideration.
Erik

 

 

Frequent Contributor

So lovely. Looking forwards to use the array functions soon

Visitor

My office 365 education don't have dynamic array

Occasional Visitor

I've really appreciated the FILTER function. I may have found a bug when I try and concatenate a cell reference with a wild card search. 

Here's what returns the error message #CALC!

=FILTER('Registration Form'!N1:AB999,'Registration Form'!AA1:AA999=A1&"*")

I'm expecting an array of 5 form entries.

I know I can search using this format (concatenating a cell reference with a wild card) because the code below produces exactly what I would expect: the value 5.

=COUNTIF('Registration Form'!AA:AA,A1&"*")

Is this a known bug, or have I entered something incorrectly?

@Joe McDaid

 

New Contributor

@Erik_Westwig, have you received any response from Microsoft? I raised concerns about the same thing about a year ago and am still not satisfied with the behavior in the current version. I am very worried that this will be released as-is and that spreadsheets created with our custom functions will suddenly show up as CSE array functions when they only return scalar values.

 

We use real-time data servers (RTD) for recalculation and there's a bug in older versions of Excel where the RTD server does not disconnect and will never refresh the data unless you restart Excel. I know this issue has been fixed in newer versions, however my worry is users opening the same files in older versions and running into all sorts of issues because everything is now marked as a CSE-array function...

Regular Visitor

@GabrielMichaud- No I'm afraid I haven't heard anything.  The whole compatibility issue with DA and UDF functions seems to me to be a very big mess, and I share your concern that they will just ship it like it works now.

Regular Visitor

I am still not seeing Dynamic Array features. My Excel version is 1910 (Build 12130.20390 Click to Run) Monthly Channel.

Office Updates are set to automatically download and install.

@Derek White , deploying on Monthly Channel started only few days ago. I don't know how many users are covered and how much time it takes. My guess it's not significant per cent and entire deployment could take months.

 

Build number doesn't matter. Only channel matters, after that is the lottery when Microsoft flick the switch for you.

As variant you may receive any new functionality even without build update, the code could be already within your build. Just be online to be activated.

Occasional Contributor

Hey people You know things,

I have one conceptual (or technical) question about those dynamic arrays.

How correctly format cells filled (spilled) with dynamic array formulas so all and only spilled cells will be formatted accordingly

I know currently 3 options:
* manual formatting after spilled area change
* formatting all candidate range (column or row) accordingly
* using conditional formats with Applies to =xxx#

unfortunately this last one DONT WORK. Applies to C16# will be changed to regular range and not any more dynamic and exact same happend with INDIRECT

clipboard_image_1.png



clipboard_image_0.png

@Henn Sarv - yes, conditional formatting works only with static ranges. At least so far.

Occasional Visitor

Hi,

 

I am running Office 365 Personal,

Version 1911 (Build 12228.20332 Click to Run),

Office Insider:

  • You are signed up for the Monthly Chanel (Targeted)
  • You will receive new builds of Office once or twice a month

What can I do, to get DA Excel version to test it?

Contributor

@Gradjevinac - it should be in the Monthly Targeted channel unless they have halted it for some reason. But even at that, it can take a few days to show up after a fresh install or a move from one channel to a faster channel.

@Gradjevinac and @Ed Hansberry 

 

DA is slowly being release across the Monthly Targeted so not everyone gets hit with it in one go.  You can't control it

 

Also Check out Excel Online as it should now be showing up there

Also, rather than Monthly Targeted you could switch to Office Insider build 1912 to get access right now in the desktop version

@Wyn Hopkins , my understanding DA is slowly being release across the Monthly channel. It shall be available on Monthly Targeted, deployment for this channel started in June 2019 as I remember and now it shall be fully covered.

Yes Monthly too sorry, but also given it isn’t showing up for @Gradjevinac I’m thinking they are still testing with insiders too.  Just a thought. I’m not sure if / when a channel is “ fully covered”.   I’d be interested to know

Occasional Contributor

just few days ago I installed (using ODT) on some training coputer InsiderFast and got Dynamic Arrays immediately
on other computer I had to wait few days

 

Wyn, I also don't know for sure about Monthly Targeted, just my guess - for 6 months and partly having now DA on Excel Online and Monthly, I assume Monthly Targeted is covered. At least on English SKU. With this case I'd support Henn, perhaps some time gap between installing new build and activation of the functionality on it.

Occasional Visitor

Hi,

 

Thank you for explanations and answers. Just to give feedback, I just got DA Excel version.