Dynamic Ranking Question

[PaulF]

Hello all,

I've tried... and tried... and tried

I'm tracking gas prices at a number of local stations with Stations as my Rows and dates across the top and the daily gas prices as the data...

I have a rank column with the following NON-Dynamic Formula in E5:

=IF([@[10/2/2016]]="","",RANK([@[10/2/2016]],[10/2/2016],1)+COUNTIF($N$4:N5,N5)-1)

I have been unable to come up with a solution to make this dynamic. As the days months and years go on, I want the ranking column to always rank the latest column.

Thank you in advance for any assistance you provide

Respectfully,
PaulF

 

[narunfca]

Hi

Enclosed the solution.

with best regards,
Arun N

 

[Somendra Misra]

Hi,

Another option, a little long one :

In E5 and copy down:
=IF(INDEX($F5:INDEX($F5:$ZZ5,,MATCH("zzzz",$F$4:$ZZ$4)),,MATCH("zzzz",$F$4:$ZZ$4))="","",RANK(INDEX($F5:INDEX($F5:$ZZ5,,MATCH("zzzz",$F$4:$ZZ$4)),,MATCH("zzzz",$F$4:$ZZ$4)),INDEX($F$5:$ZZ$21,,MATCH("zzzz",$F$4:$ZZ$4)),1)+COUNTIF(INDEX($F$4:$ZZ$21,1,MATCH("zzzz",$F$4:$ZZ$4)):INDEX($F$4:$ZZ$21,ROW()-4,MATCH("zzzz",$F$4:$ZZ$4)),INDEX($F5:INDEX($F5:$ZZ5,,MATCH("zzzz",$F$4:$ZZ$4)),,MATCH("zzzz",$F$4:$ZZ$4))))


Regards,

 

[NARAYANK991]

Hi ,

Why not retain your formula , revising it as follows :

=IF(INDIRECT("Table2[@[" & LastColumn & "]]")="","",RANK(INDIRECT("Table2[@[" & LastColumn & "]]"),INDIRECT("Table2[" & LastColumn & "]"),1)+COUNTIF($N$4:N5,N5)-1)

Define a named range called LastColumn , and in the Refers To box , enter the formula :

=LOOKUP(1,1/Table2[#Headers],Table2[#Headers])

Narayan

 

[PaulF]

Hi ,

Why not retain your formula , revising it as follows :

=IF(INDIRECT("Table2[@[" & LastColumn & "]]")="","",RANK(INDIRECT("Table2[@[" & LastColumn & "]]"),INDIRECT("Table2[" & LastColumn & "]"),1)+COUNTIF($N$4:N5,N5)-1)

Define a named range called LastColumn , and in the Refers To box , enter the formula :

=LOOKUP(1,1/Table2[#Headers],Table2[#Headers])

Narayan

I tried and tried between offset and indirect... I could not solution it.

I like this solution the best as I can leverage this knowledge going forward...

Thank you everyone !!

Respectfully,
PaulF