average of column b of all values in col d found in col a

%3CLINGO-SUB%20id%3D%22lingo-sub-508131%22%20slang%3D%22en-US%22%3Eaverage%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508131%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%203%20columns(%20in%20reality%20these%20columns%20have%20up%20to%201200%20entries)%3C%2FP%3E%3CP%3ECol1%20(named%20range%20DBNUM%20Col2%20(named%20range%20EQUITY)%26nbsp%3B%20Col15(named%20range%20DBNUMRN)%3C%2FP%3E%3CP%3E1%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2022%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%203%3C%2FP%3E%3CP%3E2%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2045%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%204%3C%2FP%3E%3CP%3E3%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%2012%3C%2FP%3E%3CP%3E4%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%209%3C%2FP%3E%3CP%3E5%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%207%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20find%20the%20average%20of%20the%20values%20in%20col2%20that%20correspond%20the%20values%20in%20the%203r%20column%20that%20are%20found%20in%20the%201st%20col%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-508131%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508885%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20your%20DBNUM%20field%20is%20a%20strictly%20increasing%20set%20of%20values%2C%20LOOKUP%20may%20be%20used%20to%20return%20values%20from%20EQUITY%20column.%3C%2FP%3E%3CP%3E%3D%20AVERAGE(%20LOOKUP(%20DBNUMRN%2C%20DBNUM%2C%20EQUITY%20)%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-515125%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-515125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3EThanks%20Peter%20yours%20is%20the%20only%20formula%20of%20about%206%20I%20have%20tried%20that%20has%20retuned%20the%20correct%20value.%20The%20one%20issue%20I%20am%20running%20into%20is%20that%20for%20some%20unknown%20reason%20the%20total%20sheet%20has%20become%20volatile%2C(it%20may%20be%20due%20to%20the%20sheer%20number%20of%20calcs%20and%20dynamic%20arrays%20it%20is%20building)%20As%20a%20result%20at%20times%20the%20array%20DBNUMRN%20(and%20some%20others)%20will%20at%20times%20have%20a%20blank%20row%20in%20them.%20If%20I%20click%20on%20a%20few%20cells%20in%20the%20sheet%20and%20get%20the%20blank%20to%20go%20away%20your%20formula%20works%20fine.%20In%20this%20same%20volatile%20behavior%20the%20results%20in%20DBNUMRN%20calc%20have%20some%20results%20show%20up%20in%20non%20consecutive%20order.%20Somewhere%20something%20on%20the%20sheet%20is%20causing%20the%20volatility%20and%20leading%20to%20the%20calculation%20getting%20into%20an%20iterations%20loop.%20I%20went%20and%20set%20max%20iterations%20to%2020%20just%20to%20calm%20this%20effect%20but%20that's%20treating%20the%20symptom%20not%20the%20problem.%20I%20have%20been%20very%20careful%20to%20have%20no%20circular%20references%20in%20the%20sheet%20so%20I%20don't%20know%20why%20it%20is%20doing%20this%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-515975%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-515975%22%20slang%3D%22en-US%22%3EYou%20might%20have%20used%20the%20volatile%20OFFSET%20in%20naming%20your%20ranges.%20Use%20INDEX%20instead.%20Avoid%20array%20formulas%20and%20replace%20them%20with%20non-array%20ones.%20Also%2C%20try%20replacing%20the%20volatile%20INDIRECT%20with%20CHOOSE.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-516281%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-516281%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EI%20used%20INDEX%20for%20all%20my%20named%20ranges.%20No%20OFFSET%20in%20the%20entire%20sheet%2C%20I%20do%20have%20over%2018%20Dynamic%20named%20ranges%20though.%20I%20did%20use%20INDIRECT%20for%20a%20small%20number%20of%20formulas%2018%20to%20be%20exact%2C%20The%20volatility%20started%20showing%20up%20before%20that.%20However%20likely%20I%20will%20go%20back%20and%20try%20to%20find%20a%20way%20using%20index%20instead%2C%20Not%20sure%20I%20will%20be%20successful%20as%20using%20INDIRECT(ADDRESS...%20for%20this%20application%20may%20be%20too%20difficult%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-516419%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-516419%22%20slang%3D%22en-US%22%3EPlease%20specify%20your%20INDIRECT%20formula.%20Perhaps%2C%20I%20can%20suggest%20an%20alternative%20that%20avoids%20the%20volatile%20INDIRECT.%3CBR%20%2F%3EBy%20the%20way%2C%20you%20didn%E2%80%99t%20mention%20if%20you%20also%20have%20array%20formulas.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-516718%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-516718%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20entire%20sheet%20is%20all%20array%20formulas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(%24BM%2410%3A%24BO%2410%2C%2C(INDIRECT(ADDRESS(ROW()%2B10%2C20))))%20is%20the%20basic%20building%20block%20formula%3C%2FP%3E%3CP%3EColumn%2020%20is%20a%20helper%20column%20built%20with%20a%20ton%20of%20criteria%20that%20returns%20the%20column%20in%20BM10%3ABO10%20I%20want%20the%20data%20from.%20When%20the%20iferror%20occurs%20it%20goes%20to%20the%20same%20formula%20but%20looking%20at%20col%2021..%20etc%20etc%20until%20it%20has%20looked%20thru%204%20columns%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-516860%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-516860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20FYI%20the%20CountA%20for%20the%20sheet%20is%20currently%20at%2013807%20and%20only%20about%20800%20of%20those%20do%20not%20contain%20formulas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-517495%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-517495%22%20slang%3D%22en-US%22%3EPlease%20attach%20your%20sample%20file.%20I%20believe%20we%20can%20avoid%20the%20volatile%20INDIRECT.%20You%20also%20admitted%20the%20sheet%20is%20all%20array%20formulas%2C%20which%20another%20way%20of%20saying%20%E2%80%9Cthe%20sheet%20calculates%20very%20slowly%E2%80%9D.%20We%20will%20try%20to%20avoid%20array%20formulas%2C%20too.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-518048%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-518048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInteresting%20difference%20in%20approach.%26nbsp%3B%20I%20work%20very%20hard%20to%20ensure%20every%20possible%20calculation%20%3CSTRONG%3Eis%3C%2FSTRONG%3E%20an%20array%20formula%20in%20order%20to%20simplify%20the%20logic%20of%20the%20model.%26nbsp%3B%20I%20have%20come%20across%20factors%20of%202%20lost%20in%20speed%20for%20a%20financial%20model%20(more%20for%20IFS%20are%20replaced%20since%20they%20are%20very%20fast)%20but%20nothing%20to%20put%20me%20off%20array%20formulas%20as%20a%20basic%20building%20tool.%26nbsp%3B%20I%20have%20even%20gained%20a%20factor%20of%203%20in%20speed%20on%20one%20client%20workbook%20by%20omitting%20%3CSPAN%3Eintermediate%20%3C%2FSPAN%3Estep-by-step%20calculations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20used%20to%20be%20concerns%20about%20array%20formulas%20mysteriously%20stopping%20calculating%20part%20way%20through%20without%20reporting%20an%20error%20but%20I%20think%20that%20is%20a%20thing%20of%20the%20past.%26nbsp%3B%20For%20the%20future%2C%20modern%20Dynamic%20Arrays%20have%20the%20potential%20to%20change%20everything.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-518153%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-518153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3EI%20agree%20and%20as%20my%20sheet%20is%20constantly%20referring%20to%20arrays%20in%20one%20table%20to%20an%20array%20in%20another%20table%20any%20changes%20would%20be%20minor%3C%2FP%3E%3CP%3EI%20think%20my%20real%20problem%20is%20with%20the%20dynamic%20named%20ranges%20as%20it%20is%20when%20I%20click%20on%20a%20blank%20cell%20in%20the%20same%20column%20but%20above%20the%20named%20ranges%20start%20cell%20that%20the%20sheet%20does%20a%20minor%20recalc%20that%20glitches%20though%20ti%20does%20still%20do%20the%20calcs.%20I%20am%20in%20the%20process%20of%20switching%20the%20data%20fields%20to%20structured%20tables%20and%20using%20those%20for%20references%20rather%20than%20the%20named%20ranges.%20This%20should%20improve%20accuracy%20and%20speed%20as%20the%20tables%20are%20not%20at%20the%20top%20of%20the%20worksheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThough%20if%20you%20could%20take%20a%20peek%20at%20the%20indirect%20formula%20mentioned%20above%20I%20would%20appreciate%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-518275%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-518275%22%20slang%3D%22en-US%22%3EAs%20with%20everything%20else%20in%20life%2C%20array%20formulas%20are%20efficient%20if%20used%20sparingly.%20When%20used%20excessively%2C%20as%20described%20here%2C%20array%20formulas%20become%20inefficient.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-518521%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-518521%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20not%20have%20noticed%20but%20in%20the%20tiny%20implementation%20based%20upon%20your%20problem%20definition%2C%20I%20converted%20both%20ranges%20to%20tables.%26nbsp%3B%20I%20then%20used%20defined%20names%20to%20refer%20to%20ranges%20within%20the%20table.%26nbsp%3B%20This%20makes%20the%20named%20ranges%20dynamic.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-518526%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-518526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EA%20I%20am%20reworking%20some%20things%20calculation%20speed%20doesn't%20seem%20to%20be%20an%20issue%20as%20it%20does%20this%20in%20about%201sec.%20The%20weird%20glitch%20when%20you%20click%20on%20an%20empty%20cell%20that%20is%20in%20a%20dynamic%20named%20ranges%20column%20but%20above%20its%20start%20point%20that%20is%20having%20the%20data%20reshuffle%26nbsp%3B%20in%20areas%20across%20the%20sheet%20is%20a%20pain.%20No%20data%20results%20are%20being%20dropped%2C%20but%20occasionally%20because%20it%20will%20insert%20a%20blank%20this%20causes%20a%20problem%20until%20I%20click%20on%20another%20cell%20and%20the%20blank%20goes%20away.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20sense%20in%20sending%20it%20to%20you%20to%20look%20at%20the%20indirects%20until%20after%20I%20have%20modified%20the%20sheet%20formulas%20to%20use%20structured%20table%20references%20instead%20of%20dynamic%20named%20ranges.%20This%20does%20seem%20to%20be%20working%20better%20on%20a%20bunch%20of%20levels%20While%20I%20am%20at%20it%20I%20will%20look%20for%20dtat%20columns%20where%20I%20used%20array%20formulas%20and%20didn't%20need%20to.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-518551%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-518551%22%20slang%3D%22en-US%22%3EYes%20I%20did%20notice%20that.%20At%20the%20time%20I%20was%20trying%20to%20avoid%20tables%20but%20alas%20another%20theory%20shot%20to%20hell%20lol.%20The%20larger%20problem%20I%20had%20with%20your%20solution%20is%20I%20was%20not%20just%20trying%20to%20sort%20large%20to%20small%20but%20thru%20comparison%20of%20what%20turned%20about%20to%20be%2018%20different%20criteria%20to%20establish%20the%20ordering%2C%2C%2C%20particularly%20for%20the%20middle%20values%20in%20the%20results%20column.%20I%20will%20assuredly%20revisit%20this%20though%20once%20I%20have%20reset%20my%20other%207%20dynamic%20tables%20to%20use%20structured%20table%20references%20rather%20than%20dynamic%20named%20ranges%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-518787%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-518787%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20may%20well%20finish%20up%20at%20a%20similar%20place.%26nbsp%3B%20It%20is%20just%20that%20I%20start%20with%20the%20assumption%20of%20array%20calculation%20and%20only%20decompose%20to%20single%20cell%20calculation%20when%20I%20have%20reason%20to%20do%20so.%26nbsp%3B%20That%20might%20be%20a%20use%20case%20that%20required%20incremental%20updates%20and%20only%20a%20small%20proportion%20of%20the%20calculation%20changes.%26nbsp%3B%20The%20other%20reason%20to%20use%20relative%20referencing%20is%20that%20I%20simply%20run%20out%20of%20array%20dimensions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20my%20use%20of%20arrays%20will%20increase%20with%20the%20modern%20Dynamic%20Arrays.%26nbsp%3B%20I%20really%20like%20the%20fact%20that%20the%20formula%20is%20held%20in%20a%20single%20cell%20and%20the%20spill%20region%20is%20determined%20without%20further%20user%20input%20(either%20manually%20determined%20fill%20ranges%20or%20the%20cumbersome%20CSE).%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-519466%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-519466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESO%20the%20glitch%20I%20am%20having%20seems%20to%20be%20because%20EXCEL%20is%20saying%20I%20have%20circular%20references.%20I%20was%20careful%20not%20to%20have%20any%2C%20and%20the%20circular%20reference%20in%20error%20checking%20is%20greyed%20out.%20It%20keeps%20saying%20circular%20reference%20and%20I%20go%20to%20the%20cell%20it%20listed%20in%20the%20bottom%20left%20corner...%20refill%20the%20cell%20and%20then%20it%20sends%20me%20to%20another%20one%20somewhere%20else%20which%20doesn't%20exist.%20After%2015%20or%20so%20completely%20random%20cells%20always%20within%20a%20column%20of%20similar%20cells%20I%20gave%20up%20After%20looking%20online%20the%20only%20real%20explanation%20I%20found%20that%20made%20any%20sense%20was%20it%20is%20due%20to%20too%20many%20array%20formulas%20%2C%2C%2C%2C%20BONUS%20points%20to%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%3C%2FP%3E%3CP%3ENot%20sure%20that%20is%20actually%20the%20issue%20as%20it%20isn't%20resolved%20yet%20other%20than%20to%20allow%20iterations%20in%20calculate%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-519764%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-519764%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20with%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%20as%20far%20as%20the%20goal%20of%20removing%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DINDIRECT(ADDRESS(ROW()%2B10%2C20)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eis%20concerned.%26nbsp%3B%20It%20is%20a%20volatile%20function%20do%20not%20see%20what%20it%20achieves%20over%20and%20above%20a%20relative%20reference%20to%20the%20addressed%20cell.%26nbsp%3B%20Since%20the%26nbsp%3B%3CSPAN%3Ecolumn%20picked%20up%20using%20INDEX%3C%2FSPAN%3E%20value%20depends%20on%20the%20content%20of%20the%20cell%2C%20it%20is%20pretty%20arbitrary%20and%20circular%20references%20are%20likely%20to%20be%20a%20problem%20to%20track%20down.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-521914%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-521914%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(%24BC%2410%3A%24BL%2410%2C%2C(INDIRECT(ADDRESS(ROW()%2C20))))%2C(IFERROR(INDEX(%24BC%2410%3A%24BL%2410%2C%2C(INDIRECT(ADDRESS((ROW()-(COUNT(%24T%242%3A%24T%2411))%2C21))%E2%80%A6%E2%80%A6%E2%80%A6%E2%80%A6..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20the%20results%20column%20it%20travels%20down%20col%2020%20fine%20and%20yes%20I%20could%20use%20relative%20reference%20in%20that%20snippet.%20Now%20when%20I%20get%20to%20the%20next%20iferror%20I%20am%20a%20nonspecific%20number%20down%20the%20results%20column..%20hence%20the%20(-COUNT...)%20because%20when%20I%20shift%20over%20to%20col%2021%20I%20need%20the%20result%20at%20the%20top.%20But%20I%20have%20no%20way%20of%20knowing%20when%20this%20event%20would%20trigger%2C%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Indirect%20allows%20me%20to%20start%20this%20second%20iferror%20looking%20at%20the%20top%20of%20its%20column%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20that%20make%20sense%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-522789%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-522789%22%20slang%3D%22en-US%22%3EYour%20use%20of%20multiple%20IFERROR%20functions%20is%20inefficient.%20It%20would%20be%20better%20if%20reduced%20to%20a%20minimum%2C%20perhaps%20only%20one.%3CBR%20%2F%3ESuch%20inefficiency%20is%20further%20worsened%20by%20the%20multiple%20INDIRECT%20functions%20within%20them.%20INDIRECT%20can%20be%20completely%20avoided%20in%20your%20case.%20You%20only%20have%20to%20establish%20a%20definitive%20logic%20that%20determines%20which%20column%20to%20CHOOSE%3B%20otherwise%2C%20you%20need%20to%20refer%20to%20only%20one%20column.%20Succinctly%2C%20a%20change%20in%20your%20data%20layout%20is%20inevitable.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-522918%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-522918%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20I%20will%20definitely%20look%20into%20CHOOSE.%20No%20good%20way%20to%20put%20the%20data%20in%20one%20column%20and%20still%20not%20run%20into%20the%20issue%20of%20which%20row%20to%20select%2C%20The%20Formulas%20for%20creating%20each%20helper%20column%20are%20already%20very%20long.%20To%20conjoin%20them%20so%20all%20data%20would%20be%20in%20one%20column%20would%20make%20the%20formula%20a%20major%20novel%20and%20lead%20to%20excel%20to%20doing%20a%20ton%20of%20recalculations%20over%20and%20over%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-524131%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-524131%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPutting%20the%20implementation%20issues%20aside%20for%20the%20moment%2C%20I%20suspect%20that%20the%20logic%20of%20the%20solution%20needs%20to%20be%20checked%20through%20at%20the%20'array%20of%20arrays'%20level.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3E%22Column%2020%20is%20a%20helper%20column%20built%20with%20a%20ton%20of%20criteria%20%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3Ethat%20returns%20the%20column%20in%20BM10%3ABO10%20I%20want%20the%20data%20from%22%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20were%20with%20you%2C%20could%20you%20demonstrate%20to%20me%20that%20none%20of%20the%20criteria%20depend%20upon%20the%20content%20of%20columns%20%3CSPAN%3E%3CSTRONG%3EBM%3ABO%3C%2FSTRONG%3E%3F%26nbsp%3B%20The%20combination%20of%20circular%20references%20and%20volatile%20functions%20means%20that%20a%20cycle%20of%20complete%20recalculations%20will%20be%20triggered%20by%20virtually%20any%20interaction%20with%20the%20workbook.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-526767%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-526767%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3EThe%20content%20of%20BM10%3ABO10%20is%20static%2C%20Just%20values%20no%20formula.%20The%20helper%20columns%20make%20no%20reference%20to%20any%20of%20the%20values%20in%20BM10%3ABO10.%20Other%20rows%20in%20these%20columns%20are%20not%20dependent%20on%20the%20value%20in%20BM10%3ABO10.%20The%20helpers%20are%20dependent%20on%20the%20values%20in%20row%2011%2C%2012%2C%20and%2013%20which%20are%20dependent%20on%20values%20in%20other%20places%20on%20the%20sheet%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-526844%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-526844%22%20slang%3D%22en-US%22%3ETo%20ultimately%20resolve%20the%20issue%2C%20you%20must%20attach%20a%20sample%20file.%20Note%20that%20you%20aren%E2%80%99t%20required%20to%20attach%20the%20actual%20file.%20So%2C%20no%20confidentiality%20issues%20would%20arise.%20I%20only%20require%20a%20sample%20(not%20the%20actual)%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-526865%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-526865%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%20Not%20sure%20where%20you%20see%20a%20circular%20reference%20possible%2C%2C%2C%20The%20helper%20columns%20evaluate%20the%20data%20in%20the%20rows%20of%20column%20Bm%3ABO%20based%20upon%20criteria%20like%20%26gt%3B%20%26lt%3B%20%3D%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20other%20areas%20in%20some%20formulas%20where%20a%20cell%20does%20refer%20to%20itself%20is%20when%20using%20ROW%20and%20also%20when%20using%20Countif%26nbsp%3B%20ex.%20in%20cell%20BP25%26nbsp%3B%20%3DIFERROR(INDEX(MAIN%5BDBNUM%5D%2CMATCH(0%2CIF((%24BR%2421%26gt%3BMAIN%5BEQUITY%5D)*(%24BR%2422%26gt%3BMAIN%5BEV%5D)%2CCOUNTIF(%24BP%2424%3A%24BP25%2CMAIN%5BDBNUM%5D)%2C%22%22)%2C0))%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-526896%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-526896%22%20slang%3D%22en-US%22%3EThe%20presence%20of%20IF%20sandwiched%20in%20your%20formula%20presupposes%20that%20is%20is%20an%20array%20formula.%20And%20there%20are%20plenty%20of%20them.%20It%E2%80%99s%20not%20surprising%20that%20aside%20from%20circular%20references%2C%20the%20array%20formulas%20also%20significantly%20cause%20slow%20calculation.%20As%20I%20declared%20earlier%2C%20that%20is%20an%20excessive%20use%20of%20array%20formulas.%20Avoid%20those%20array%20formulas%20by%20all%20means!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-527176%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-527176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EIt%20doesn't%20calculate%20slow%20at%20all%2C%2C%2C%20calcs%20done%20in%20a%20mouse%20click%20it%20is%20the%20random%20circular%20refences%20that%20create%20an%20odd%20glitch%20that%20shuffles%20data%20at%20time%20is%20all%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-527234%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-527234%22%20slang%3D%22en-US%22%3EIn%20that%20case%2C%20I%20will%20never%20discover%20the%20cause%20of%20the%20circular%20reference%20without%20your%20sample%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-527238%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-527238%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20Countif%20was%20the%20problem%20I%20fixed%20it%20and%20no%20more%20Glitch%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-527262%22%20slang%3D%22en-US%22%3ERe%3A%20average%20of%20column%20b%20of%20all%20values%20in%20col%20d%20found%20in%20col%20a%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-527262%22%20slang%3D%22en-US%22%3EHopefully%2C%20you%20did.%20I%20wish%20you%20good%20luck%20with%20your%20work.%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I have 3 columns( in reality these columns have up to 1200 entries)

Col1 (named range DBNUM Col2 (named range EQUITY)  Col15(named range DBNUMRN)

1                                      22                              3

2                                       45                             4

3                                      12

4                                      9

5                                      7

 

I am trying to find the average of the values in col2 that correspond the values in the 3r column that are found in the 1st col

29 Replies
Highlighted

@Twifoo 

Thanks I will definitely look into CHOOSE. No good way to put the data in one column and still not run into the issue of which row to select, The Formulas for creating each helper column are already very long. To conjoin them so all data would be in one column would make the formula a major novel and lead to excel to doing a ton of recalculations over and over

Highlighted

@Dichotomy66 

Putting the implementation issues aside for the moment, I suspect that the logic of the solution needs to be checked through at the 'array of arrays' level.

 

"Column 20 is a helper column built with a ton of criteria

that returns the column in BM10:BO10 I want the data from"

 

If I were with you, could you demonstrate to me that none of the criteria depend upon the content of columns BM:BO?  The combination of circular references and volatile functions means that a cycle of complete recalculations will be triggered by virtually any interaction with the workbook.

Highlighted

@Peter BartholomewThe content of BM10:BO10 is static, Just values no formula. The helper columns make no reference to any of the values in BM10:BO10. Other rows in these columns are not dependent on the value in BM10:BO10. The helpers are dependent on the values in row 11, 12, and 13 which are dependent on values in other places on the sheet

Highlighted
To ultimately resolve the issue, you must attach a sample file. Note that you aren’t required to attach the actual file. So, no confidentiality issues would arise. I only require a sample (not the actual) file.
Highlighted

@Peter Bartholomew  Not sure where you see a circular reference possible,,, The helper columns evaluate the data in the rows of column Bm:BO based upon criteria like > < = 

 

The other areas in some formulas where a cell does refer to itself is when using ROW and also when using Countif  ex. in cell BP25  =IFERROR(INDEX(MAIN[DBNUM],MATCH(0,IF(($BR$21>MAIN[EQUITY])*($BR$22>MAIN[EV]),COUNTIF($BP$24:$BP25,MAIN[DBNUM]),""),0)),"")

Highlighted
The presence of IF sandwiched in your formula presupposes that is is an array formula. And there are plenty of them. It’s not surprising that aside from circular references, the array formulas also significantly cause slow calculation. As I declared earlier, that is an excessive use of array formulas. Avoid those array formulas by all means!
Highlighted

@TwifooIt doesn't calculate slow at all,,, calcs done in a mouse click it is the random circular refences that create an odd glitch that shuffles data at time is all

Highlighted
In that case, I will never discover the cause of the circular reference without your sample file.

The Countif was the problem I fixed it and no more Glitch

Highlighted
Hopefully, you did. I wish you good luck with your work.