Forum Discussion
Is it possible to Rank repeating strings without repeating same ranks in an Excel Table?
I see. Excel Table "remembers" that, for example, C5 is in the last row of the table. If you expand the table it adjust formula in row 5 such way it reference on the last row of expanded table. Thus you have two formulas with C6 an no one with C5.
Workaround could be
=[@Date]&[@Fruit]& COUNTIFS( $C$2:INDEX([Fruit],ROW()-ROW(Table1[[#Headers],[Fruit]])), [@Fruit], $B$2:INDEX([Date],ROW()-ROW(Table1[[#Headers],[Date]])), [@Date])
Genius, I appreciate it. PeterBartholomew1
One follow up question
I've always wondered that hardcoding values would save up on the calculation speed.
Writing 1 instead of referencing header row, would prevent one wheel of calculation, in case the
sheet is very heavy.
Is there any evidence of this?
If you are going to attempt to optimise a sheet for speed then I suggest you use tools to analyse the performance such as that offered by Charles Williams's FastExcel. Otherwise, it is only too easy to commit too much effort to changing code that is not consuming significant resource in the first place.
Usually the first consideration is to select computationally efficient algorithms, then to program them to be as transparent as possible. Rather than hard-wiring values which cannot be guaranteed to be constant, I would prefer to add the record sequence as a helper range; it would do little for speed but, given a suitable variable name, may add clarity to downstream formulas.