Excel formula - index match address indirect rank.avg

1
votes

Need help crafting formula.

I have data in SQL, exporting to excel. In excel have formula's to determine rank of parts based on values in a certain week in a range/array. The columns are weekly buckets, and each time I export, the weekly buckets change (no dates in the past). I would like the rank to remain fixed a specific date, but each export per week the specific column comes inward by 1 column.

I need an excel formula which finds the position of the specific value in the weekly bucket array. After this is determined, I need another formula to convert this column address (e.g. "AX") to be referenced by the rank.avg function.

I am guessing this is some combination of match, index, cell, indirect, & rank.avg. So far I have tried just the first part and cannot get cell("col",(index(match))) to return the letter values of the column it is in. It returns the # of the column instead.

Here is example

  A       B        C         D
1 Part    Rank(C)  11/28/17  12/04/17
2 Part A  1        $100      $100    
3 Part B  2        $50       $125    
4 Part C  3        $25       $200

Column B has the rank formula which is determining the rank of the parts (A,B,C) from column C. Column C is determining from hardcoding go to the value 11/28/17.

I am stuck at =CELL("address",INDEX(C1:D1,1,MATCH(43067,C1:D1,0))). This returns $C$1 when I just want $C or just C. Afterwards I intend to use this with indirect to be able to pass it through the rank function. Any assistance would be greatly appreciated where someone can fill the gap on the formula or come up with an easier way to do this.

Nick

3
excelexcel-formulacell

3 Answers

2
votes

Yes get rid of ADDRESS and INDIRECT and just put the column in a COUNTIF formula.

ADDRESS and INDIRECT are volatile. This is an non-Volatile and non-Array formula that does what you want in one step:

=COUNTIF(INDEX(C:D,2,MATCH(43067,$C$1:$D$1,0)):INDEX(C:D,1040000,MATCH(43067,$C$1:$D$1,0)),">" & INDEX(C:D,ROW(),MATCH(43067,$C$1:$D$1,0))) + 1

enter image description here

0
votes

To get just the column letter you could get fancy with Address and Substitute:

=SUBSTITUTE(ADDRESS(1,MATCH(43067, C1:D1,0)+2,4), "1", "")

Address takes in a column and row and spits out a cell. So we just always reference row 1 and whatever column is returned from address (+2 since we are starting at C in Match). Then we substitute out the 1 from the returned cell address with nothing to be left with the column letter.

0
votes

Well, if your formula returns cell reference in form of C1 you can wrap it with:

=CHAR(64+COLUMN(C1))

Just change C1 with your formula.