Home

Using Index Counta to set up a dynamic range is not returning the correct value, but sing offset doe

%3CLINGO-SUB%20id%3D%22lingo-sub-461917%22%20slang%3D%22en-US%22%3EUsing%20Index%20Counta%20to%20set%20up%20a%20dynamic%20range%20is%20not%20returning%20the%20correct%20value%2C%20but%20sing%20offset%20doe%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-461917%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20worksheet%20that%20is%20doing%20a%20ton%20of%20calculations%20so%20I%20am%20trying%20to%20streamline%20it%20a%20bit%20so%20it%20will%20be%20more%20friendly%20to%20slower%20computers%2C%20Originally%20I%20set%20up%20dynamic%20named%20ranges%20using%20offset%26nbsp%3BOFFSET('FLOP%20DATA'!%24A%2413%2C0%2C0%2CCOUNTA('FLOP%20DATA'!%24A%3A%24A)%2C1))%3C%2FP%3E%3CP%3EThis%20works%20fine%20and%20returns%20the%20accurate%20result%20when%20doing%20a%20count%20of%2074%3C%2FP%3E%3CP%3EI%20am%20wanting%20to%20switch%20these%20to%20a%20less%20volatile%20argument%20so%20I%20was%20using%3C%2FP%3E%3CP%3E('FLOP%20DATA'!%24A%2413%3AINDEX('FLOP%20DATA'!%24A%3A%24A%2C%20COUNTA('FLOP%20DATA'!%24A%3A%24A)))%3C%2FP%3E%3CP%3EThis%20is%20returning%20a%20count%20value%20of%2063%20and%20dropping%20of%20the%20bottom%20of%20the%20range%2C%20The%20values%20in%20the%20range%20are%20all%20numbers%20and%20have%20no%20blanks%20etc...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20is%20using%26nbsp%3BIFERROR(INDEX(FLOP%2CMATCH(%24P14%2CDBNUM%2C0))%2C%22%22)%20less%20of%20a%20drain%20on%20computer%20resources%20than%20using%20vlookup%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-461917%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-461979%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Index%20Counta%20to%20set%20up%20a%20dynamic%20range%20is%20not%20returning%20the%20correct%20value%2C%20but%20sing%20offset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-461979%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%2C%20perhaps%20it%20shall%20be%3C%2FP%3E%0A%3CPRE%3E('FLOP%20DATA'!%24A%2413%3AINDEX('FLOP%20DATA'!%24A%3A%24A%2C%20COUNTA('FLOP%20DATA'!%24A%3A%24A)%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%2B13%3C%2FSTRONG%3E%3C%2FFONT%3E))%3C%2FPRE%3E%0A%3CP%3Eor%20so%2C%20index%20starts%20from%20first%20row%20and%20you%20need%20to%20add%20some%20offset.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20with%20dynamic%20ranges%20OFFSET%20is%20more%20easy%20in%20maintenance%20(at%20least%20for%20me)%2C%20but%20INDEX%20is%20much%20better%20from%20performance%20point%20of%20view.%26nbsp%3B%20Thus%20on%20small%20ranges%20it%20doesn't%20matter%20what%20to%20use%2C%20but%20on%20relatively%20big%20ones%20INDEX%20is%20definitely%20more%20preferable.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-462066%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Index%20Counta%20to%20set%20up%20a%20dynamic%20range%20is%20not%20returning%20the%20correct%20value%2C%20but%20sing%20offset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462066%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20does%20seem%20to%20work%20I%20will%20have%20tp%20test%20is%20a%20lot.On%20this%20worksheet%20the%20data%20set%20is%20replaceable%20and%20can%20be%20anywhere%20from%20a%2030x%208%20array%20to%20an%201177%20x%208%20array%20.%20Also%20I%20am%20using%20about%2014%20named%20ranges%20so%20this%20is%20especially%20where%20I%20am%20trying%20to%20get%20rid%20of%20offset%20though%20it%20is%20super%20easy%20to%20use%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-464313%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Index%20Counta%20to%20set%20up%20a%20dynamic%20range%20is%20not%20returning%20the%20correct%20value%2C%20but%20sing%20offset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-464313%22%20slang%3D%22en-US%22%3EYour%20INDEX-COUNTA%20formula%20for%20the%20dynamic%20range%20is%20correct%2C%20provided%20that%20A1%3AA12%20are%20not%20blank.%20If%20A1%3AA12%20are%20blank%2C%20the%20last%2012%20cells%20in%20Column%20A%20would%20be%20excluded%20from%20your%20dynamic%20range.%20If%2011%20out%20of%20the%2012%20cells%20in%20A1%3AA12%20are%20blank%2C%20which%20I%20suspect%20is%20true%20in%20your%20case%2C%20then%20the%20last%2011%20cells%20would%20be%20excluded%20from%20your%20dynamic%20range%2C%20such%20that%20it%20only%20counts%2063%20instead%20of%2074.%20To%20remedy%20the%20possible%20existence%20of%20blank%20cells%20above%20the%20starting%20row%20of%20your%20dynamic%20range%2C%20exclude%20A1%3AA12%20through%20this%20formula%3A%3CBR%20%2F%3E%3D'FLOP%20DATA'!%24A%2413%3AINDEX('FLOP%20DATA'!%24A%2413%3A%24A%241048576%2C%3CBR%20%2F%3ECOUNTA('FLOP%20DATA'!%24A%2413%3A%24A%241048576))%3C%2FLINGO-BODY%3E
Dichotomy66
Contributor

I have a worksheet that is doing a ton of calculations so I am trying to streamline it a bit so it will be more friendly to slower computers, Originally I set up dynamic named ranges using offset OFFSET('FLOP DATA'!$A$13,0,0,COUNTA('FLOP DATA'!$A:$A),1))

This works fine and returns the accurate result when doing a count of 74

I am wanting to switch these to a less volatile argument so I was using

('FLOP DATA'!$A$13:INDEX('FLOP DATA'!$A:$A, COUNTA('FLOP DATA'!$A:$A)))

This is returning a count value of 63 and dropping of the bottom of the range, The values in the range are all numbers and have no blanks etc...

 

Also is using IFERROR(INDEX(FLOP,MATCH($P14,DBNUM,0)),"") less of a drain on computer resources than using vlookup?

3 Replies

@Dichotomy66 , perhaps it shall be

('FLOP DATA'!$A$13:INDEX('FLOP DATA'!$A:$A, COUNTA('FLOP DATA'!$A:$A)+13))

or so, index starts from first row and you need to add some offset.

 

In general with dynamic ranges OFFSET is more easy in maintenance (at least for me), but INDEX is much better from performance point of view.  Thus on small ranges it doesn't matter what to use, but on relatively big ones INDEX is definitely more preferable.

 

That does seem to work I will have tp test is a lot.On this worksheet the data set is replaceable and can be anywhere from a 30x 8 array to an 1177 x 8 array . Also I am using about 14 named ranges so this is especially where I am trying to get rid of offset though it is super easy to use 

Your INDEX-COUNTA formula for the dynamic range is correct, provided that A1:A12 are not blank. If A1:A12 are blank, the last 12 cells in Column A would be excluded from your dynamic range. If 11 out of the 12 cells in A1:A12 are blank, which I suspect is true in your case, then the last 11 cells would be excluded from your dynamic range, such that it only counts 63 instead of 74. To remedy the possible existence of blank cells above the starting row of your dynamic range, exclude A1:A12 through this formula:
='FLOP DATA'!$A$13:INDEX('FLOP DATA'!$A$13:$A$1048576,
COUNTA('FLOP DATA'!$A$13:$A$1048576))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies