Pivotting - Formula w/ Varying Column

[Bigfoot17]

I am using a Pivot Table and I have some formulas to rank the top numbers in
each column. My problem is - at times there will be 7 columns and other
times 3 or 4, so the "Grand Totals" column moves.

Here is my formula:RANK(J6,$J$6:$J$500,0)+COUNTIF(J6:$J$6,J6)-1
But the the reference to J may need to be changed.

Basically: If J5 ="Grand Total" use the above formula, but if i5="Grand
Total" then use RANK(i6,$i$6:$i$500,0)+COUNTIF(i6:$i$6,i6)-1

 

[T. Valko]

This is a real "mouthful" but it works (without having to use volatile
functions!).

Assume the Grand Total can be in either H5:J5

The formula is entered in cell L6.

=RANK(INDEX(H$6:J$500,ROWS(L$6:L6),MATCH("Grand
Total",H$5:J$5,0)),INDEX(H$6:J$500,,MATCH("Grand
Total",H$5:J$5,0)))+COUNTIF(INDEX(H$6:J$500,ROW(L$6:L$6),MATCH("Grand
Total",H$5:J$5,0)):INDEX(H$6:J$500,ROWS(L$6:L6),MATCH("Grand
Total",H$5:J$5,0)),INDEX(H$6:J$500,ROWS(L$6:L6),MATCH("Grand
Total",H$5:J$5,0)))-1

Copied down

 

[T. Valko]

Slight tweak...doesn't effect or change the result. Just makes more logical
sense!

In the COUNTIF function:

....+COUNTIF(INDEX(H$6:J$500,ROW(L$6:L$6)...

Change ROW(L$6:L$6) to ROW(L$1:L$1)

 

[T. Valko]

Change ROW(L$6:L$6) to ROW(L$1:L$1)

That can be reduced to ROW(L$1)

 

[Bigfoot17]

You are truly an MVP with me too. This worked fine and I have been able to
make mods to my situation (change the range, search for a cell value instead
of text). I appreciate your looking at the problem and the proposed solution.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Bigfoot17]

I apologize if this is a repeat, but IE schizzed out on me when I submitted
an earlier attempt to ask this and I have not seen the post appear.

The formula does not break ties, I end up with 1,2,3,4,4,6,7,8,8
This presents a problem when I attempt to pull the names in the ranked order
I get the first tied name twice.

Any chance you could show me how to add the tiebreaker to the formula? I
though it was there but apparently not. Oh, BTW I needed to do some error
trapping and have been using the formula as it appears below.

=IF(ISNA(RANK(INDEX(B$6:L$500,ROWS(L$6:L6),MATCH("Grand
Total",B$5:L$5,0)),INDEX(B$6:L$500,,MATCH("Grand
Total",B$5:L$5,0)))+COUNTIF(INDEX(B$6:L$500,ROW(L$6:L$6),MATCH("Grand
Total",B$5:L$5,0)):INDEX(B$6:L$500,ROWS(L$6:L6),MATCH("Grand
Total",B$5:L$5,0)),INDEX(B$6:L$500,ROWS(L$6:L6),MATCH("Grand
Total",B$5:L$5,0)))-1),"",RANK(INDEX(B$6:L$500,ROWS(L$6:L6),MATCH("Grand
Total",B$5:L$5,0)),INDEX(B$6:L$500,,MATCH("Grand
Total",B$5:L$5,0)))+COUNTIF(INDEX(B$6:L$500,ROW(L$6:L$6),MATCH("Grand
Total",B$5:L$5,0)):INDEX(B$6:L$500,ROWS(L$6:L6),MATCH("Grand
Total",B$5:L$5,0)),INDEX(B$6:L$500,ROWS(L$6:L6),MATCH("Grand
Total",B$5:L$5,0)))-1)

 

[T. Valko]

The formula does not break ties

The formula I suggested *does* break ties.

If you're error trapping for empty cells try this version:

=IF(INDEX(H$6:J$20,ROWS(L$6:L6),MATCH("Grand
Total",H$5:J$5,0))="","",RANK(INDEX(H$6:J$20,ROWS(L$6:L6),MATCH("Grand
Total",H$5:J$5,0)),INDEX(H$6:J$20,,MATCH("Grand
Total",H$5:J$5,0)))+COUNTIF(INDEX(H$6:J$20,1,MATCH("Grand
Total",H$5:J$5,0)):INDEX(H$6:J$20,ROWS(L$6:L6),MATCH("Grand
Total",H$5:J$5,0)),INDEX(H$6:J$20,ROWS(L$6:L6),MATCH("Grand
Total",H$5:J$5,0)))-1)